退出后，Excel的VBA自动化在内存中留下一个进程 [英] VBA automation of Excel leaves a process in memory after Quit

问题描述

我已经看到了很多关于这个问题的建议，我已经尝试了所有这些，但没有一个似乎没有工作。 VBA代码位于非Microsoft产品（SAP Business Objects，可能是问题）。我创建一个Excel对象：

I have seen a lot of suggestions for this problem, and I have tried them all, but none seem to work. The VBA code is in a non-Microsoft product (SAP Business Objects, which might be the problem). I create an Excel object:

设置oExcel = CreateObject（Excel.Application）

从特定工作簿中的一个工作表的第1列加载内容，然后关闭Excel。每次都会有一个内存中的进程，占用了超过5 MB的内存。

Load the contents from column 1 of one of the WorkSheets in a particular workbook, then close Excel. Each time, it leaves a process in memory, taking up 5+ mb of memory.

我试图使oExcel对象可见，至少我可以杀死它使用任务管理器，但是当我打电话给Quit时，UI退出，并且仍然保留该过程。

I tried making the oExcel object visible, so that at least I could kill it without resorting to the Task Manager, but when I call Quit, the UI quits, and still leaves the process.

每次运行代码时，都会创建一个新的进程。所以我试图通过调用

Every time I run the code, it creates a new process. So I tried to reuse any existing Excel processes by calling

重新使用任何现有的Excel进程设置m_oExcel = GetObject（，Excel.Application）

，只有当该调用不返回时才创建它，

and only creating it if that call returns nothing,

没有增加进程，单个进程每次增长5+ mb，所以本质上是同样的问题。

That did not proliferate the processes, but the single process grew by 5+ mb each time, so essentially the same problem.

在每种情况下，我关闭我打开的工作簿，并在退出之前将DisplayAlerts设置为False： / p>

In each case, I close the workbook I opened and set DisplayAlerts to False before quitting:

m_oBook.Close SaveChanges:=False
m_oExcel.DisplayAlerts = False
m_oExcel.Quit

这一段代码已经使用了至少五年，但这个问题在我们搬到Windows 7。

This bit of code has been in use for at least five years, but this problem did not crop up until we moved to Windows 7.

这是完整的代码，以防它有帮助。注意所有的Excel对象都是模块级变量（m_前缀）每个建议，我已经使用一点规则每一个建议。我也尝试使用通用对象（即后期绑定），但是还没有解决问题：

Here is the full code in case it helps. Note all the Excel objects are module level variables ("m_" prefix) per one suggestion, and I have used the "one-dot" rule per another suggestion. I also tried using generic objects (i.e. late bound) but that did not resolve the problem either:

Private Function GetVariablesFromXLS(ByVal sFile As String) As Boolean
    On Error GoTo SubError

    If Dir(sFile) = "" Then
        MsgBox "File '" & sFile & "' does not exist.  " & _
               "The Agent and Account lists have not been updated."
    Else
        Set m_oExcel = CreateObject("Excel.Application")
        Set m_oBooks = m_oExcel.Workbooks
        Set m_oBook = m_oBooks.Open(sFile)

        ThisDocument.Variables("Agent(s)").Value = DelimitedList("Agents")
        ThisDocument.Variables("Account(s)").Value = DelimitedList("Accounts")
    End If

    GetVariablesFromXLS = True

SubExit:

    On Error GoTo ResumeNext
    m_oBook.Close SaveChanges:=False
    Set m_oBook = Nothing
    Set m_oBooks = Nothing

    m_oExcel.DisplayAlerts = False
    m_oExcel.Quit

    Set m_oExcel = Nothing

    Exit Function

SubError:
    MsgBox Err.Description
    GetVariablesFromXLS = False
    Resume SubExit

ResumeNext:
    MsgBox Err.Description
    GetVariablesFromXLS = False
    Resume Next

End Function

推荐答案

大多数时候，这是因为Excel保持一个COM加载项打开。尝试使用下面的链接删除COM加载项的帮助。

Most times this happens because Excel is keeping a COM Add-in open. Try using the link below for help on removing the COM Add-in.

添加或删除加载项

我在笔记中找到特别的安慰：

I find particular comfort in the note:

注意这将从内存中删除加载项，但将其名称保留在列表中的可用加载项。它不会从您的计算机中删除加载项。

Note This removes the add-in from memory but keeps its name in the list of available add-ins. It does not delete the add-in from your computer.

这篇关于退出后，Excel的VBA自动化在内存中留下一个进程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！