Thanks Thanks:  0
Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Dec 2014
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,016
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access Call to Excel Macro Not Completing -- Automation Error 404

    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?

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Dec 2014
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Call to Excel Macro Not Completing -- Automation Error 404

    Quote Originally Posted by xenou View Post
    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

  4. #4
    Board Regular
    Join Date
    Dec 2014
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Call to Excel Macro Not Completing -- Automation Error 404

    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?

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,016
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access Call to Excel Macro Not Completing -- Automation Error 404

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •