Automation Error after closing Excel application 1

[rj51cxa]

I am using the following code to output a report into Excel 2003.

Dim oXL As Object, x
    stDocName = "RptSignatureSheet"

    Set oXL = CreateObject("Excel.Application")
    DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, "c:\zzz\signaturedata.xls", True
    
    With oXL.Application
    .Visible = True
    .Workbooks.Open Filename:=("c:\zzz\ExcelMacros.xls")
    x = .Run("TrophySigSheet")
    End With
    Set oXL = Nothing

The Excel Macro looks like this:

Windows("signaturedata.xls").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    Workbooks.Open Filename:="c:\zzz\Trophy Sig Sheet.xls", UpdateLinks _
        :=3
    ActiveWorkbook.Save
    
Windows("ExcelMacros.xls").Close

Everything works fine except that when access is re-opened, I receive a message box saying "Automation Error".

Can anyone please tell me why I am receiving this message?

Thanks
John

 

[kjv1611]

Do you have a default "start-up" form that possibly has some extra code in it? I would think that you have some start-up code that is causing the error more than closing code.

 

[rj51cxa]

Thanks,kvj1611

What I am trying to do is as follows:
1 Output RptSignatureSheet to Excel
2 Save it as an excel file (signaturedata.xls).
3 Open file (Trophy Sig Sheet.xls) which has links to signaturedata.xls.
4 Update the links and close signaturedata.xls
5 Save Trophy Sig Sheet.xls
6 Return to Access

My current codes work as far as the Excel Macro line

Workbooks.Open Filename:="c:\zzz\Trophy Sig Sheet.xls", UpdateLinks _
        :=3
    ActiveWorkbook.Save

If I disable "Windows("ExcelMacros.xls").Close", I am left with Trophy Sig Sheet.xls open. I can switch windows to Access and get no warning message.

Is there any way I can finish the Excel Macro code so that I can close the Excel file, close Excel and return to Access?

Hope this clarifies the problem. Any ideas would be most welcome.

John

 

[kjv1611]

Would adding:

  oXL.Quit

at the end work?

I use that in Access all the time, but haven't used it in Excel, so I don't know for 100% sure if that will work.

 

[rj51cxa]

Thanks,kjv1611, that did it.

Best Regards
John