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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,289
Office Version
  1. 365
Platform
  1. Windows
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 ?

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

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows
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 ?

VBA Code:
'        DoEvents
'        wb.Worksheets("Menu").Select
'        Application.Run "'" & wb.Name & "'!Load_Budget"
        wb.Close SaveChanges:=True
'        DoEvents
Depends on your need

 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,159
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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

Well-known Member
Joined
Feb 23, 2021
Messages
2,289
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 16, 2017
Messages
1,159
Office Version
  1. 365
  2. 2010
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?
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,908
Hi marimor02. After reading #6 above, it reminded me of this thread at this link. Maybe U have a file registry error? HTH. Dave
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
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.
 

Forum statistics

Threads
1,148,250
Messages
5,745,645
Members
423,965
Latest member
visionquest1972

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