How to return code actively back to Access from Excel 1

[TKaminski]

From within Access (XP) I open an Excel workbook and run a macro in that workbook. The last action the Excel macro does is to save and close that workbook. My problem is the VBA code in Access then fails instead of running the next line of code in Access.

How do I get Access to continue running after the Excel macro has closed the workbook?

I want to keep Excel open for other uses later in the code so only the workbook is closed.

Both the Access DB and the Excel workbook are on the local C: drive and not being run over a network. Permissions are not an issue.

Access gives me the error code 440 Method ‘Run’ of object’_Application’ failed.

Here is the sub:

Public Sub subOpenExcelWorkbookAndRunMacro()
On Error GoTo RangeNamed_ErrorHandling
    
Dim appExcel As Excel.Application
Dim strNameOfXlMacro As String

'   with Reference set to Excel Object Library
    
On Error Resume Next
'   Attempt to bind to an open instance of Excel
Set appExcel = GetObject(, "Excel.Application")
    
    If Err.Number <> 0 Then
        '   if Excel not open _
            then clear error
        Err.Clear
        '   and open Excel
        Set appExcel = CreateObject _
                ("Excel.Application")
    End If

'   restore normal error handling
On Error GoTo RangeNamed_ErrorHandling

'   open the workbook
appExcel.Workbooks.Open _
("C:\MyFolderName\MyExcelWorkbookName.xls")

'   set the Excel session to be visible
appExcel.Application.Visible = True
[COLOR=red]
'   run the Excel macro
appExcel.Run ("NameOfMyExcelMacro")
[/color]
'#########
'#  Excel marco runs and finishes
'#  by saving and closes workbook.
'#  Code does not get past here.
'#  error code: 440
'#  desc: method 'Run' of object'_Application failed
'#########

'   do more stuff
MsgBox "Excel is done." & vbCr & _
       "Now doing more stuff."

Exit Sub
RangeNamed_ErrorHandling:
    
MsgBox "Error handling actions and message goes here." & vbCr & _
vbCr & _
"The error description is: " & vbCr & _
Err.Description & vbCr & _
vbCr & _
"The error type number is: " & vbCr & _
Err.Number _

End Sub

This is a repost from thread705-1353330 in which I hadn’t provide a full picture.

Any thoughts about what to try?

Thanks
Tom

 

[bubba100]

Try having the Excel Macro just do the calulations then have Access save and close the workbook.

 

[TKaminski]

How would I have Access 'Know' when the Excel macro is done?

 

[bubba100]

try it and see what happens.

 

[TKaminski]

Thank you.

I got rid of the save and close action in the Excel macro and changed my Access code as follows:

'   run the Excel macro
appExcel.Run ("NameOfMyExcelMacro")

'   do more stuff
MsgBox "Excel is done." & vbCr & _
       "Now doing more stuff."

'   save and close the workbook
appExcel.ActiveWorkbook.Close (True)

So the 'boss' application gets to stay 'boss'...

Thanks to the insight. Here is a star.