Excel.exe*32 not closing in task manager. Running VBA from Access. Running Office 2013

Question

The excel.exe*32 process remains open in the Windows Task Manager even though I am closing it in VBA. I am running the following VBA code from Access. I have looked and tried various solutions to no avail. The only way to close excel.exe is to quit Access. Can someone point out what I am missing.

Public Sub GenerateQualityReportsSub()


On Error GoTo ERR_GenerateQualityReportsSub
Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim dbs As DAO.Database
Dim rstRpt As DAO.Recordset
Dim objMyRange As Object
Dim rstList As DAO.Recordset
Dim FullOutFileName As String
Dim strSQLList As String
Dim strSQLRpt As String
Dim i As Integer
Dim DiscrepancyRecords As Long
Dim NeedToCloseExcel As Boolean
Dim ReportName As String
Dim col As Integer

'Initialize Variables
Set dbs = CurrentDb
RunDate = Now()
FullOutFileName = "DataQualityDiscrepancyReport.xlsx"
i = 0
DiscrepancyRecords = 0
NeedToCloseExcel = False

'Determine the Reports to Generate
strSQLList = "" & _
  "SELECT ReportNum, ReportName, SheetName, QueryName, [Responsible Department] " & _
  "FROM [Data Quality Reports] " & _
  "ORDER BY ReportNum"
Set rstList = dbs.OpenRecordset(strSQLList, dbOpenSnapshot, dbReadOnly)
If rstList.RecordCount = 0 Then
    i = 0
    GoTo Exit_GenerateQualityReportsSub
Else
    'Open Excel
    Set xl = New Excel.Application                      'Open the Excel File
    xl.Visible = True                                 'Make Excel Invisible to User
    'Create the Excel Spreadsheet and Sheets
    Set wbk = xl.Workbooks.Add                          'Add a Wookbook to the Excel File
    wbk.Sheets("Sheet1").Select                             'Select Sheet 1
    wbk.SaveAs FileName:=FullOutFileName                'Save the Excel File
    NeedToCloseExcel = True
End If

'Create One Sheet Per Report
i = 1
While Not rstList.EOF
    DiscrepancyRecords = 0

'Add, if necessary, and Rename the Sheet
    If i <> 1 Then
        Set wks = xl.Worksheets.Add                           'Add a Wooksheet to the Excel File
    End If
    wbk.Sheets("Sheet" & i).Select                            'Select the new Sheet
    wbk.Sheets("Sheet" & i).Name = rstList("SheetName")       'Rename the Sheet
    Set wks = wbk.activesheet


'Obtain and Write Data to the Excel Sheet
    strSQLRpt = "Select * from [" & rstList("QueryName") & "]"
    Set objMyRange = wks.Cells(xl.activesheet.UsedRange.Rows.Count + 1, 1)
    Set rstRpt = dbs.OpenRecordset(strSQLRpt, dbOpenSnapshot, dbReadOnly)
    If rstRpt.RecordCount = 0 Then
        GoTo Exit_GenerateQualityReportsSub
    Else
        rstRpt.MoveLast
        DiscrepancyRecords = rstRpt.RecordCount
        rstRpt.MoveFirst
    End If

'Write the Column Headers to the Sheet
    For col = 0 To rstRpt.Fields.Count - 1
        wks.Cells(1, col + 1) = rstRpt.Fields(col).Name
    Next col

'Write Data to the Excel Sheeet
    Range("A2").Select
    With objMyRange
        rstRpt.MoveFirst
        .CopyFromRecordset rstRpt
    End With

'Format the Sheet Cells
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select

'Save the Excel File
    wbk.Save                                                'Save the Excel File


NextReport:
'Close the Data Results
    rstRpt.Close
    Set rstRpt = Nothing
    rstList.MoveNext
    i = i + 1
Wend
i = i - 1

'Close the Excel File and Application
xl.Visible = True
wbk.Save
wbk.Close savechanges:=True
xl.Quit
Set wks = Nothing
DoEvents
Set wbk = Nothing
DoEvents
Set xl = Nothing
DoEvents
NeedToCloseExcel = False

'Close the Report Record
rstList.Close
Set rstList = Nothing

Exit_GenerateQualityReportsSub:
If NeedToCloseExcel Then
    xl.Visible = True
    wbk.Save
    wbk.Close savechanges:=True
    xl.Quit
    Set wks = Nothing
    DoEvents
    Set wbk = Nothing
    DoEvents
    Set xl = Nothing
    DoEvents
    NeedToCloseExcel = False
End If
Exit Sub


ERR_GenerateQualityReportsSub:
.....

End Sub

Answer 1

What I would recommend:

Don't create a new Excel application, try to re-use an existing one first. Make sure you set the variables you used for manipulating Excel objects to Nothing before you quit the Excel app. In your code, you Quit the app, but still keep references to some of the variables.

'-----------------------------------------------------------------------------
' Return an intance of Excel
' First tries to open an existing instance. If it fails, it will create an instance.
' If that fails too, then we return 'Nothing'
'-----------------------------------------------------------------------------
Public Function GetExcelObject() As Object
    On Error Resume Next
    Dim xlo As Object
    ' Try to get running instance of Excel
    Set xlo = GetObject("Excel.Application")
    If xlo Is Nothing Then
        Set xlo = CreateObject("Excel.Application")
    End If
    Set GetExcelObject = xlo
End Function

then use:

Set xl = GetExcelObject()

When you're finished with your Excel file:

' Clear all variables that were used to contain Excel objects
set objMyRange = nothing
set Range = nothing
set Selection = nothing
' Save and close
wbk.Save
wbk.Close savechanges:=True
Set wks = Nothing
Set wbk = Nothing
xl.Quit
Set xl = Nothing

I think you need to revise your code a bit. Maybe you didn't include all of it here but there are some iffy things:

• you use undeclared variables (Selection, Cell, Range). Maybe you didn't include the code that declares them but make sure you have Option Explicit at the top of all your VBA files to force you to declare all variables.

• You have some GoTo Exit_GenerateQualityReportsSub which generally is a sign that your code needs refactoring. Apart from error managagement in VBA, it's extremely rare that you would need to use GoTo. In this case, you can perfectly use Exit Do to exit you loop.
  These jumps make reasoning about the code harder.

• You also repeated the same cleanup code at the end of your function to take care of the GoTo branching. By duplicating your code, you take the risk of making it less maintainable and you may introduce errors by forgetting to update both blocks when you make a change to one.