Userform Show Path/file Error 75
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
'Dim frm As UserForm
'For Each frm In UserForms
' Unload frm
View Complete Thread with Replies
Related Forum Messages:
UserForm: File/Path Access Error & I/O Error
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.
Path/File Error Showing UserForm
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.
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.
UserForm As Add-In (Getting Error 9 On .show)
I have a userform that is working perfectly, but when I try to make it an add-in I can't even get it to load. When loading the userform as an add-in I get the error "Run error nr. '9'.: The Index is out of the interval" (freely translated from Swedish to English). The code I'm using to load the userform is
UserForm Show Causes Error On 1 PC
I've created a userform on one computer which launchs when I open the excel workbook. The userform contains comboboxs and a picture. But when i saved it on another computer and tried to open it and it errors. (Run-Time error 9). (Still works fine on the computer that created it). All references are to this workbook only. Userform is contained within the Forms folder of this workbook(not personal.xls) and the userform name is PDM_SPLASH1. Also, if i go into VBA I can see the form, but it wont launch when i try to play in manually. (Run-Time error 9)
Private Sub Workbook_Open()
Worksheets("Instructions").Visible = False
Worksheets("SelectDB2").Visible = False
Worksheets("REPALIGNMENT").Visible = False
Worksheets("PDM").Visible = False
Worksheets("TABLES").Visible = False
Worksheets("DISPENSING").Visible = False
Worksheets("DISPENSED").Visible = False
Worksheets("OpportunityAnalysis").Visible = False
Worksheets("ENGINE1").Visible = False
Worksheets("POTENTIALCONTRACTLOSS").Visible = False
Show Userform - Invalid Property Value Error
I created a form that my employees could use to log their work. When I toggle between the combobox and the qtytextbox I get an "invalid property value" error that debugs to the line of code that opens the userform. I have no idea how to fix this. The spreadsheet has a button that shows the userform. This code is:
Private Sub rectangle1_click()
If frmLCWork.Visible = False Then frmLCWork.Show
The userform has a combobox for employees to select the completed "task", a quantity textbox and then an "add to log" button. The form code is:
Private Sub cmdAdd_Click()
Dim sStep As String
Dim Row As Long
'check for a task
If Trim(Me.cbxTask.Value) = "" Then
MsgBox "Please specify which task you completed."
Trim Full File Name & Path To File Path Only
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?
Show Flash .swf File On Userform
Private Sub CommandButton1_Click()
Call ShockwaveFlash1.SetVariable("testVar", "Message B")
'This sets the _ value of the variable in the Flash Project.
Private Sub ShockwaveFlash1_FSCommand(ByVal command As String, ByVal args As String)
Text1.Text = args
'this catches the action sent by Flash, and uses it in the TextBox.
Private Sub UserForm_Activate()
Call ShockwaveFlash1.LoadMovie(0, CurDir + "/vbSample.swf")
'This loads the movie that was made into level 0 of the project.
(I got from this link http://www.kirupa.com/developer/mx/flashvb.htm) As you can see I have adapted it to work in Excel 2007. Now - when i first run it - the flash file loads and shows in the userform. The only nag being that the .swf file doesnt pass the text into the textbox on the userform. But once i exit the Excel workbook and reload it - the .swf file doesnt show up at all no matter what i do.
Show UserForm Upon Opening File/Workbook
I have a range named "start" and I have a form named "fMenu". I have used this same code in other spreadsheets to have the userform automatically become visible when the spreadsheet is opened and it works in other workbooks. Apparently I have been struck with the stupid stick this morning. I have a hotkey of "M" and when I use control M the form shows up and the macros invoked by each button on the form are working properly.
Import CSV File Using Cell As File Name & Active Workbook Path
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Dim wsName As String
wsName = ActiveCell
LastRow = .SpecialCells(11).Row
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
Save File As Text Using Cells For Path & File Name
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
User Input For File & Path For File Copy
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?
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
Show Workbook Location & Path
Is there a way to display the location of an active workbook on my local drive and save it into a varible. I'm new to Excel and VBA but I'm trying to create a template to use for multiple vehicles. When the macro is run...
1) the workbook will have to identify what Workbook and worksheet it is in and save it as a varible so that it can access it later. Each workbook is specific to a vehicle.
*(The reason is because I will need to refer to the address of the original workbook when pasting the copied data from the "OilChart" Workbook.)
2) Copy the vehicle identification # and save as a varible. (Dim vehicleNumber As Variant)
3) Open another workbook on the local drive (OilChart.xls)
4) Run the macro in the new workbook (OilChart.xls) to update the data
5) Search Column B in (OilChart.xls) for matches of the vehicleNumber, and for all rows that match, select and copy all rows and paste it back into the orig workbook (Vehicle Specific Workbook).
This is the code I have so far.
Private Sub btnFind_Click()
Dim vehicleNumber As Variant
Dim sourceLocation As String
Dim sourceFileName As String
Dim fileName As String
Application. ScreenUpdating = False
I have'nt figured out how to do step 5 yet so if anybody has any ideas I'd be open to them. I tried looking at Aaron Bloods Kickbutt VBA Find Function but I got a little lost on how his code would relate to my code. It also looks like it doesn't copy or paste anything to another workbook.
Split File Name From File Path
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)
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.
Separate File Name From File Path
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.
Get A File Path Using Vba
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.
FileName = Application. GetOpenFilename _
FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
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.
Get Rid Of Path In File Name
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 (~),
Use The Path Of My File
how can I refere to the path whaere my addin is located without having to write it
i tried with this, but....................
p = Application.Path
AddIns("HTML").Installed = True
Return File 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()
fn = Application.GetOpenFilename 'can add parameters. See help for details.
If fn = False Then
MsgBox "Nothing Chosen"
MsgBox "You chose " & fn
'now that you have the name, you can open it or do something else
What I would like is the filepath (of the file they choose) to appear as the displayed caption on another button or label.
Extract File Name From Path
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?
MoveFolder. Run-time Error 76, Path Not Found
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
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.
Launching Windows Explorer Path Error
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.
Dim PID As Double
Dim strRootPath As String
Const strExpExe = "explorer.exe"
Const strArg = " " '" /e,/root, "
'// Change rootpath here
strRootPath = "J:MyDirectoryWilliams,Bob 7264" ..............
Check If Certain File Exist On Path
I am using below mentioned Code for checking if certain file exist on Path.
Its working on my home PC (XP SP2 Excel 2003).
BUT its not working on my office PC (XP SP2 Excel 2003).
Can somebody tell me why its not working in my office PC ( or if its missing something, then from where can i download it.)
Const strFolder = "C:Documents and SettingskreshnrDesktopTest"
Dim fso, msg, i
Dim rngData As Range
Set fso = CreateObject("Scripting.FileSystemObject")
Set rngData = Sheets("Sheet1").Range("A1")
Do While .Offset(i, 0).Value ""
If (fso.FileExists(strFolder & .Offset(i, 0).Value & ". ")) Then
.Offset(i, 2).Value = "Yes"
.Offset(i, 2).Value = "No"
i = i + 1
Correct Declaration For A File Path
I am using the format of using variables for declaring file paths and document names in VBA however I want to make sure I am doing it properly...
Dim Server, VersionName As String
Server = Worksheets("Calculation Matrix").Range("CalculationMatrix_Server").Value
VersionName = Worksheets("Calculation Matrix").Range("CalculationMatrix_VersionName").Value
ActiveWorkbook.SaveAs Filename:="" & Server & "uploadcomplete" & VersionName & "", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Should these variables be declared as String?
Macro That Will Open The File Path
hi, i need a macro that will open the below file path, and when i select the file i would like the macro to copy the active sheet from the source file being opened from range A2:L10000 and then paste special values to destination CELL A16 of the activesheet of the workbook.
I would like the date format for the below to be generic so it will apply for all months and years.
SaveAs VBscript File Path
I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.
What I believe is the pertinent part of the code that needs adjusting is:
"Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False "
How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies
Determine File Name & Path On Opening
Const mFileLoc As String = "c:documents and settingsdcrakerdesktop est location.xls"
Set myPlace = Sheet1. Range("F1") 'currently has formula ->=CELL("filename",A1)<-
If myPlace = mFileLoc = False Then
MsgBox ("Warning, File is in incorrect location. Please contact David for corrections"), vbCritical
'add codes to disable macros from running (still need to find this)
>What happens, is it always tells me that the file is in the wrong location. I want it to be able to see what is hardcoded in VB and compare it to active workbook paths, and if it sees a change then it knows that it is in a different location.
> used the macro recorder also to make sure that the filepath was correct in the code also.
It happened, and could happen again someday. The company upgraded the server, and all the files were moved around. When the User accessed the file that I had created awhile ago, he couldn't figure out what happened. For some reason, my computer did okay on finding the correct file.
What it's suppose to do:
It will popup a message, informing the User that the file is no longer where it should be and may not work properly.
And then I have to add/find codes that will not allow the macros to run, so that I can come in and fix the problem.
It took me about 2 1/2 hours to figure out what happened and since I might not remember next time this could happen.
Find File Path To USB Drive
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?
Concatenate Columns Into File Path
I am creating an excel file for a mail merge following this process. I need to take the three columns below and enter the path to the corresponding chart. I have all the charts created in a directory. The naming convention for all of the files is XX-XX-XX.gif. The X's represent the numbers below without the decimal places. Therefore the example below would be 44-43-27.gif. Is there anyway to create a formula to drag down instead of entering the filename's manually? In the end, I want to have the full file path as shown in the XLS.
4.4 4.3 2.7
I have 700+ rows to do this for, therefore I want a formula or VBA solution instead of the manual donkey work.
Code Dynamic File Path
I have a question. How to code the filepath name to make sure it change when i move my files from one directory to another. Example:
I need to update 4 files from this directory, but i am moving them to another directory.
c:documents and settingTesting
The file being able to follow which ever folder.
Macro For Relative File Path
I have a macro in excel that saves to an external text file, but the excel workbook (and the text file) will be copied to different folders so i need it to alway read the file from the location the worksheet was opened from. I hope that makes sense
Ive tried using ActiveWorkBook.path but that doesnt work, maybe im just using it the wrong way
Get Relative File Path Of Workbook
I work on multiple workbooks. They all calculate a lot of functions and I am forced to create multiple workbooks to do these calculations. Linking to other workbooks basically displays the results of workbook1 and works from there.
I need to keep an overview of these files, so I store them in different folders all within the same 'master' folder. So let's say: master folder is FolderMaster, and this contains folderA, folderB, folderC. FolderA contains workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally folderC contains workbookC1 and workbookC2.
Suppose that this hierarchy might change. Moving eg workbook A2 to folderB. This would cause a #REF if it is linked in another workbook.
So I thought: organize your hierarchy in a 'master workbook' and store all paths to the individual files in a worksheet. If I link to a specific folder I do not directly link to the actual folder, but would use INDIRECT and grab the filepath from the masterworkbook. If the path to a workbook change I do not have to update all individual links, but only change the path in the masterworkbook. Right?
But 'how' is the big one here. How do you formulate your path to a file? It can not be the entire file path since that's variable... (sometimes 'hd:User1:....'; sometimes 'HD:User2'..."). So I think I need something as mentioned in this office help thing. But I have no clue. I do not want to use VBA, I hoped for another solution to this.
File Path Changes On Linked Formula
We have a set of workbooks with a linked Vlookup formula. When we email the file to staff that do not have access to the linked file, the linked formula seems to change the directory. see below
=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Formula after user opens email with the drive changed automatically to C
=VLOOKUP($A$30,'C:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Note that the user does not have access to the G drive and they are not updating the links when they open the file.
Two Cells Which Have Formulas Stopping Error Messages Show An Error When Added
See the attached sheet. I am trying to add together two figs which are linked to calculations which have formula built in to stop error messages when there is a 0 / 0 = #value type error. However when these two cells are added, if the cells are blank I get an error message. And if only one cell has a value, I get "" with my existing formula. what I need to do to get a result of 7 if for example cell A4 = "" + B4 =7. At the moment my formula shows "" in the sum total of these cells
Unload Active Userform And Show New Userform
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Application.ScreenUpdating = True
Cancel = True
Trying to use above code to unload active userform and show new userform when red X (close) selected by operator. With the code as is, the Unload Me leaves the form on the screen and displays the RestrictedOptions form. If i remove the Restrictedoptions.Show, the Unload Me does remove the original form.
Fill UserForm ListBox & Show Userform
having trouble with the details of actually making these features work for me. I figured out how to create a UserForm with a ListBox and 2 buttons, but I don't know how to proceed from here.
1. Populate the ListBox in the UserForm with a list of names from the sheet "Totals_Dropdowns", cells K2:K11
2. Make the UserForm pop up and enter the user's selection into cell C40 of the "Regenerate Request"
I know these are very basic operations, and I'm pretty sure I can figure out the rest of my problems once I can get past the above.
Check File Path Of Before Running Macro
I have a spreadsheet with a macro that saves a backup s/s to a particular folder, but the problem I have need the macro to check the filepath is that of the original s/s before running.
Its so that when people take copies of the main s/s and save them to their desktop I dont want the copies being saved in the backup folder
macro is below
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.ReadOnly Then Exit Sub
If MsgBox("Do You Want To Save A Backup?", vbYesNo) = vbNo Then Exit Sub
Dim fso As Object
Dim objFiles As Object
Dim myWkBk As String
Dim myFName As String
Dim BkUpDir As String
Dim CountFiles As Integer
Dim mydate As Double
BkUpDir = "k:singstatappealsappeals trackerBackups"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(BkUpDir).Files
On Error Resume Next
'the actual counting of files takes place here.
If Err.Number 0 Then
CountFiles = 0
CountFiles = objFiles.Count
If CountFiles > 4 Then
MsgBox "You have " & CountFiles & " saved workbooks. Perhaps you should delete some?"
mydate = Format(Now(), "ddmmyy")
myWkBk = ActiveWorkbook.Name
myFName = ActiveWorkbook.FullName
fso.CopyFile myFName, BkUpDir & mydate & myWkBk
Open File Where Path, Name & Sheet Name Are Variables
I'm trying to use vba open a new workbook and then edit it. But i can not select cell in the new workbook. but it can be activated!
it can open the file (fname) and activate the sheets (sName), but will flage an error on range select.
Workbooks.Open fileName:=fPath & "" & fName
Hyperlink Does Not Maintain Path To Target File
I read about Hyperlinks in the August 2006 "OZGRID" Newsletters but found no answer.
Assume 2 workbooks - book1 and book2, both located in "c:" (root of drive C)
Now, In book1 I have declared an HL to book2 using CTRL+K and upon clicking in that cell the book2 is opened.
After MOVING(!) book1 to a new location (D:DATA) and clicking the HL - I get an Error "cannot open the specified file".
While book1 was still in root C: - I tried to force to c:ook2.xls by typing it in the HL address window but after saving, Excel deletes the "c:" and leaves "book2.xls" only – I think because both files were located I the same directory.
I am aware that my "problem" can be solved by using the function HYPERLINK – but I would like to learn (if possible) how to insert an HL using CTRL+K that will maintain the path although moving the file which contains the HL).