Workbook.Open second time leads to Run-time error 1004: Method Open of object Workbooks failed

[marimar02]

Hello, I am using a modified version of code that normally has a "FolderPicker" to (1) open all workbooks in a designated folder and (2) to run a macro found in the other workbooks.

Instead of the FolderPicker I just pointed myPath to a range which inludes "\" at the end of the path.

I run into a problem when I run this macro 2nd time. 1st time it goes through the entire macro fine and it does what it's supposed to, but second run it stops at

Set wb = Workbooks.Open(myPath & myFile)

. The only way to run the macro 2nd time is to close the workbook and reopen it as something seems to reset.
I have same macro to load Forecast and cannot load it after Budget unless I close the workbook and reopen.

I should point out that if I leave FolderPicker in place, I can load multiple times with no issues.

thank you for any insight...

Sub ImportBudget()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

myPath = Range("SourcePath")
If Dir(myPath) <> "" Then
NextCode:
    myPath = myPath
    If myPath = "" Then GoTo ResetSettings
    myExtension = "*.xls*"
    myFile = Dir(myPath & myExtension)
    Do While myFile <> ""
        Set wb = Workbooks.Open(myPath & myFile)
        DoEvents
        wb.Worksheets("Menu").Select
        Application.Run "'" & wb.Name & "'!Load_Budget"
        wb.Close SaveChanges:=True
        DoEvents
        myFile = Dir
    Loop
End If
    
    MsgBox "Import Complete!"

ResetSettings:
With Application
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

    Set wb = Nothing
ThisWorkbook.Save

End Sub

 

[Zot]

Try specify the workbook you are running the macro from instead of just
myPath = Range("SourcePath")

specify where Range("SourcePath") is located at.

 

[Alex Blakenburg]

When I comment out the lines as shown in the below, the code runs fine.
I suspect that the one of the Do Events lines is causing something to run out of sequence possibly not closing 1 of the files properly.
Do you need the DoEvents lines ?

'        DoEvents
'        wb.Worksheets("Menu").Select
'        Application.Run "'" & wb.Name & "'!Load_Budget"
        wb.Close SaveChanges:=True
'        DoEvents

 

[Zot]

When I comment out the lines as shown in the below, the code runs fine.
I suspect that the one of the Do Events lines is causing something to run out of sequence possibly not closing 1 of the files properly.
Do you need the DoEvents lines ?

'        DoEvents
'        wb.Worksheets("Menu").Select
'        Application.Run "'" & wb.Name & "'!Load_Budget"
        wb.Close SaveChanges:=True
'        DoEvents

Depends on your need

VBA DoEvents

Excel VBA DoEvents Function With the help of VBA DoEvents, we can make the code run in the background and simultaneously allow us to work with excel

www.wallstreetmojo.com

 

[rlv01]

I pasted your code into a workbook, commented out the Application.Run "'" & wb.Name & "'!Load_Budget" statement, and ran it. It worked fine for me 1st , 2nd or 3rd time. Could something be changing your values for mypath or myfile on the 2nd run?

 

[marimar02]

Thank you all for your inputs. I think I found out why I get 1004 error but not sure how to resolve it.

After I run the code 1st time and it completes the sequence successfully, I then try to open the other workbook manually and get:

1. We found a problem with some content in "Workbook". Do you want to try to recover..." I click Yes and it follows with this:
2. "File in Use" message saying that the file "is locked for editing" and prompts me to open it with Read Only/Notify/Cancel options

So without my expert knowledge, I thought the issue would be here

wb.Close SaveChanges:=True

as @Alex Blakenburg mentions above. I looked in Task Manager to see if the file is open in background somewhere and it's not.

Then I tried commenting out

wb.Close SaveChanges:=True

so that the files remain open. I closed/saved them manually. No luck.

So I'm thinking it's something within this code. I also specified the range of the file path as @Zot suggested. No luck their either.

I'm happy to try/use a more efficient code that will open all files in a specific folder if there is one.

 

[Alex Blakenburg]

Is the issue always with the same file ?
If yes, what happens if you open it manually, run it's budget_load macro and save it.

 

[rlv01]

What happens if you comment out this line?

        Application.Run "'" & wb.Name & "'!Load_Budget"

Can you run the macro more than once then?

 

[NdNoviceHlp]

Hi marimor02. After reading #6 above, it reminded me of this thread at this link. Maybe U have a file registry error? HTH. Dave

How to detect / bypass the Workbook Recovery window and process

I'm working with a Word/VBA system, and as it goes, it opens an XLSX to get some information from it. One of many users reported that nothing came from the XLSX, so I manually opened Excel to open the file which the code should have opened, just to prove it wasn't a permissions / access issue...

www.mrexcel.com

 

[Zot]

Thank you all for your inputs. I think I found out why I get 1004 error but not sure how to resolve it.

After I run the code 1st time and it completes the sequence successfully, I then try to open the other workbook manually and get:

1. We found a problem with some content in "Workbook". Do you want to try to recover..." I click Yes and it follows with this:
2. "File in Use" message saying that the file "is locked for editing" and prompts me to open it with Read Only/Notify/Cancel options

So without my expert knowledge, I thought the issue would be here

wb.Close SaveChanges:=True

as @Alex Blakenburg mentions above. I looked in Task Manager to see if the file is open in background somewhere and it's not.

Then I tried commenting out

wb.Close SaveChanges:=True

so that the files remain open. I closed/saved them manually. No luck.

So I'm thinking it's something within this code. I also specified the range of the file path as @Zot suggested. No luck their either.

I'm happy to try/use a more efficient code that will open all files in a specific folder if there is one.

In that case most likely that there is something wrong somewhere else. It is not the code itself but the code and the Windows combination like @NdNoviceHlp suspected.

Since you said you have use code with Folder Picker, maybe looping using FSO (File System Object) will work. Now you are using the Dir method. Look at this site on how to use FSO. I prefer the section
Using the File System Object (FSO) Late Binding – Method #2 since you don't need to set Reference first.

VBA code to loop through files in a folder (and sub folders)

Looping through every file in a folder is a useful technique to make changes to lots of file. This post provides 6 different methods to achieve this.

exceloffthegrid.com