process - 宏完成后 Excel.exe 保留在内存中

Question

我正在使用 中的 VBA 自动化访问 2010 使用 创建图表Excel 2010 .我的代码工作正常，但留下一个孤立的 Excel.exe 进程运行。我已将问题追溯到对单独模块中的 Excel VBA 函数的调用。如果我消除了对该函数的调用，Excel 进程将在代码完成时关闭。函数是 Chip Pearson 的 FindAll进行修改以使其在 Access 中运行，例如将范围声明为 Excel.Range并明确引用 Union方法为Excel.Application.Union .我如何需要修改此功能，以免孤立的 Excel 进程运行？

以下代码在主程序中绑定(bind) Excel 应用程序:

Dim oApp As New excel.Application
Dim oBook As excel.Workbook
Dim oSheet As excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

然后，最后:

oBook.Close False
oApp.Quit

Set oSheet = Nothing
Set oBook = Nothing
Set oApp = Nothing

我正在使用这条线调用 FindAll:

      b = FindAll(oSheet.Range("a2", oSheet.Range("a2").End(xlDown)), strWellID).Rows.Count

FindAll功能如下:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modFindAll
' By Chip Pearson, chip@cpearson.com. www.cpearson.com
' 24-October-2007
' This module is described at www.cpearson.com/Excel/FindAll.aspx
' Requires Excel 2000 or later.
'
' This module contains two functions, FindAll and FindAllOnWorksheets that are use
' to find values on a worksheet or multiple worksheets.
'
' FindAll searches a range and returns a range containing the cells in which the
'   searched for text was found. If the string was not found, it returns Nothing.

' FindAllOnWorksheets searches the same range on one or more workshets. It return
'   an array of ranges, each of which is the range on that worksheet in which the
'   value was found. If the value was not found on a worksheet, that worksheet's
'   element in the returned array will be Nothing.
'
' In both functions, the parameters that control the search have the same meaning
' and effect as they do in the Range.Find method.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function FindAll(SearchRange As Excel.Range, _
                FindWhat As Variant, _
                Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False) As Range
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' FindAll
    ' This searches the range specified by SearchRange and returns a Range object
    ' that contains all the cells in which FindWhat was found. The search parameters to
    ' this function have the same meaning and effect as they do with the
    ' Range.Find method. If the value was not found, the function return Nothing.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim FoundCell As Excel.Range
    Dim FirstFound As Excel.Range
    Dim LastCell As Excel.Range
    Dim ResultRange As Excel.Range

    With SearchRange
        Set LastCell = .Cells(.Cells.Count)
    End With

    'On Error Resume Next
    On Error GoTo 0

    Set FoundCell = SearchRange.Find(What:=FindWhat, _
            after:=LastCell, _
            LookIn:=LookIn, _
            LookAt:=LookAt, _
            SearchOrder:=SearchOrder, _
            MatchCase:=MatchCase)

    If Not FoundCell Is Nothing Then

        Set FirstFound = FoundCell
        Set ResultRange = FoundCell
        Set FoundCell = SearchRange.FindNext(after:=FoundCell)

        Do Until False ' Loop forever. We'll "Exit Do" when necessary.
            If (FoundCell Is Nothing) Then
                Exit Do
            End If

            If (FoundCell.Address = FirstFound.Address) Then
                Exit Do
            End If

            Set ResultRange = Excel.Application.Union(ResultRange, FoundCell)
            Set FoundCell = SearchRange.FindNext(after:=FoundCell)
        Loop
    End If

    Set FindAll = ResultRange

    'added below

    Set ResultRange = Nothing
    Set FoundCell = Nothing
    Set FirstFound = Nothing
    Set LastCell = Nothing
    Set SearchRange = Nothing
End Function

Answer 1

Jimmy Pena 和 Siddharth Rout 就如何帮助删除幻像 excel 流程提供了出色的评论。

我只想补充一点，虽然我不一定在所有情况下都推荐它，但如果这是一个持续存在的问题，并且您在排除故障时正在寻找快速“修复”，您可以强制关闭，如本 SO 中所述问题:VBA script to close every instance of Excel except itself

Sub ForceExcelExit()

Dim BruteForce As String

BruteForce = "TASKKILL /F /IM excel.exe"
Shell BruteForce, vbHide

End Sub

测试揭示了该过程的快速结束，如果不是特别优雅的话。

您还可以使用迭代循环以更优雅的方式结束此类进程 - 但仍使用系统进程方法，而不是应用程序/应用程序接口(interface)方法: HOW TO close Excel instance started by mail merge

虽然我不认可这些方法中的任何一种作为由于错误检查不完整或不足、代码封装或一般回调、方法使用、变量实例化或其他编程问题而使实例处于事件状态的一揽子解决方案，但它是在以下情况下的修复需要修复。