Using vba, is it possible to open multiple Excel w

Question

This is not the first time I ask this question, but I have slightly altered the details to hopefully make it clearer. Here is one version of the code I have tried:

Private Sub OpenWbsInPath(zPath As String)
Dim zFile As String
zPath = IIf(Right$(zPath, 1) = "\", zPath, zPath & "\")   ' Ensure zPath ends with "\".
zFile = Dir(zPath & "*.xls*")                             ' "xxxx\*.xls*". OK.
Do While zFile <> ""
    Workbooks.Open Filename:=zPath & zFile      ' Fails with error 1004 on 2nd iteration.
    DoEvents                                    ' Makes no difference.
    zFile = Dir()                               ' Next filename. Path also OK.
Loop
End Sub

I have also tried a similar procedure using CreateObject("Scripting.FileSystemObject"). In all approaches I have tried so far, including one in which I created an array of file names first and then tried to open each file individually, the first file opens OK, then the next file fails to open with error 1004. The really strange thing is that in an older version of the program, all the files open OK. I have even tried copying the identical code, but it still doesn't work in the new location. I am running out of ideas - do I need to create a temporary folder for each workbook before I open it? Seems crazy.

Answer 1

I've run your code, and can confirm it works fine, if the files can be opened, and fails with error 1004 if they can't.

Possible reasons that a file can't be opened include

1. it's corrupt, or.
2. it's already open.

A file can't be open if:

1. you or your code opened it already (is the file containing the code also in the folder?),
2. someone else has it open (is the folder shared?),
3. another instance of Excel has it open (check task manager for extra instances).

Can you manually open the file the code fails on? Add a Debug.Print zFile before the Open to see what file fails