Disposing of Excel after using it in VBA

Question

I have a routine that pastes any recordset into an existing workbook from an MS Access database. The code works fine the first time, but I can never run it twice because it leaves one instance of Excel running in the Task Manager. Of course, this causes an error when I refer to Excel objects in my code the 2nd, 3rd, etc. time, because the objects are ambiguous.

For the sake of missing anything here is the entire code:

'I call the routine like so: 



 Private Sub cmdGenerateRpt
    Dim strPath As String
        strPath = "C:\Test\MyReport.xlsx"

        Call PushToExistingExcel("MAIN SHEET", strPath)

    End sub


    Public Sub PushToExistingExcel(strSheetToPlaceData As String, strPathToWorkbook As String)
    'Puts a recordset into a specific cell of an Excel workbook
    Dim xlApp As Object
    Dim wb As Object
    Dim xlSheet As Object
    Dim rs As DAO.Recordset
    Dim rsTotals As DAO.Recordset
    Dim x As Integer
    Dim fld As Variant
    Dim intRecords As Integer
    Dim intTotals As Integer

    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open(strPathToWorkbook)


    Set xlSheet = wb.Sheets(strSheetToPlaceData) 'or you can manually type the sheet name in place of strSheetToPlaceData

    Set rs = CurrentDb.OpenRecordset("Select * from qryRPT")
    Set rsTotals = CurrentDb.OpenRecordset("Select * from qryTOTALS")


    intRecords = rs.RecordCount
    intTotals = intRecords + 3

    xlSheet.Select
    xlSheet.Range("A3:AH3").Select
    xlSheet.Range(Selection, Selection.End(xlDown)).Select

    'PLACE
    xlSheet.Range("A3").CopyFromRecordset rs
    xlSheet.Range("L" & intRecords + 3).CopyFromRecordset rsTotals

    Cells.EntireColumn.AutoFit
    xlSheet.Range("A1").Select

        Range("A" & intTotals & ":AH" & intTotals).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        With Selection.Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 11
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0

        End With
        Selection.NumberFormat = "$#,##0.00"
        Range("A" & intTotals).Value = "TOTALS"

    wb.Save
    MsgBox "Done"

    xlApp.Visible = True

    'If I use xlApp.quit it quits, but still leaves it running in task manager

    Set wb = Nothing
    Set xlSheet = Nothing
    Set rs = Nothing
    Set rsTotals = Nothing
    Set xlApp = Nothing

    End Sub

At the end of the day, I want for the finished Workbook to open up presenting itself to the user. There is no reason to just say that the report is done - 'go look for it'.
But I can't seem to figure out how to get rid of the instance of Excel left over from VBA.

Answer 1

In order to clean up successfully, you need to

1. destroy all objects that refer to objects in the Excel App
2. close all workbooks
3. Quit the app

Also, to avoid any mistakes and to create cleaner code you should

1. avoid use of the implicit ActiveSheet. The unqualified references to Cells. ..., Range( ..., Selection. ... may be leaving references to the Excel app hanging. Use variables for all references

2. avoid Select, Selection etc

See this answer for help on avoiding these

---

Cleanup code should be

Set xlSheet = Nothing
For Each wb In xlApp.Workbooks
    wb.Close False
Next
xlApp.Quit
Set xlApp = Nothing

Answer 2

This one is closer to 'air tight.' In addition to avoiding using '.select' or '.selection any stray reference like cells.EntireColumn.AutoFit was a Gotcha for me.

Note how I tried to stick to 3 variables for Excel - xlApp, wb and xlSheet Any reference I used needed all three of these tightly integrating the full address. I also used 'Late Binding.'

Then I isolated the presentation of the workbook in another routine.

Use this as an example for pasting a complex query into an existing workbook at a specified location and presenting the report. It works nicely!

Public Sub PushToExistingExcel(strSheetToPlaceData As String, strPathToWorkbook As String)

'Puts a recordset into a specific cell of an Excel workbook
Dim xlApp As Object
Dim wb As Object
Dim xlSheet As Object


Dim rs As DAO.Recordset
Dim rsTotals As DAO.Recordset
Dim x As Integer
Dim fld As Variant

Dim intRecords As Integer
Dim intTotals As Integer

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(strPathToWorkbook)

Set xlSheet = wb.Sheets(strSheetToPlaceData) 'or you can manually type the sheet name in place of strSheetToPlaceData

Set rs = CurrentDb.OpenRecordset("Select * from qryRPT")
Set rsTotals = CurrentDb.OpenRecordset("Select * from qryTOTALS")


intRecords = rs.RecordCount
intTotals = intRecords + 3

xlSheet.Rows("3:" & xlSheet.Rows.Count).ClearContents

'PLACE

With xlSheet
.Range("A3").CopyFromRecordset rs
.Range("L" & intRecords + 3).CopyFromRecordset rsTotals
.Cells.EntireColumn.AutoFit
End With


With xlSheet.Range("A" & intTotals & ":AH" & intTotals).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

With xlSheet.Range("A" & intTotals & ":AH" & intTotals).Font
    .Name = "Calibri"
    .FontStyle = "Bold"
    .Size = 11
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0

End With

xlSheet.Range("A3:AH" & intTotals).NumberFormat = "$#,##0.00"
xlSheet.Range("A" & intTotals).Value = "TOTALS"

wb.Save

'cleanup
Set xlSheet = Nothing
For Each wb In xlApp.Workbooks
    wb.Close False
Next

Set rs = Nothing
Set rsTotals = Nothing

xlApp.Quit
Set xlApp = Nothing

MsgBox "Report Complete"
PresentExcel (strPathToWorkbook)

End Sub

Public Sub PresentExcel(strPath As String)
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")

    xlApp.Visible = True

    xlApp.Workbooks.Open strPath
    Debug.Print xlApp.Version
    Set xlApp = Nothing

End Sub