Results 1 to 12 of 12

Thread: [RESOLVED] Excel Runtime error 1004 FileName could not be found error

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2008
    Posts
    3

    Resolved [RESOLVED] Excel Runtime error 1004 FileName could not be found error

    I am a noob to these boards (and fairly new 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 manually.
      ActiveWorkbook.UpdateRemoteReferences = True
      
    End Sub
    It fails on the line:
    Code:
    Workbooks.Open "DgetData.xls"
    This used to work. Now it doesn't. Please help.

    Cheers,

    Gray
    Attached Images Attached Images  

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel Runtime error 1004 FileName could not be found error

    you need to supply the full path to the workbook to open, as well as the filename
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2008
    Posts
    3

    Re: Excel Runtime error 1004 FileName could not be found error

    I am very sorry, but why should I now need to specify the full path, when I never needed to previously?

    I swear it used to worked perfectly.....

    Also, I want to distribute these spreadsheets to work colleagues, and they might not save the files in the same location as me. So the path would therefore automatically be wrong. As long as the two files were in the same folder, this always used to work just fine.

    Sorry, but I am very confused.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel Runtime error 1004 FileName could not be found error

    there are several reasons why the default path of excel may have changed
    if the 2 workbooks are in the same folder, you could use thisworkbook.path as the path to the second workbook
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Oct 2008
    Posts
    3

    Re: Excel Runtime error 1004 FileName could not be found error

    This sounds very hopeful....
    Please could you give me an example of it's usage, as I am not familiar with the syntax of this command?
    Cheers,
    Gray

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2008
    Posts
    3

    Re: Excel Runtime error 1004 FileName could not be found error

    Got it.
    THANK YOU for your help! Changed the error code line to be:
    Code:
    Workbooks.Open ThisWorkbook.Path & "\DgetData.xls"
    And it works!
    You're a hero.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Excel Runtime error 1004 FileName could not be found error

    glad you got it working, better you figured it out for yourself
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8
    New Member
    Join Date
    Apr 2015
    Posts
    3

    Re: [RESOLVED] Excel Runtime error 1004 FileName could not be found error

    Hi,

    I also have this error in the red sentence:
    Sub EinKopieren()
    Workbooks.Add Template:="Arbeitsmappe" 'LEERE Arbeitsmappe erstellen
    ActiveWorkbook.SaveAs Filename:=Pfad & "\" & TabellenName 'Bewertungs NAME für LEERE Arbeitsmappe festlegen
    BEWERTUNG = ActiveWorkbook.Name 'Tabelle dem Arbeitsnamen BEWERTUNG zuordnen

    Could you help me please.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Excel Runtime error 1004 FileName could not be found error

    this is an old thread marked resolved, you should have started a new thread

    the template argument should be the full path to file, with file extension
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    New Member
    Join Date
    Apr 2015
    Posts
    3

    Re: [RESOLVED] Excel Runtime error 1004 FileName could not be found error

    Thanks for the reply, it works now, but the template has to be in the same place as the macro file and the file that I open, if not it doesnt work. Have any idea why?

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Excel Runtime error 1004 FileName could not be found error

    Have any idea why?
    it should not matter, if you specify the full path to the template file
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12
    New Member
    Join Date
    Apr 2015
    Posts
    3

    Re: [RESOLVED] Excel Runtime error 1004 FileName could not be found error

    now it works! thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width