+ Reply to Thread
Results 1 to 7 of 7

Workbooks.Open method and runtime error 1004 - "file couldn't be found".

  1. #1
    Registered User
    Join Date
    02-25-2014
    Location
    Poznan, Poland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Workbooks.Open method and runtime error 1004 - "file couldn't be found".

    Hi guys,

    first of all - as I'm new to VBA and to the Forum - welcome everyone, and I hope I don't breach any rules here.

    I know that there's been many things said on the Runtime Error 1004 while executing the Workbooks.Open from VBA, and I searched for it, but couldn't find anything that would match my scenario (and solve the problem).

    Background
    I have two files in one folder (C:\PB):
    TestBook01.xlsx
    2013_12_TestBook02.xlsx (the '2013_12' bit will change every quarter)

    I also have the following code in my PERSONAL.XLS:

    Please Login or Register  to view this content.
    I open the TestBook01 and try the code (just go by F8). Not surprising, it stops at the Application.Workbooks.Open sFile line, and I get the Runtime error '1004':

    The file '2013_12_TestBook02.xlsx' could not be found. Check the spelling...



    Now, the fun part:

    1. As you can see, the code actually "finds" the file in the folder (it knows that the * bit means '2013_12') despite claiming that the file could not be found (also: the ActiveWorkbook.Path is resolved properly as C:\PB)
    2. There is a "workaround": if I open the TestBook02 manually, then I Save as... (same filename, same folder = just overwrite the original) and close it - the macro works like a charm... :/
    3. The workaround works perfectly until... the comp is rebooted. Then, even though nothing's changed (folders, file names etc) - it gets back to the good old Runtime Error 1004.

    I would be grateful if you can share some insights here.

    Thanks,
    Piotr
    Last edited by PHBlaz; 02-28-2014 at 01:37 PM. Reason: Typo in explanation points: "ActiveWorkbook.Path" was "Activesheet.Path"

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Workbooks.Open method and runtime error 1004 - "file couldn't be found".

    Piotr

    Dir only returns the filename so you need to precede it with the path when opening the file.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    02-25-2014
    Location
    Poznan, Poland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Workbooks.Open method and runtime error 1004 - "file couldn't be found".

    Hi Norie,

    that was like instant reply

    Shouldn't the ActiveWorkbook.Path (TestBook01 being ActiveWorkbook and in the same directory as the 02 I'm trying to open) take care of this?
    And also - as I said, there are times (after manually saving the 02 file) that the code works perfectly.

    Thanks,
    Piotr

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Workbooks.Open method and runtime error 1004 - "file couldn't be found".

    Piotr

    You need to include the path when opening file, you aren't including any path..

    Without the path VBA looks in the current directory.

    When you do the save as you are changing the current directory to whatever directory you save the file in, so the file will be found after the save as.

  5. #5
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2016 and 365
    Posts
    848

    Re: Workbooks.Open method and runtime error 1004 - "file couldn't be found".

    Hi guys!
    Piotr, what Norie says is that the Dir gets the name of the file '2013_12_TestBook02.xlsx' but not the whole path.
    Then, in .Open you should tell excel where is the file.
    try like this:
    Please Login or Register  to view this content.
    Norrie, apologies if my intervention was innappropiate.

    Have fun!

  6. #6
    Registered User
    Join Date
    02-25-2014
    Location
    Poznan, Poland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Workbooks.Open method and runtime error 1004 - "file couldn't be found".

    Norie,

    a big ThankYou!

    Even though it was probably one of the most basic mistakes, I've been looking in wrong parts of the code all the time.

    So, the below one seems to be working fine:

    Please Login or Register  to view this content.
    BTW: I stuck to this DIR because it was the only one that seemed to get the * mark properly translated.

    Thanks,
    Piotr

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Workbooks.Open method and runtime error 1004 - "file couldn't be found".

    Piotr

    Glad you got it working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel VBA Runtime error '1004' "If WorksheetFunction.Find" function
    By miguelitoven in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2013, 04:34 PM
  2. Vlook up VBA Error when value not found "runtime error -1004"
    By sriram170 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-06-2013, 01:14 PM
  3. Runtime Error 1004 "Active method of range class failed".
    By new2excel2012 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2012, 07:55 AM
  4. Excel VBA Runtime error 1004 "Select method of Range class failed"
    By jeskit in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2012, 10:16 AM
  5. Runtime Error "1004" Autofill Method of range class failed
    By DonKarlos in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-15-2009, 07:44 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1