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

 

[marimar02]

What happens if you comment out this line?

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

Can you run the macro more than once then?

Yes. It could be a conflict between two codes. For now, I have pasted the code from the other workbook into this workbook as a workaround (replacing this:

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

). So now, I'm not relying on the other workbook's macro and run it directly by referencing workbooks.

 

[marimar02]

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

Thanks. However, I cannot modify registry. Corporate restrictions.