workbooks.opentext causes error number 1004

[dmash]

This really puzzles me.

I have a whole bunch of workbooks.opentext in my code.
The macro sometimes works fine and sometimes it crashes with an error message:
"error number 1004, microsoft office excel cannot access the file <my file name> there are several possible reasons blah blah blah"

If I rerun the macro, it may crash at a different file, or it may not crash at all.

Any idea what I should look for?

Thanks,
Don

 

[GlennUK]

Are these files on a Network shared drive? It may be that network traffic means that Excel times out trying to open the file due to slow access.

Do other people/programs have access to these files? Maybe someone or something has the files open ahead of you.

 

[dmash]

That's correct. The files are on a network shared drive and multiple people may be using the file at the same time.

However, the macro does not write anything to any of the source files. It only opens the files one at a time, copy the content to a specific worksheet in the excel file, close the source file, then continue with the next source file.

I could have the file open manually using a different excel workbook, then run my macro and it would run fine. Thus I don't think it's an issue where the source file is locked. It seems to be a completely random event to me as to when and which file would generate the run time error.

Each of the source files has small file size, mostly between 5 and 10 kb, none greater than 20 kb.