Excel.exe*32 未在任务管理器中关闭。从 Access 运行 VBA。运行 Office 2013

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

提问by VBAConst

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.

excel.exe*32 进程在 Windows 任务管理器中保持打开状态，即使我在 VBA 中关闭它。我正在从 Access 运行以下 VBA 代码。我查看并尝试了各种解决方案都无济于事。关闭 excel.exe 的唯一方法是退出 Access。有人可以指出我缺少什么。

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

回答by Renaud Bompuis

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 Nothingbefore you quit the Excel app. In your code, you Quit the app, but still keep references to some of the variables.

不要创建新的 Excel 应用程序，首先尝试重新使用现有的应用程序。Nothing在退出 Excel 应用程序之前，请确保将用于操作 Excel 对象的变量设置为。在您的代码中，您退出了应用程序，但仍保留对某些变量的引用。

'-----------------------------------------------------------------------------
' 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:

完成 Excel 文件后：

' 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 Explicitat the top of all your VBA files to force you to declare all variables.

• You have some GoTo Exit_GenerateQualityReportsSubwhich 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 Doto 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 GoTobranching. 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.

• 您使用未声明的变量（选择、单元格、范围）。也许您没有包含声明它们的代码，但请确保您Option Explicit在所有 VBA 文件的顶部都有以强制您声明所有变量。

• 您有一些GoTo Exit_GenerateQualityReportsSub通常表明您的代码需要重构的标志。除了 VBA 中的错误管理之外，您很少需要使用GoTo. 在这种情况下，您可以完美地使用Exit Do退出循环。
  这些跳转使得对代码的推理变得更加困难。

• 您还在函数末尾重复了相同的清理代码来处理GoTo分支。通过复制您的代码，您会冒着降低其可维护性的风险，并且您可能会因为在对一个块进行更改时忘记更新这两个块而引入错误。