Acc97/XL97 Automation (97 SR-2)

Topic

New Reply

I am writing some automation code to populate an Excel Template (.XLT) file with data retrieved from an Access query. All goes well until I try to run the process a second time. I am getting hammered with RPC Automation errors. This seems to go away if I manually go into the code module and choose Run>Reset, and make sure there is no hidden instance of Excel hanging around.

What I am looking for:
1. How can I make absolutely sure that there is no instance of Excel left open when I exit my function (Whether or not an error occurs)?
( The Task manager only shows it in the Processes pane, not the Applications pane.)
2. Is there any way to perform the Run>Reset command within my VBA code? (not that I should have to…)
3. Has anyone found anything more useful on the topic of RPC Automation Error than what I find on the MSKB site?

I wait for your ideas with baited breath!!! Thanks so much…

Rich P

Here is some of the pertinent code…

Dim xlAPP As Excel.Application, xlWB As Workbook
Dim ExcelWasNotRunning As Boolean

On Error Resume Next

ExcelWasNotRunning = False

Set xlAPP = GetObject(, “Excel.Application”)
If Err.Number 0 Then
ExcelWasNotRunning = True
Err.Clear
Call DetectExcel
Set xlAPP = CreateObject(“Excel.Application”)
End If

On Error GoTo ErrorHandler
‘ Add a workbook based on the specified template
Set xlWB = xlAPP.Workbooks.Add(strTemplateName) ‘Open a new file based on a template

do my thing here…
then clean up…

xlAPP.Visible = True
xlAPP.UserControl = False
xlWB.Windows(1).Visible = True
xlWB.SaveAs strSaveAsName

‘If we opened Excel then Quit it
‘If ExcelWasNotRunning = True Then
xlAPP.Application.Quit
‘End If
‘
IN_Make_Invoice_Summary_Exit:
rst.Close
Set qdf = Nothing
Set db = Nothing
Set xlWB = Nothing
Set xlAPP = Nothing ‘ Clean up on the way out
Exit Function

ErrorHandler:
MsgBox “An error occurred while trying to build the Invoice Summary.” & vbCrLf _
& “The specific error number and description are: ” & vbCrLf _
& Err.Number & vbCrLf & Err.Description & vbCrLf _
& “Please wait a moment and then try again. If the problem persists, ” _
& “contact your database administrator with this error info.”
GoTo IN_Make_Invoice_Summary_Exit

Reply | Quote

Replies

This doesn’t answer your specific question, but I was wondering if you are only pushing data to Excel, or if you were tweaking Excel specific stuff also (like formulas or formatting)? If you are only pushing data out, I highly recommend using ADO to push the data into an Excel file. It is much faster then automation.

Drew

Reply | Quote

RichP,

What is in your DetectExcel function?

Another thing I just noticed, you are using

xlAPP.Application.Quit

before you release the ponters to the workbook.

You should always release the object pointers in the order you create them in.

Open XL
Open the WBK
do your stuff
Close the WBK
Set wbkVariable = nothing
Close XL (if required)
Set XLVariable = nothing

Reply | Quote

It looks as if your code should close Excel OK untill you get an error in the code. Your error handler IN_Make_Invoice_Summary_Exit: does not include the check to see if it needs to close Excel. Are you getting copies of Excel left open when there has been no error messages?

Peter

Reply | Quote

Thanks to all who have responded. I did get to the bottom of this (these) problem(s). It turned out to be a combination of things. Not only was I handling the variables in the wrong order, but after finding a reference to MSKB article Q178510 and applying its ‘knowledge’ I was able to resolve the problem.

For anyone who is having trouble getting automation code to run, I highly recommend reviewing that article.

Thanks again for the input.

Rich P.

Reply | Quote