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

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
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!

Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Dec 19, 2014
Messages
203
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:
Code:
mainWB.SaveAs savePath3 & "OCR_Query_Data_" & Format(Date, "mmddyyyy") & "_Mod_2.xlsm", 52

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

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
Fixed it!!!

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

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

I merely commented it out, and it ran:

Code:
    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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,277
Messages
5,674,799
Members
419,526
Latest member
ranjit446

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