Forum Discussion
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??????
1 Reply
- EricStarkerFormer Employee
Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future.