Export Access Report to an Excel File in VBA. Close Excel File and Open Another

[GeminiG]

Hello I have a perfectly (for my purposes) formatted report I run monthly.
What I need to do is Export this report to an excel file (with all the formatting and grouping intact).
I had a command button to export macro that works fine. I then turned it into VBA code so that I could open another Macro enabled Excel file which copies the data from the regular excel file and formats it how I need it.
If I run each part separately they work fine, but I'd like to combine the two process. The code I came up with below is throwing Object Method errors and crashing.
Can anyone identify what I'm doing wrong or have a better solution?

Private Sub MonthlyReport_Click()
On Error GoTo MonthlyReport_Click_Err


'Set Variables
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True

'Output Access "Monthly Report" to Excel Workbook named Data.xls
    DoCmd.OutputTo acOutputReport, "Monthly Report", "Excel97-Excel2003Workbook(*.xls)", "W:\Database\Reports\Data.xls", True, "", , acExportQualityPrint


'Save and Close Data.xls Excel File, hopefully without being prompted
    xlApp.ThisWorkbook.Save
    xlApp.Workbooks.Close


'Open new "Monthly Report" in Macro Enabled Excel File, Macro runs on Open to format data
    xlApp.Workbooks.Open "W:\Database\Reports\Monthly Report.xlsm", True, False
    Set xlApp = Nothing
    xlApp.Quit    


MonthlyReport_Click_Exit:
    Exit Sub


MonthlyReport_Click_Err:
    MsgBox Error$
    Resume MonthlyReport_Click_Exit


End Sub

 

[xenou]

This won't work because once you set the xlApp object to nothing you can't use its methods anymore:
Set xlApp = Nothing
xlApp.Quit

The order can be reversed - first quit, then set the xlApp to nothing.