We have built quite a large Excel based program that contains quite a lot of coding. the program seems to work fine the majority of the time but every so often we recieve an error message that causes excel to not save, or to crash and shut down.
The message that comes up is "File/Path Error"
Now I've just done some hunting around online and some links I have found speak of declairing the document location correctly in the coding. I was wondering could this be the issue at hand?
Anytime the document needs to save, there is a cell with the directory path and file path declared as a variable (Actually as Dim String) within the coding. Is this the best way to declare a file path?
Also, the problem seems intermittent. We Compile the VBA coding before saving, we also use a Clean Project add-in within Excel. These processes usually help but every so often the problem comes back.
the spreadsheet needs to be copied to a directory called "C:downloads" as it contains a ODBC query to itself (In reality, this is a query to an External Oracle Database)
On loading, it should pop up a simple userform, with a combo and two command buttons, which when pressed takes you to a (hidden) tab that displays a pivottable.
All works well until I try to close /save when 60% of the time, Excel encounters problems and closes and will not load up the file the next time until either quit excel or disable macros. Messages include "file/path access error", "I/O Error" or get restarts excel.
On a casual run through, I expect you might report back that "All worked ok for me". Please can you give it a bit of a thrashing, comment out the userform show, save the file (frequently) becuase i assure you it will break ultimately!
This is a brand-new file and I've tried it on about 5 different PC running different versions of Excel and generally get the same result.
I have an excel 2003 template containing a userform that is displayed using the workbook_open event.
The userform causes excel to crash with file/path error 75. i have tried many suggestions given on ozgrid and several other forums with no joy.
When the userform crashes, the debugger stops on DataFormNew.show.
I have added stop commands in the initialise procedure to see what is going on and it appears that my userform has disappeared!
to upload the excel template and any other information required to get this sorted. its size is 765 KB which exceeds the size limit to attach to the post.
here is the workbook_open macro:
Private Sub Workbook_Open() 'On Error GoTo MyError 'Call UnProtectAll
'Dim frm As UserForm 'For Each frm In UserForms ' Unload frm 'Next
The attached spreadsheet is as simple as it gets - a single userform with a combo box that allows 1 of 4 choices. It gets loaded and displayed in the workbook_open() event - or at least it should - but 9/10 times i get the message "Path/File error" and debug crashes out on the userform.show line. The next time I try and open the file (despite not saving it) Excel crashes and closes! Even re-starting Excel does not allow the file to be opened!
If, however, I open the file with macros disabled, and go into the VBE and manually run the workbook_open() all works fine. I'm using Excel 2003 and I've tried it on other installations - sometimes it will work, other times not.
I've tried every combination of userform load, unload, hide & show with similar results.
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be; S:BryanFor KenGulf ConstructionST0609014-t.xls (the file name length may vary) What code can I use to consistently trim it back to; S:BryanFor KenGulf Construction as the "DestFldr" variable?
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath 'controle = Dir(pad$ + "FactuurNummer7.txt") 'If controle = "" Then GoTo EerstAanmaken 'Open pad$ + "Factuurnummer7.txt" For Input As #10 'Input #10, Nummer1 'Close #10......................
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1() Dim fs As Object Dim oldPath As String, newPath As String oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save Set fs = CreateObject("Scripting.FileSystemObject") fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls" Set fs = Nothing End Sub
I just found a code to copy file path and file contents. However it is copying folder path and folder contents.here is my requirement.I will specify a path, macro has to copy that file name in a particular cell, then it has to copy all its contents. ex:
file name 1 has 3 sub files in it, file name 2 has 2 sub files in it--
Each month I am sent a spreadsheet which contains a list of 100's of different file paths (including file name).
For example (Quater 1 is the document name) R:ManagementJohn2008SalesSectionAQuater1
I would like to remove the file name from the file path (I will then convert the file paths to hyperlinks which will open the folder the document is stored in but not the document itself.
I am trying to separate a filename from a filepath in Excel VBA 2003.
I am using the Application. GetOpenFilename command to get the user to select the correct file. However, this also seems to record the filepath.
I am trying to separate the filename out of this so that each file( name) activates a different sub procedure. I am storing the result of the getopenfilename as a Variant.
At the moment I am having to specify the full path in order to get each sub to run. This is fine for 1 user - but not for others as the files are stored in different locations for each user.
I am trying to launch windows explorer from within excel vba. The routine below is borrowed from another post on this site and modified for my purposes.
I am getting the following run time error when trying to use MoveFolder from the scripting runtime library:
run-time error ‘76’ path not found
The source and destination paths are picked up out of cells and I think the spaces in the name or the length of the path names may be causing the problem.
The source folder looks something like this (contains 50 characters): K:Example123 Folder1AB CD ESubfold,XX abcdefgh
The destination folder looks something like this (contains 60 characters): K:Example123 Folder1XX New123AB CD ESubfold,XX abcdefgh
The code is pretty basic:
Sub MoveFolder(Sourcepath As String, Destinationpath As String) Dim FSO As Scripting.FileSystemObject
Set FSO = New Scripting.FileSystemObject
'some code here removes the trailing backslashes on the 'end of the pathnames
FSO.MoveFolder Sourcepath, Destinationpath End Sub
I have double checked the source path name exists. I have also tried doing it manually with the path names being entered in as text but i keep getting the error.
I'm using this code to import a file, but I want to be able to save just the path (with no file name) to a variable to use for the output file. This code saves the path and file.
Any help would be greatly appreciated. I didn't know but what there was a function that I could apply to the 'FileName' variable that would remove the file name...don't know.
How can I get the name of a workbook WITHOUT the path when I have the name WITH the path?
To be more specific. When I use "Application. GetOpenFilename" the result is a file name for say "my_book" including the path. With this I can open the file using "Workbooks.Open" and Excel will show me "my_book". But when I want to reference this workbook later on in say "Workbooks(my_book)", then I need the file name without the path. How can I get that one in VBA?
Till now I was using "CurDir" to find out the path after opening the file and just subtracted it from the full file name. This seamed to be fine. But now I had a situation where CurDir gave me a path with abbreviations (~),
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
I have a WinForm, which I'm calling via VBA's Shell command:
Code: Sub Button1_Click() Shell "C:TemplateFuncitons.exe " + ActiveWorkbook.FullName, 1 End Sub
I made this module an Excel Add-in and added it as a button in the quick access toolbar. Now, the idea is for the WinForm.exe and the Excel.xla to always be in the same directory (go hand in hand). I want to make a relative path for the .exe which will be the path to the folder where the .xla is + the string "TemplateFunctions.exe" in the end.
I am having trouble with a macro of mine. Essentially it opens a file, based on user input (Full path is stored in variable FilePath), but then I want to select that workbook, and therefore need just the .xls filename. I figured I would create a new variable, FileName, to pull out the filename.
How can I edit the string in my macro to extract just the filename.xls from the full path?
I have a UserForm with a button that, when clicked, opens up a File Directory Browser that the user can select the file of their choice.
The code is below :
Private Sub DirectoryBrowser_Click() Dim fn fn = Application.GetOpenFilename 'can add parameters. See help for details. If fn = False Then MsgBox "Nothing Chosen" Else MsgBox "You chose " & fn 'now that you have the name, you can open it or do something else End If Workbooks.Open fn End Sub
What I would like is the filepath (of the file they choose) to appear as the displayed caption on another button or label.
How can I run a DOS .bat (batch) file from within a Macro? This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.
I am looking to have these workbooks save automatically to a folder on the C: drive. If the folder I specify is not available on the C: drive, how do I write teh save comand to create it automatically with a prompt
I have a file which is kept on the C Drive. Each day, a different excel file is downloaded to a usb memory stick. The first file reads data from this file. The problem is, the drive letter keeps changing from E to F and the macro then crashes out.
The file on the memory stick always has the same name, so is there a macro to find the drive letter of this file and then use it in my existing macro?
How can I make the files path independent. Lets say I have 6 workbooks, one of them having macro that connects all the six books. While working on my pc, I entered the file locations by myself. The macro runs fine.
Now I have to send the files to someone and would not like them to add their download directory at most places in the code. I want that after downloading, they have to only open the main file and press the button to run macro. How should I do that.
Right now am using this approach:-
sub main{ str 1file; set 1file = Workbooks.Open ("c:/downloads...../1stfile.xls")
