How to return code actively back to Access from Excel

[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.

I trigger the Excel macro with this line of code in Access:

appExcel.Run (strNameOfExcelMacro)

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.

Thanks for the help.
Tom

 

[PHV]

Does access instantiate the workbook object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TKaminski]

I use this code:

'   open a session of Excel
If funIsExcelOpen Then
'   use the open session of Excel
   Set appExcel = GetObject(, "Excel.Application")
Else
   Set appExcel = CreateObject("Excel.Application")
End If

 

[PHV]

And how is the workbook open ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TKaminski]

This is the code:

Dim appExcel As Excel.Application
...

'   combine the path and file name
strWorksheetPath = _
 strWorksheetPath & strWorkbookName

'   open the workbook
appExcel.Workbooks.Open (strWorksheetPath)

'   set the Excel session to be either visible or not
appExcel.Application.Visible = True

…
appExcel.Run (strNameOfExcelMacro)'<- workbook open, macro run, work closes without error.  Assess then errors here

msgbox “Excel is done. Now doing next thing.”	‘<- code does not reach here
...