Access Call to Excel Macro Not Completing -- Automation Error 404

[AlexB123]

Hi all,

I have a Access Sub that Opens an excel macro workbook and runs a module. When testing within Excel, the macro runs fine. When I call the sub from a form button, it breaks on "xlApp.Run "MacroPart2".

I have been playing with the code ... previously, I was storing a workbook, saved while the macro runs, as a macro-enabled workbook. I recently changed this to a ".xlsx" format ...

Apart from the above, I have checked my code and don't see any obvious errors.

Any ideas? Thanks!

Private Sub Command13_Click()
'On Error GoTo ErrHandler
On Error GoTo 0
DoCmd.SetWarnings False
Dim r1 As String, r2 As String, macroPath As String
Dim filePath1 As String: filePath1 = "C:\Users\Alex\DB\"
Dim filePath2 As String: filePath2 = filePath1 & "DB Files\"
Dim savePath1 As String: savePath1 = filePath1 & "Archive\Data\"
Dim savePath2 As String: savePath2 = savePath1 & Year(Now()) & "\"
Dim savePath3 As String: savePath3 = savePath2 & Month(Now) & " " & MonthName(Month(Now)) & "\"
    
        Call CheckForPaths(filePath1, filePath2)
        Call CheckForPaths(savePath1, savePath2)
        Call CheckOnePath(savePath3)
    
    r1 = MsgBox("Are you ready to run Macro Part 2?", vbYesNo)
        If r1 = vbYes Then
    
            Dim xlApp As Object
            Dim xlWB As Object
            Set xlApp = CreateObject("Excel.Application")
            Set xlWB = xlApp.Workbooks.Open("C:\Users\Alex\DB\DB Files\UploadMacro_Dev.xlsm"): DoEvents
            xlApp.Visible = True
            xlApp.Run "MacroPart2"
            DoEvents
            xlApp.Quit
            Set xlApp = Nothing
            Set xlWB = Nothing
        Else
        
            MsgBox "Please run the macro when files are ready."
            Exit Sub
        
        End If
    MsgBox "Process Part 2 has completed."
    
DoCmd.SetWarnings True
Exit Sub
    
ErrHandler:
        MsgBox "There was an error. Please see an admin."
        DoCmd.SetWarnings True
        Exit Sub
        
End Sub

 

[xenou]

If your save method is changing or the file type you have to make that explicit in your save. You should check your save code. What did you change?

 

[AlexB123]

If your save method is changing or the file type you have to make that explicit in your save. You should check your save code. What did you change?

I changed:

mainWB.SaveAs savePath3 & "OCR_Query_Data_" & Format(Date, "mmddyyyy") & "_Mod_2.xlsm", 52

to:

mainWB.SaveAs savePath3 & "OCR_Query_Data_" & Format(Date, "mmddyyyy") & "_Mod_2.xlsx", 51

 

[AlexB123]

Fixed it!!!

The problem was my closing the macro workbook at the end of the module ... Whereas the last line in my code was:

    mainWB.Close False
        
Application.DisplayAlerts = True
Application.ScreenUpdating = True
macroWB.Close False
Exit Sub

I merely commented it out, and it ran:

    mainWB.Close False
        
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'macroWB.Close False
Exit Sub

Can anyone explain why this prevented the "xlApp.Run" from completing?

 

[xenou]

If a macro is in a workbook and you close the workbook I don't think it makes sense to try to run it anymore.
But not sure - I'm guessing about where the code is because I have to try to workout your code flow from just the snippets you given and most of your code (on the Excel end) is unknown.