退出后，Excel 的 VBA 自动化将进程留在内存中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21291083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA automation of Excel leaves a process in memory after Quit
提问by user3224542
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:
我已经看到了很多关于这个问题的建议，我已经尝试了所有的建议，但似乎都没有奏效。VBA 代码位于非 Microsoft 产品（SAP Business Objects，可能是问题所在）中。我创建了一个 Excel 对象：
Set oExcel = CreateObject("Excel.Application")
Set oExcel = CreateObject("Excel.Application")
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.
从特定工作簿中工作表之一的第 1 列加载内容，然后关闭 Excel。每次，它都会在内存中留下一个进程，占用 5+ mb 的内存。
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.
我尝试使 oExcel 对象可见，这样至少我可以在不求助于任务管理器的情况下杀死它，但是当我调用 Quit 时，UI 退出，并且仍然离开进程。
Every time I run the code, it creates a new process. So I tried to reuse any existing Excel processes by calling
每次我运行代码时，它都会创建一个新进程。所以我试图通过调用重用任何现有的 Excel 进程
Set m_oExcel = GetObject(, "Excel.Application")
Set m_oExcel = GetObject(, "Excel.Application")
and only creating it if that call returns nothing,
并且仅在该调用不返回任何内容时才创建它，
That did not proliferate the processes, but the single process grew by 5+ mb each time, so essentially the same problem.
这并没有增加进程，但是单个进程每次增加 5+ mb，所以本质上是相同的问题。
In each case, I close the workbook I opened and set DisplayAlerts to False before quitting:
在每种情况下，我都会关闭我打开的工作簿并将 DisplayAlerts 设置为 False，然后再退出：
m_oBook.Close SaveChanges:=False
m_oExcel.DisplayAlerts = False
m_oExcel.Quit
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.
这段代码已经使用了至少五年，但直到我们迁移到 Windows 7 才出现这个问题。
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:
这是完整的代码，以防万一。请注意，每个建议的所有 Excel 对象都是模块级变量（“m_”前缀），并且我对每个建议使用了“单点”规则。我也尝试使用通用对象（即后期绑定），但这也没有解决问题：
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
回答by Dave Excel
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.
大多数情况下，这是因为 Excel 保持 COM 加载项处于打开状态。尝试使用下面的链接获取有关删除 COM 加载项的帮助。
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.
注意这会从内存中删除加载项，但将其名称保留在可用加载项列表中。它不会从您的计算机中删除加载项。
回答by Q---ten
Adding an answer based on David Zemens comment. Works for me.
添加基于 David Zemens 评论的答案。对我来说有效。
m_oExcel.Quit '<- Still in Task Manager after this line
Set m_oExcel = Nothing '<- Gone after this line
回答by lonestorm
This question has already been answered by Acantudin response to a subsequent post:
https://stackoverflow.com/questions/25147242Fully qualify your references to objects within the Excel workbook you open to avoid creating orphaned processes in the task manager. In this case, the solution is to prefix DelimitedListwith m_oBook, such as
这个问题已由Acantud在随后的帖子中回答：https:
//stackoverflow.com/questions/25147242完全限定您对打开的 Excel 工作簿中的对象的引用，以避免在任务管理器中创建孤立进程。在这种情况下，溶液是前缀DelimitedList与m_oBook如
ThisDocument.Variables("Agent(s)").Value = m_oBook.DelimitedList("Agents")
回答by Uri Goren
Though this isn't supposed to happen, you could send excel a "WindowClose" message in order to force close.
虽然这不应该发生，但您可以向 excel 发送“WindowClose”消息以强制关闭。
You'll be needing these API functions
您将需要这些 API 函数
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Long) As Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
And it should look something like this:
它应该看起来像这样：
// First, get the handle
hWindow = FindWindow(vbNullString, "Excel")
//Get proccess ID
GetWindowThreadProcessId(hWindow, ProcessValueID)
//Kill the process
ProcessValue = OpenProcess(PROCESS_ALL_ACCESS, CLng(0), ProcessValueID)
TerminateProcess(ProcessValue, CLng(0))
CloseHandle ProcessValueID
回答by TomazVDSN
Need to use only:
只需要使用：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Excel.Application.Quit
End Sub
