VB.Net - Excel COM对象未被释放 [英] VB.Net - Excel COM Object not getting released

问题描述

我的Excel进程终止后，只有在我关闭之后，我才会遇到Excel Process保持活动的问题。用户表单

以下是示例代码，显示了我正在做的所有事情以摆脱Excel流程：

  public class frmTEST 
 Private Sub Button1_Click（sender As Object，e As EventArgs）Handles Button1.Click 
 Dim objExcel As xl.Application 
 Dim wbReport As xl.Workbook = Nothing 
 
 objExcel = CreateObject（Excel.Application）
 
尝试
 wbReport = objExcel.Workbooks.Open（D ：\EL\Nicolas\VS Online\Classe A v2\Launcher-v2.2\Resources\Modules\Zoom.xlsm）
 Catch ex As Exception 
 Common。 WriteDebugLog（异常行44）
结束尝试
如果wbReport是Nothing然后
 MsgBox（Erre报告 - 代码745.，vbExclamation）
退出子
结束如果
 
与objExcel 
 .Visible = False 
 .ScreenUpdating = False 
 .Calculation = xl.XlCalculation.xlCalculationManual 
 .DisplayAlerts = False 
 End with 
 
''这里我做我所有的处理，我已经删除更简单的问题
 
带objExcel 
 .Calculation = xl.XlCalculation.xlCalculationAutomatic 
 .ScreenUpdating = True 
 .DisplayAlerts = True 
结束
 
''~~>关闭&清理
 wbReport.Close（SaveChanges：= False）
 objExcel.Quit（）
 
 Me.ReleaseObject（wbReport）
 Me.ReleaseObject（objExcel）
 
 MsgBox（Done）
 End Sub 
 
 Private Sub ReleaseObject（ByVal obj As Object）
尝试
 Dim intRel As Integer = 0 
 Do 
 intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
循环while intRel> 0 
 MsgBox（Final Released obj＃& intRel）
 Catch ex As Exception 
 MsgBox（Error release object& ex.ToString）
 obj = Nothing 
最后
 GC.Collect（）
结束尝试
 End Sub 
结束类
  

更新：根据我收到的评论，我修改了代码
，跟随其他线程，但它仍然没有帮助。我的Excel
进程终止，但仅在关闭用户窗体后才会

解决方案

如果正在使用.Net V4或更高版本，尝试一下。
将所有您的 Button1_Click 代码移动到子程序中，并从 Button1_Click 中调用。这将允许该子例程本地的对象超出范围，从而有资格进行垃圾回收。

然后调用使用 Marshal.AreComObjectsAvailableForCleanup 函数来确定释放COM对象需要多少个垃圾回收周期。

备注

如果托管和本机代码之间存在大量引用，并且使用深度依赖关系图，则可能需要很长时间才能清除所有对象。每次GC运行它将释放一些数量的RCW，这将反过来释放下面的COM对象。那些COM对象然后将释放他们的托管引用，并在下一次GC运行时使更多的对象可用于清理，从而重新启动该过程。

AreComObjectsAvailableForCleanup方法提供应用程序确定GC.Collect和GC.WaitForPendingFinalizer需要执行多少周期才能清理所有内容。

  Private Sub Button1_Click（sender As Object，e As EventArgs）Handles Button1.Click 
 ExcelWork（）
清理（）
 End Sub 
 
 Private Sub ExcelWork（）
 Dim objExcel As xl.Application 
 Dim wbReport As xl.Workbook = Nothing 
 
 objExcel = CreateObject（Excel.Application）
 
尝试
 wbReport = objExcel.Workbooks.Open（D：\EL\Nicolas\VS Online\Classe A v2\Launcher-v2.2\Resources\Modules \Zoom.xlsm）
 Catch ex As Exception 
 Common.WriteDe bugLog（异常行44）
结束尝试
如果wbReport不是，然后
 MsgBox（Erreur d'ouverture du reporting  -  Code 745.，vbExclamation）
 Exit Sub 
 End If 
 
 With objExcel 
 .Visible = False 
 .ScreenUpdating = False 
 .Calculation = xl.XlCalculation.xlCalculationManual 
 .DisplayAlerts = False 
结束
 
'这里我做所有的处理，我已经删除，使问题更简化
 
与objExcel 
。计算= xl.XlCalculation.xlCalculationAutomatic 
 .ScreenUpdating = True 
 .DisplayAlerts = True 
结束
 
''~~>关闭&清理
 wbReport.Close（SaveChanges：= False）
 objExcel.Quit（）
 
 MsgBox（Done）
 End Sub 
 
 Private Sub Cleanup（）
 Do 
 GC.Collect（）
 GC.WaitForPendingFinalizers（）
循环while Marshal.AreComObjectsAvailableForCleanup 
 End Sub 
  

I am facing an issue where Excel Process remains active even after calling ReleaseComObject and GC.Collect method.

My Excel Process terminates but ONLY after I close User Form

Below is sample code which shows what all things I am doing to get rid of Excel Process:

Public Class frmTEST
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim objExcel As xl.Application
        Dim wbReport As xl.Workbook = Nothing

        objExcel = CreateObject("Excel.Application")

        Try
            wbReport = objExcel.Workbooks.Open("D:\EL\Nicolas\VS Online\Classe A v2\Launcher-v2.2\Resources\Modules\Zoom.xlsm")
        Catch ex As Exception
            Common.WriteDebugLog("Exception line 44")
        End Try
        If wbReport Is Nothing Then
            MsgBox("Erreur d'ouverture du reporting - Code 745.", vbExclamation)
            Exit Sub
        End If

        With objExcel
            .Visible = False
            .ScreenUpdating = False
            .Calculation = xl.XlCalculation.xlCalculationManual
            .DisplayAlerts = False
        End With

        '' Here I do all my processing which I have removed to make the question more simplified

        With objExcel
            .Calculation = xl.XlCalculation.xlCalculationAutomatic
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With

        ''~~> Close & Clean Up
        wbReport.Close(SaveChanges:=False)
        objExcel.Quit()

        Me.ReleaseObject(wbReport)
        Me.ReleaseObject(objExcel)

        MsgBox("Done")
    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            Dim intRel As Integer = 0
            Do
                intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            Loop While intRel > 0
            MsgBox("Final Released obj # " & intRel)
        Catch ex As Exception
            MsgBox("Error releasing object" & ex.ToString)
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

UPDATE: Based on comments I received, I made changes in my code following the other thread, but it still doesn't help. My Excel Process terminates but ONLY after I close User Form

解决方案

If you are using .Net V4 or greater, give this a try. Move all your Button1_Click code into a subroutine and call it from Button1_Click. This will allow the objects that are local to that subroutine to go out of scope and thereby be eligible for garbage collection.

Then call a cleanup method that uses the Marshal.AreComObjectsAvailableForCleanup function to determine how many garbage collection cycles are required to free the COM objects.

Remarks

If there are a lot of references between managed and native code with deep dependency graphs it can take a long time for all the objects to clean up. Each time a GC runs it will free up some number of RCWs, which will in turn release the underlying COM objects. Those COM objects will then release their managed references and make more objects available for cleanup the next time a GC runs, which starts the process over again.

The AreComObjectsAvailableForCleanup method provides a way for the application to determine how many cycles of GC.Collect and GC.WaitForPendingFinalizers need to happen in order to clean everything up.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ExcelWork()
    Cleanup()
End Sub

Private Sub ExcelWork()
    Dim objExcel As xl.Application
    Dim wbReport As xl.Workbook = Nothing

    objExcel = CreateObject("Excel.Application")

    Try
        wbReport = objExcel.Workbooks.Open("D:\EL\Nicolas\VS Online\Classe A v2\Launcher-v2.2\Resources\Modules\Zoom.xlsm")
    Catch ex As Exception
        Common.WriteDebugLog("Exception line 44")
    End Try
    If wbReport Is Nothing Then
        MsgBox("Erreur d'ouverture du reporting - Code 745.", vbExclamation)
        Exit Sub
    End If

    With objExcel
        .Visible = False
        .ScreenUpdating = False
        .Calculation = xl.XlCalculation.xlCalculationManual
        .DisplayAlerts = False
    End With

    '' Here I do all my processing which I have removed to make the question more simplified

    With objExcel
        .Calculation = xl.XlCalculation.xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

    ''~~> Close & Clean Up
    wbReport.Close(SaveChanges:=False)
    objExcel.Quit()

    MsgBox("Done")
End Sub

Private Sub Cleanup()
    Do
        GC.Collect()
        GC.WaitForPendingFinalizers()
    Loop While Marshal.AreComObjectsAvailableForCleanup
End Sub

这篇关于VB.Net - Excel COM对象未被释放的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！