Trying to Open Files Not Located in Specified Path

**freybe06** · 07-03-2013, 04:15 PM

Hello Everyone,

I have a macro that has run just fine as recently as yesterday, however, when I try to run it today, I keep getting a "Run-time error '1004': Microsoft Excel cannot access the file..."

My problem is that the file the error message lists is not even in the path that is specified. My code tells it to open any .xls file within a certain folder.

Does anyone know why the macro would try to open a file that isn't even located in the path? Does this sound like a network or server issue?

Thanks!

**JBeaucaire** · 07-03-2013, 04:33 PM

Sounds like the logic that constructs the filename to be opened and/or the command that tries to open the file using the constructed filename is faulty. Show us, and point out where the error lies when you DEBUG, which line of code?

**freybe06** · 07-03-2013, 05:25 PM

JBeaucaire - Could it really be the code though? I used the same exact file with the same exact path yesterday and it worked just fine. I opened it up today, tried to run it without changing anything, and now it doesn't work.

It is part of a larger macro, but here is the part of code this pertains to:

Please Login or Register  to view this content.

The error occurs at "Set oWbk = Workbooks.Open(sPath & "\" & sFil)"

As you can see in the code, I paste the path of the folder I would like the macro to go to in cell A16 of the "Info" sheet. The macro then goes in to each xls file in that folder and copies a section of the document. But like I said, the error that I'm receiving is telling me that a sheet that isn't even located in the folder does not exist. I had a colleague run the same thing and he got the same error only the error named a different file that didn't exist.

**JBeaucaire** · 07-03-2013, 06:48 PM

Should that path separator be "/" instead of "\" ?

Or should it be there at all? Perhaps:

Please Login or Register  to view this content.

**freybe06** · 07-05-2013, 10:01 PM

sPath definitely needs to be there because that is where the path to the folder is pasted. Since the path is pasted without the "\" at the end, the "\" also needs to be in there.

I'm really not sure if this is a code issue. This morning I opened up the file, did not change a thing, and it worked fine. Now, 14 hours later, I opened it up again and did not change a thing and its back to giving me the error.

I guess this might be more of a server issue or something along those lines, but I'm not really sure where else to post this question since it only happens when I run this macro.

**JBeaucaire** · 07-06-2013, 12:40 AM

Earlier, near the top, you have this line:

Please Login or Register  to view this content.

That means you have set Excel's local directory to sPath, whatever that may be. That means you shouldn't need to include the path, you're already in it.

Please Login or Register  to view this content.

During the time when the macro is actually working, try that edit, you'll find it is still working.

Then the next time it ISN'T working, click on DEBUG and carefully hover your mouse over sPath and sFil in the line of code that is highlighted... what are the current values.

Or press CTRL-G to open the immediate window while that line of code is still highlighted, and type this command into that window?

? sPath & "\" & sFil

What response do you get? Is it exactly right? Even a single errant space (is there one hidden in there anywhere?) will make it not work.