代码清理后，Excel进程卡在任务管理器中 [英] Excel process stuck in task manager after code clean-up

问题描述

我有一个问题，Access打开一个Excel文件，从它读取并关闭它

...但Excel进程不会离开任务管理器。

我发现完全相同的问题这里，但没有解决方案适用于我，所以我在这里发布我的情况（见下文） p>

如果我点击VB编辑器中的重置按钮，它将消失（或者如果我更改代码中的任何内容，这也会导致项目重置，并摆脱不必要的任何关于代码错误的想法（在Access中运行）？

em> code

我有以下类，名为 clsTest ：

 选项比较数据库
选项显式
 
私有xlFolder As String 
私有xlFile As String 
私人xlSheet As String 
私人colShortURL As String 
私有oXL作为Excel.Application 
私有oWB作为Excel.Workbook 
私有oWS作为Excel.Wor ksheet 
私人iLastRow As Long 
 
 Private Sub Class_Initialize（）
 Debug.PrintFrom class：在类中执行初始化 - 构造函数
 xlFolder =E： \COMH\Excel
 xlFile =记录v8z.xlsm
 xlSheet =comh
设置oXL =新建Excel.Application 
设置oWB = oXL.Workbooks。打开（文件名：=（xlFolder& \& xlFile），ReadOnly：= True）
设置oWS = oWB.Sheets（xlSheet）
 iLastRow = oWS.Range（A& Rows.Count）.End（xlUp）.row 
 End Sub 
 
公共属性获取ShortURL（）As String 
 ShortURL =Hello World& iLastRow 
结束属性
 
私有子类_Terminate（）
 Debug.Print从类：通过清理内部类 - 析构函数
 oWB.Close SaveChanges ：= False 
设置oWS = Nothing 
设置oWB =没有
 oXL.Quit 
设置oXL =没有
结束Sub 
  

我有以下模块可以使用上面的类：

 选项比较数据库
选项显式
 
私有子TestClass（）
 Dim newExcel As clsTest 
尝试：
错误GoTo Catch 
设置newExcel =新的clsTest 
 Debug.Print类实例化，所有好
 Debug.PrintShortURL =& newExcel.ShortURL 
 GoTo终于
 Catch：
 Debug.Print处理错误
 Debug.Print Err.Description&  - & Err.Number 
最后：
 Debug.Print做最后的东西
设置newExcel = Nothing 
 End Sub 
  

当我运行上面的代码，一切都正常，我得到了我想要的结果：

 从类：在类中执行初始化 - 构造函数
类实例化，所有好
 ShortURL = Hello World 2603 
做最后的东西
从类：通过清理内部类 - 析构函数
  

没有错误，但Excel的进程仍然存在在任务管理器进程选项卡。

解决方案

好的故障排除！

从

 更改有问题的行iLastRow = oWS.Range（A& Rows.Count）.End（xlUp）。行
  

（您可能需要进一步参考应用程序级别的范围，我不记得了，现在不能测试）

  iLastRow = oWS.Range（A& oWS.Rows.Count）.End（xlUp）.row 
  

可能的问题是你不请参阅此处

I have a problem with Access opening an Excel file, reading from it and closing it

... but the Excel process does not go away from Task Manager.

I have found the exact same problem here but no solution that worked for me so I am posting my situation here (see below).

If I click the Reset button in the VB Editor, it goes away (or if I change anything in the code, which also causes the project to reset and gets rid of the unwanted Excel process).

Any thoughts on what is wrong with the code (run in Access)?

code

I have the following class, called clsTest:

Option Compare Database
Option Explicit

Private xlFolder As String
Private xlFile As String
Private xlSheet As String
Private colShortURL As String
Private oXL As Excel.Application
Private oWB As Excel.Workbook
Private oWS As Excel.Worksheet
Private iLastRow As Long

Private Sub Class_Initialize()
    Debug.Print "From class: Going through initialization inside class - constructor"
    xlFolder = "E:\COMH\Excel"
    xlFile = "Records v8z.xlsm"
    xlSheet = "comh"
    Set oXL = New Excel.Application
    Set oWB = oXL.Workbooks.Open(Filename:=(xlFolder & "\" & xlFile), ReadOnly:=True)
    Set oWS = oWB.Sheets(xlSheet)
    iLastRow = oWS.Range("A" & Rows.Count).End(xlUp).row
End Sub

Public Property Get ShortURL() As String
    ShortURL = "Hello World " & iLastRow
End Property

Private Sub Class_Terminate()
    Debug.Print "From class: Going through the clean-up inside class - destructor"
    oWB.Close SaveChanges:=False
    Set oWS = Nothing
    Set oWB = Nothing
    oXL.Quit
    Set oXL = Nothing
End Sub

And I have the following Module to use above class:

Option Compare Database
Option Explicit

Private Sub TestClass()
    Dim newExcel As clsTest
Try:
    On Error GoTo Catch
    Set newExcel = New clsTest
    Debug.Print "Class instantiated, all good"
    Debug.Print "ShortURL=" & newExcel.ShortURL
    GoTo Finally
Catch:
    Debug.Print "dealing with the error"
    Debug.Print Err.Description & " - " & Err.Number
Finally:
    Debug.Print "doing the finally stuff"
    Set newExcel = Nothing
End Sub

When I run the code above, everything works fine, I got the result I want:

From class: Going through initialization inside class - constructor
Class instantiated, all good
ShortURL=Hello World 2603
doing the finally stuff
From class: Going through the clean-up inside class - destructor

There are no errors but a process for Excel still persists in Task Manager Processes tab.

解决方案

nice troubleshooting!

try changing the problematic line from

 iLastRow = oWS.Range("A" & Rows.Count).End(xlUp).row

to this (you may need to go further and reference the range at the Application level, I can't remember this and can't test now)

iLastRow = oWS.Range("A" & oWS.Rows.Count).End(xlUp).row

The likely issue is that you do not have a fully qualified reference, see here

这篇关于代码清理后，Excel进程卡在任务管理器中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！