通过VBA代码关闭的Excel工作簿仍在任务管理器中的进程中运行

问题描述

通过excel VBA，我正在打开工作簿并关闭它，从而退出了Excel应用程序.但是它仍在任务管理器中运行，这阻止了我的插件正常工作.部分代码如下:

  Private Sub btn_Click()Dim oExcel作为对象Dim oBook作为对象昏暗的oSheet作为对象昏暗的整数Dim j作为整数Sheets("Sheet1").SelectFinalrow =单元格(Rows.count，1).End(xlUp).rowLastCol =单元格(1，Columns.count).End(xlToLeft).Column设置oExcel = New Excel.Application设置oBook = oExcel.Workbooks.Open(ADDIN_PATH&"\"&"hello.xls")设置oSheet = oBook.Worksheets(1)sCellName =-选择-"oSheet.Range("A3").Value =名称"如果(ComboBox1.Value ="--Select--"或ComboBox1.Value =")，则MsgBox请映射名称字段"'别的'oSheet.Range("B2").Value = ComboBox1.Value退出子万一'oSheet.Range("B2").Value = ComboBox1.ValueoSheet.Range("B2").Value = ComboBox1.Value如果(ComboBox2.Value =")，则ComboBox2.Value = sCellNameoBook.SaveAs ADDIN_PATH&"\"&"hello.xls"oBook.CloseoExcel退出设置oExcel = NothingMsgBox您的当前设置已保存"SettingForm.Hide结束子 

解决方案

当我在创建和操作excel应用程序/工作簿(通常是从Access中)后遇到幽灵Excel进程时，发现的解决方案始终是相同的-但是经过一番测试之后，您那里的代码并没有为我创建幻影过程.无论哪种方式都可以实施此修复程序，因此这里有两个建议:

1.)在创建/操作新的excel应用程序时，您需要对每个引用进行完全限定-COM通过简单的计数跟踪打开对象的数量-当您打开新的excel进程时，它会增加1，但如果您使用两个开放的标准引用(例如，使用 Cells(Rows.Count，1)... 而不是 oExcel.Workbooks(oBook.Name).Worksheets(oSheet.Name).Cells(oExcel.Workbooks(oBook.Name).WorkSheets(oSheet.Name).Rows.Count，1)... ，它再次跳动，即使它们都已关闭COM对象的内部计数仍为1，Excel保持打开状态(显然，不必在创建对象时每次都键入完整的内容.)因此，我将使用-

 将此工作表调为工作表设置thisSheet = ActiveWorkbook.Worksheets(1)thisSheet.Activate 

即使不是现在引起问题的原因，也可以从另一个应用程序中自动执行Excel时，执行 Sheets("Sheet1")之类的操作，而无需附带工作簿/应用程序引用，则可以100％保证进行鬼进程.

当对所有Rows.Count，Columns.Count等执行此操作时，我认为您也应该为组合框指定工作表.

2.)完成所有操作后，确定哪一行在增加COM对象计数的方法是将 oExcel.Quit 行移到 Set oExcel =之后.新建Excel.Application 行，并继续重新运行它，并逐渐移至 oExcel.Quit 行，直到遇到幻影过程，这才是罪魁祸首./p>

我希望可以帮助解决此问题，我将太多时间浪费在了类似的问题上，希望这是相同的根本原因.

Through excel VBA I'm opening a workbook and closing it, thus quitting the Excel application. But it is still running in the Task manager, which prevents my addin from working properly. Part of the code is as follows:

Private Sub btn_Click()
  Dim oExcel As Object
  Dim oBook As Object
  Dim oSheet As Object

  Dim i  As Integer
  Dim j As Integer
  Sheets("Sheet1").Select

  Finalrow = Cells(Rows.count, 1).End(xlUp).row

  LastCol = Cells(1, Columns.count).End(xlToLeft).Column
  Set oExcel = New Excel.Application
  Set oBook = oExcel.Workbooks.Open(ADDIN_PATH & "\" & "hello.xls")
  Set oSheet = oBook.Worksheets(1)
  sCellName = "--Select--"

  oSheet.Range("A3").Value = "Name"

  If (ComboBox1.Value = "--Select--" Or ComboBox1.Value = "") Then
    MsgBox "Please Map the name field"
    'Else
    'oSheet.Range("B2").Value = ComboBox1.Value
      Exit Sub
  End If

 'oSheet.Range("B2").Value = ComboBox1.Value
  oSheet.Range("B2").Value = ComboBox1.Value

  If (ComboBox2.Value = "") Then ComboBox2.Value = sCellName

   oBook.SaveAs ADDIN_PATH & "\" & "hello.xls"
   oBook.Close
   oExcel.Quit
   Set oExcel = Nothing

   MsgBox "Your current setting has been saved"
   SettingForm.Hide
End Sub

解决方案

When I run into this issue with ghost Excel processes after creating and manipulating an excel application/workbook (usually from Access) the solution I found is always the same - but after doing some testing on my end, the code you have there does not create a ghost process for me. Either way implementing this fix should work, so here are two suggestions:

1.) You need to fully qualify every reference when you are creating/manipulating a new excel application - COM keeps track of the number of open objects by a simple count - when you open a new excel process it ticks up by 1, but if you make an unqualified reference with two open (Say, using Cells(Rows.Count,1)... instead of oExcel.Workbooks(oBook.Name).Worksheets(oSheet.Name).Cells(oExcel.Workbooks(oBook.Name).WorkSheets(oSheet.Name).Rows.Count,1)..., it ticks up again, and now even when they're all closed the internal count of COM objects is still at 1 and Excel stays open. (Obviously you don't have to type out the full thing each time, just when creating the objects.) So I would use -

dim thisSheet as Worksheet
set thisSheet = ActiveWorkbook.Worksheets(1)
thisSheet.Activate

Even if it isn't what's causing the problem now, doing something like Sheets("Sheet1") when automating Excel from another application, without the accompanying workbook/application reference is 100% guarenteed to make a ghost process.

When you do that for all the Rows.Count, Columns.Count, etc, I think you should specify the sheet for the comboboxes too.

2.) When all thats done, the surefire way to figure out which line is incrementing that COM object count is to move the oExcel.Quit line to just after the Set oExcel = New Excel.Application line , and keep re-running it and moving that oExcel.Quit line further and further down until you encounter the ghost process, and that'll be your culprit.

I hope that helps fixed it, I wasted way too many hours a similar problem, hopefully it's the same root cause.

这篇关于通过VBA代码关闭的Excel工作簿仍在任务管理器中的进程中运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！