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

Piotr

Dir only returns the filename so you need to precede it with the path when opening the file.

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.

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!

Piotr

Glad you got it working.