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

marimar02

Board Regular
Joined
May 21, 2010
Messages
126
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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...

VBA Code:
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

Board Regular
Joined
May 21, 2010
Messages
126
Office Version
  1. 365
Platform
  1. Windows
What happens if you comment out this line?

VBA Code:
        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:
VBA Code:
        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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

marimar02

Board Regular
Joined
May 21, 2010
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Hi marimor02. After reading #6 above, it reminded me of this thread at this link. Maybe U have a file registry error? HTH. Dave
Thanks. However, I cannot modify registry. Corporate restrictions.
 

Forum statistics

Threads
1,144,161
Messages
5,722,838
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top