Running Excel code from Access 1

[rj51cxa]

I am using the following code to output a report into Excel 2003 in order to achieve the following:

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, leaving Excel running with Trophy Sig Sheet open

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

This works well but leaves me with two workbooks open - Trophy Sig Sheet and ExcelMacros. I have tried various ways to get the ExcelMacros workbook to close but each time I end up with an Access error message.

Can anyone please advise how I can modify either the Access or Excel codes to get the ExcelMacros workbook to close and leave just the Trophy Sig Sheet workbook open.

Thanks a lot
John

 

[kjv1611]

If nothing else, is it possible to use:

oXL.Quit

And then open the workbook you actually want open?

 

[rj51cxa]

Thanks kjv1611, I can do that but I was hoping to find a way of leaving that file open.

John

 

[kjv1611]

I thought you wanted to have one open, but not the other?

 

[rj51cxa]

Sorry, I think I have confused you. I would like to find a way of closing the ExcelMacro workbook but leaving Excel and the Trophy Sig Sheet file open. All I then have to do is to switch windows, rather than go through the procedure of re-opening the file.

Any ideas would be very welcome

John

 

[kjv1611]

Well, that's about all I'm aware of, myself. Maybe someone else will have a better idea.

 

[rj51cxa]

Thanks for your efforts kjv1611, much appreciated. Lets hope someone else can help.

Best Regards
John

 

[PHV]

Have you tried this before releasing oXL ?
oXL.Workbooks("ExcelMacros.xls").Close

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

 

[rj51cxa]

Thanks PHV, that solved the problem completely. You come up trumps every time.

Best Regards
John