Forum Discussion

MikeH238's avatar
MikeH238
Copper Contributor
Mar 02, 2022

Excel Run Time Error 1004 with Application.Run

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 implements 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. The fact that Testing2.xlsm is opened is tested and 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

 

=====================================

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 (\)
justfilename = "testing2.xlsm"
strFilename = thisdirectory & "\" & justfilename

MsgBox "Filenames: " & vbCrLf & justfilename & vbCrLf & strFilename

MsgBox "Opening... " & strFilename
Workbooks.Open strFilename 'This works fine, the workbook opens


MsgBox "Click ok to test..."
MsgBox "Testing.. " & "testing2.xlm"
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

 

 MsgBox "Running... testing2.xlsm!test"
Application.Run ("testing2.xlsm!test")

'At this point I get the 1004 error


End Sub

 

'The following is a routine I wrote to convert server URLs to PC filepaths

'I need it because I use OneDrive

Function valMenuDirectory() As String

'Get Directory of Menu
Dim CurrentDir As String, DocLoc As Long, DocLen As Integer, DocEnd As String
Dim DocEndWidth As Integer, DocStart As String, Doc As String

On Error GoTo BadPath
CurrentDir = ThisWorkbook.Path
On Error GoTo 0
DocLoc = InStr(CurrentDir, "Documents/")
If (DocLoc > 0) Then
DocLoc = DocLoc + 10
DocLen = Len(CurrentDir)
DocEndWidth = DocLen - DocLoc + 1
DocEnd = Mid(CurrentDir, DocLoc, DocEndWidth)
DocStart = Environ("ONEDRIVE") & "\"
Doc = DocStart & DocEnd
Doc = Replace(Doc, "/", "\")
End If
If (DocLoc = 0) Then
Doc = ThisWorkbook.Path
End If

valMenuDirectory = Doc

Exit Function

BadPath:
MsgBox "Bad path exists in valMenuDirectory. Excel will close."
Application.Quit

End Function

 

=================================

This is the code of the testing2.xlsm workbook that is launched

Code is in ThisWorkbook

=================================

Sub test()
MsgBox "here at test"

End Sub

 

Any ideas??????

Resources