Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Acc97/XL97 Automation (97 SR-2)

    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)?
    ( <img src=/S/confused.gif border=0 alt=confused width=15 height=20> 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
    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
    'End If
    Set qdf = Nothing
    Set db = Nothing
    Set xlWB = Nothing
    Set xlAPP = Nothing ' Clean up on the way out
    Exit Function

    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

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Dallas, Texas, USA
    Thanked 0 Times in 0 Posts

    Re: Acc97/XL97 Automation (97 SR-2)

    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.


  3. #3
    5 Star Lounger
    Join Date
    Jul 2002
    Toronto, Ontario, Canada
    Thanked 0 Times in 0 Posts

    Re: Acc97/XL97 Automation (97 SR-2)


    What is in your DetectExcel function?

    Another thing I just noticed, you are using


    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
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Maidstone, Kent, England
    Thanked 0 Times in 0 Posts

    Re: Acc97/XL97 Automation (97 SR-2)

    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?


  5. #5
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Re: Acc97/XL97 Automation (97 SR-2)

    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.

Posting Permissions

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