Excel Vba Workbooks.open Filename Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow excel vba workbooks.open runtime error 1004 the company Business Learn more about hiring developers or posting ads with us Stack Overflow
Workbooks.open Vba
Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of workbooks.open error handling 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up VBA Open Workbook from Macro fails “(path & filename) could not be found. Check the spelling…” up vote workbooks.open read only 0 down vote favorite I have the following code, which should simply open a workbook from a master workbook to allow me to paste some information which I will later retrieve from a database. I receive the error in the title every time I try to open a workbook. I have used similar code before with no problem at all. The files are kept on a server away from my desktop, but the
Method Open Of Object Workbooks Failed
path has been correct each time. I've checked over and over again. What could be the culprit? Sub copyDealerDataToWorkbook(ByVal targetID As Integer) Dim mainWB As Workbook Dim directory As String Dim fn As String Dim aFile As Excel.Application Set aFile = CreateObject("Excel.Application") Set mainWB = ActiveWorkbook directory = ActiveWorkbook.Path 'append a "\" If InStrRev(directory, "\") < Len(directory) Then directory = directory & "\" End If fn = targetID & ".xls" aFile.Application.Visible = True aFile.Workbooks.Open directory & fn 'ERROR ON THIS LINE aFile.Parent.Windows(1).Visible = True End Sub I have also tried the following code... Sub copyDealerDataToWorkbook(ByVal targetID As Integer) Dim foreignWB As Workbook Dim mainWB As Workbook Dim directory As String Dim fn As String Set mainWB = ActiveWorkbook directory = ActiveWorkbook.Path 'append a "\" If InStrRev(directory, "\") < Len(directory) Then directory = directory & "\" End If fn = targetID & ".xls" Set foreignWB = Workbooks.Open(fileName:=directory & fn) 'ERROR ON THIS LINE End Sub excel vba share|improve this question edited Sep 4 '14 at 17:45 n8. 778420 asked Sep 28 '11 at 20:24 whitaay 3461314 First, Do you mean to have the
in the variable declarations? Second, can you tell me what your directory string is returning? –TheFuzzyGiggler Sep 28 '11 at 20:41 Of course not... excuse the
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about open file vba hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask run time error 1004 file could not be found Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join
Run Time Error 1004 Method Open Of Object Workbooks Failed
them; it only takes a minute: Sign up Excel vba workbooks.open event giving a runtime error 1004 - The Disk Is Full up vote 1 down vote favorite I'm getting a 1004 disk is full error when I try http://stackoverflow.com/questions/7589149/vba-open-workbook-from-macro-fails-path-filename-could-not-be-found-check and open a file using: Workbooks.Open Filename:=inputfolder & "\" & inputfile, ReadOnly:=True I have also tried this with the filepath and name hard-coded, with the same result. Does anyone have any ideas as to the cause? excel vba share|improve this question edited Jul 21 '14 at 14:39 demongolem 5,201114874 asked Jul 21 '14 at 14:37 Carl 612 Try to check if it works with hard-coded full path to file. If it does, then check your variables. http://stackoverflow.com/questions/24867818/excel-vba-workbooks-open-event-giving-a-runtime-error-1004-the-disk-is-full Regards, –Alex Bell Jul 21 '14 at 14:41 Still does it I'm afraid. –Carl Jul 21 '14 at 15:02 Does this error occur with every file you try, or just one? –Geoff Jul 21 '14 at 20:01 I don't think this is a VBA issue - more an Excel one, as I'm now getting the same message when I try and open a workbook manually (it eventually opens but with an error - lost visual basic project)........ –Carl Jul 22 '14 at 8:36 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote I've run into this error while trying to open from a shared location, it was caused by not having write permissions on the folder. If this is the case, try running the same code but with workbooks saved locally on your PC. share|improve this answer answered Jul 21 '14 at 14:41 Bizmark 1234 Gave this a go, but same error as before :-( –Carl Jul 21 '14 at 15:04 add a comment| up vote 0 down vote First, try to check if it works with hard-coded full path to file, like (for example): Workbooks.Open Filename:="C:\\somedir\somefile.xls", ReadOnly:=True If it does, then check your variables to make sure they point to right location. If the error persists, try to open ANY Excel file in that particular folder (just for test purpose
FileName could not be found error If this is your first visit, be sure to check out the FAQ by clicking the link http://www.vbforums.com/showthread.php?541906-RESOLVED-Excel-Runtime-error-1004-FileName-could-not-be-found-error above. You may have to register before you can post: click http://www.rondebruin.nl/win/s9/win003.htm the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 12 of 12 Thread: [RESOLVED] Excel Runtime error 1004 FileName could not be found error Tweet Thread Tools Show Printable Version error 1004 Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Oct 3rd, 2008,03:31 AM #1 GrayS View Profile View Forum Posts Thread Starter New Member Join Date Oct 2008 Posts 3 [RESOLVED] Excel Runtime error 1004 FileName could not be found error I am a noob to these boards (and fairly new time error 1004 to Excel VBA), so please forgive if this is a dumb question. I have an Excel 2003 spreadsheet ("DgetWork.xls") with some VBA to automatically open a second spreadsheet ("DgetData.xls") when the first is opened. It used to work perfectly. Then work gave me a new laptop (still Excel 2003 though). Now it always fails with error: Run-time error '1004' 'DgetData.xls' could not be found. Check the spelling.... Please can somebody help me to get this working again? I have installed all the add-on packs to no avail. The spelling of the file name is correct, and the two spreadsheets are in the same folder. The Excel 2003 VBA in the first spreadsheet is as follows: Code: Function FileIsOpen(pFileName As String) As Boolean 'Function to test if a spreadsheet is open or not. On Error Resume Next FileIsOpen = Len(Excel.Application.Workbooks(pFileName).Name) End Function Private Sub Workbook_Open() ' Open data worksheet. If Not FileIsOpen("DgetData.xls") Then Workbooks.Open "DgetData.xls" End If ' Switch focus back to orignal worksheet. Workbooks("DgetWork.xls").Activate ' Now force update of links
Mail / PDF Files / Folders Userforms Pictures / Charts Excel settings Other Topics Mac Add-ins Articles Excel for Windows Excel for Mac Search Contact Test if Folder, File or Sheet exists or File is open On this page, you will find code to check if a folder, file or Sheet exists and code to test if a workbook is open. In every example, I use a MsgBox to tell you if the folder, file or sheet exists or not. Replace the MsgBox for the code that you want to run when the folder, file or sheet exists or not. If you want to test the code you must copy the code and functions in a standard module of your workbook's project. Copy the code in a Standard module of your workbook, if you just started with VBA see this page.Where do I paste the code that I find on the internet Test if Folder exist Below are three examples to test if the folder "test" existFolderPath = "C:\Users\Ron\test" The first one uses VBA Dir and the other two use FSO (FileSystemObject). Note:read the information in the second FSO example good because you must set a reference to the "Microsoft Scripting Runtime" in the VBA editor if you want to use this example. When you use the first FSO example you not have to set a reference in the VBA editor to "Microsoft Scripting Runtime" because it use Late Binding. When you use the Intellisense help you see that there are a lot more nice options when you use FSO. Sub Test_Folder_Exist_With_Dir() Dim FolderPath As String FolderPath = "C:\Users\Ron\test" If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\" End If If Dir(FolderPath, vbDirectory) <> vbNullString Then MsgBox "Folder exist" Else MsgBox "Folder doesn't exist" End If End Sub Sub Test_Folder_Exist_FSO_Late_binding() 'No need to set a reference if you use Late binding Dim FSO As Object Dim FolderPath As String Set FSO = CreateObject("scripting.filesystemobject") FolderPath = "C:\Users\Ron\test" If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\" End If If FSO.FolderExists(FolderPath) = False Then MsgBox "Folder doesn't exist" Else MsgBox "Folder exist" End If End Sub Sub Test_Folder_Exist_FSO_Early_binding() 'If you want to use the Intellisense help showing you the properties 'and methods of the objects as you type you can use Early binding. 'Add a reference to "Microsoft Scripting Runtime"