This error occurred today in the macros of a program I have developed over the last couple of years. Until today, this code has worked perfectly. Today it is broken. No changes made.
In order to isolate the problem I have recreated two small brand new excel worksheets and tested it on two different computers with same results. So I don't think it is corruption of the original file. I don't think it is some unique problem with one PC. I believe it is something to do with how excel is implementing Application.Run.
Big picture: I have two excel files, Testing.xlsm and Testing2.xlsm. In Testing.xlsm, the macro opens Testing2.xlsm, tests it to make sure it is open, and then tries to run a macro in Testing.2 called test. Simple.
Results: Testing.xlsm opens Testing2.xlsm. Testing2.xlsm is verified. But when the macro Application.Run("Testing2.xlsm!test") is run, I get this error:
Run-time error '1004' Application-defined or object-defined error
I am looking for help or ideas to troubleshoot this problem as it uses code that has worked for past two years. Code follows...
=====================================
Here is the code in the launching workbook, Testing.xlsm:
Code is in Sheet6
=====================================
Sub launch()
'Get Directory of Menu
thisdirectory = valMenuDirectory 'this is a function that converts server paths (/} to PC paths (\). It returns a simple filepath C:\User\etc...
justfilename = "testing2.xlsm"
strFilename = thisdirectory & "\" & justfilename
Workbooks.Open strFilename 'This works fine, the workbook opens
Dim TestWkbk As Workbook
Set TestWkbk = Nothing
On Error Resume Next
Set TestWkbk = Workbooks(justfilename)
On Error GoTo 0
If TestWkbk Is Nothing Then
MsgBox "Sorry the File is not open, it is not possible to run the macro."
Else
MsgBox "File is open" 'This test works fine, the workbook is open
End If
'This is the code that throws error:
Application.Run ("testing2.xlsm!test")
'I have also tried:
'Application.Run ("'testing2.xlsm'!test")
'I have also tried:
'Application.Run ("'<full path>\testing2.xlsm'!test")
'At this point I get the 1004 error
CodePudding user response:
If the macro you're calling is in the ThisWorkbook
module then you need to specify that in the argument to Run
:
Application.Run "testing2.xlsm!ThisWorkbook.test"