Running 2 subs in excel from access vba - first runs, second doesn't

Question

I have an Access database that calls a sub in Excel and runs it perfectly. Then it flicks back to access, asks a question and then, depending upon the answer of that, should call a second sub in the same Excel spreadsheet that was already open.

Both subs in Excel are 'public' and both sit under "thisworkbook' and I definitely have the name of the second sub correct.

Access code is below. (xlApp is earlier defined by

Set xlApp = CreateObject("Excel.Application")

I get a run-time error 424 "object required" when I hit the second .run MacroName line.

With xlApp
    .Visible = True
    .Workbooks.Open progsPathName & "excel_for_plots.xlsm"
    MacroName = .ActiveWorkbook.Name & "!" & "ThisWorkbook.do_the_country_stuff"
    .Run MacroName

   ' check the labels
   m = MsgBox("Are the labels ok?", vbYesNo, "Label positions")
   If m = vbNo Then
       MacroName = .ActiveWorkbook.Name & "!" & "ThisWorkbook.first_check"
       .Run MacroName
    End If
End With

I have tried checking the sub names, checking they are public, calling the sub something different, using the immediate window to check the 2 MacroName strings are the same except for the sub names. I always get the same error.

Answer 1

ugh, I knew that would happen as soon as I posted! So it seems that the error was actually within the excel sub that was being called. When I hit debug it only highlighted the access line that was calling the excel sub but it was the excel sub that required the object.

Leaving this up incase it helps someone else as I've spent ages trying to work out what my access problem was, not my excel one.

Answer 2

Open Excel From Access

• Here are just some ideas (tips). I can't see the rest of your code so maybe you have already considered some of it.

Option Compare Database
Option Explicit

Sub DoTheExcel()
    Const ProcName As String = "DoTheExcel"
    ' Use an error-handling routine!
    On Error GoTo ClearError
    
    ' Which is it?
    ' Late-Bound
    Dim xlApp As Object: Set xlApp = CreateObject("Excel.Application")
    ' Early-Bound: Tools->References->Microsof Excel 16.0 Object Library
    'Dim xlApp As Excel.Application: Set xlApp = New Excel.Application
    
    ' Use a workbook variable!
    Dim wb As Object ' late binding
    'Dim wb As Excel.Workbook ' early binding
    
    Dim MacroName As String
    Dim m As Long
    ' Use a boolean to see if it was successful!
    Dim IsSuccess As Boolean
    
    With xlApp
        .Visible = True ' out-comment when done testing
        Set wb = .Workbooks.Open(progsPathName & "excel_for_plots.xlsm")
        MacroName = "'" & wb.Name & "'!" & "ThisWorkbook.do_the_country_stuff"
        .Run MacroName
    
        ' check the labels
        m = MsgBox("Are the labels ok?", vbYesNo, "Label positions")
        
        If m = vbNo Then
            MacroName = "'" & wb.Name & "'!" & "ThisWorkbook.first_check"
            .Run MacroName
        End If
        IsSuccess = True
    End With
    
ProcExit:
    On Error Resume Next
        ' Save and close the workbook.
        If Not wb Is Nothing Then
            wb.Close SaveChanges:=IIf(IsSuccess, True, False)
        End If
        ' Quit the instance of Excel.
        If Not xlApp Is Nothing Then xlApp.Quit
    On Error GoTo 0
    Exit Sub
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Sub