Best Way to Release Excel Interop com Object

Question

Experts, Please let me know the best way I can do this...

I am working on a VB.Net application that uses the Microsoft.Office.Interop.Excel Object Library to create worksheets within a workbook and Pivot Tables within those worksheets.

My code looks something like this:

Dim ExcelApp As New Microsoft.Office.Interop.Excel.Application
Dim wbk As Microsoft.Office.Interop.Excel.Workbook = Nothing
Dim wksRawData As Microsoft.Office.Interop.Excel.Worksheet = Nothing
Dim wksPvtTbl As Microsoft.Office.Interop.Excel.Worksheet = Nothing
Dim pvtCache As Microsoft.Office.Interop.Excel.PivotCache = Nothing
Dim pvtTables As Microsoft.Office.Interop.Excel.PivotTables = Nothing
Dim pvtTable As Microsoft.Office.Interop.Excel.PivotTable = Nothing
Dim r1 As Microsoft.Office.Interop.Excel.PivotField = Nothing
Dim r2 As Microsoft.Office.Interop.Excel.PivotField = Nothing
Dim df1 As Microsoft.Office.Interop.Excel.PivotField = Nothing

Try

... Create the objects, put in the information

Catch ex As Exception
   MessageBox.Show("There was an error creating the Excel file", "Error Creating File", MessageBoxButtons.OK)
Finally

   ReleaseObject(r1)
   ReleaseObject(r2)
   ReleaseObject(df1)
   ReleaseObject(pvtTable)
   ReleaseObject(pvtTables)
   ReleaseObject(pvtCache)
   ReleaseObject(wksRawData)
   ReleaseObject(wksPvtTbl)
   ReleaseObject(wbk)

   ExcelApp.DisplayAlerts = True
   ExcelApp.Quit()
   ReleaseObject(ExcelApp)

   ExcelApp = Nothing
   wbk = Nothing
   wksRawData = Nothing
   GC.Collect()
End Try

Then I have code that looks as follows for my release objects:

Public Sub ReleaseObject(ByRef Reference As Microsoft.Office.Interop.Excel.Application)
    Dim i As Integer
    If Reference IsNot Nothing Then
        i = System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference)

        While i > 0
            i = System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference)
        End While

        Reference = Nothing
    End If
End Sub

Public Sub ReleaseObject(ByRef Reference As Microsoft.Office.Interop.Excel.Workbook)
    Dim i As Integer
    If Reference IsNot Nothing Then
        i = System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference)

        While i > 0
            i = System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference)
        End While

        Reference = Nothing
    End If
End Sub

... etc

I know there are MANY solutions out there for this kind of problem, but I’m getting lost in all the different ones to know what best fits my current situation… Is this a good way to do this and, if not, what is a more efficient way??

Thanks!!!

Answer 1

Don't search and call release manually. The Interop process could still run loose if the application crashes catastrophically.

I would register the Interop process to the OS via Job Objects.

Here is a solution: https://stackoverflow.com/a/1307180/160146

Answer 2

My answer is not the best but it works: (Kill Excel processes used by your app)

How do I properly clean up Excel interop objects?

Answer 3

I had trouble in the past with excel not closing properly.

Here is what I do:

• Make sure you .Close() and .Dispose() all of the Workbook and then all Application
• Also Marshal.ReleaseComObject(), but only once. I never did the for loop like you do.
• Then GC.Collect() and GC.WaitForPendingFinalizers()
• Usualy set DisplayAlerts and Interactive to false at the beginning. Make sure no popup is running in the background.

I never did pivot table using the interop.

Answer 4

I share VB.net code:

Imports System.Runtime.InteropServices
Namespace Jobs

Public Class Job
    Implements IDisposable

    <DllImport("kernel32.dll", CharSet:=CharSet.Unicode)>', 
    CallingConvention:=CallingConvention.Cdecl)>
    Shared Function CreateJobObject(a As IntPtr, lpName As String) As IntPtr
    End Function

    <DllImport("kernel32.dll")>
    Public Shared Function SetInformationJobObject(hJob As IntPtr, infoType As 
    JobObjectInfoType, lpJobObjectInfo As IntPtr, cbJobObjectInfoLength As UInteger) As Boolean
    End Function

    <DllImport("kernel32.dll", SetLastError:=True)>
    Public Shared Function CloseHandle(Token As IntPtr) As Boolean
    End Function

    <DllImport("kernel32.dll", SetLastError:=True)>
    Public Shared Function AssignProcessToJobObject(job As IntPtr, process As IntPtr) as Boolean
    End Function

    Private m_handle As IntPtr
    Private m_disposed As Boolean = False

    Public Sub New()

        m_handle = CreateJobObject(IntPtr.Zero, Nothing)

        Dim info As JOBOBJECT_BASIC_LIMIT_INFORMATION = New JOBOBJECT_BASIC_LIMIT_INFORMATION()
        info.LimitFlags = &H2000

        Dim extendedInfo = New JOBOBJECT_EXTENDED_LIMIT_INFORMATION()
        extendedInfo.BasicLimitInformation = info

        Dim length As Integer = Marshal.SizeOf(GetType(JOBOBJECT_EXTENDED_LIMIT_INFORMATION))
        Dim extendedInfoPtr As IntPtr = Marshal.AllocHGlobal(length)
        Marshal.StructureToPtr(extendedInfo, extendedInfoPtr, False)

        If (Not SetInformationJobObject(m_handle, JobObjectInfoType.ExtendedLimitInformation, extendedInfoPtr, length)) Then
            Throw New Exception(String.Format("Unable to set information.  Error: {0}", Marshal.GetLastWin32Error()))
        End If
    End Sub

#Region "IDisposableMembers"

    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub

#End Region
    Private Sub Dispose(disposing As Boolean)
        If m_disposed Then
            Return
        End If
        If disposing Then
        End If
        Close()
        m_disposed = True
    End Sub

    Public Sub Close()
        CloseHandle(m_handle)
        m_handle = IntPtr.Zero
    End Sub

    Public Function AddProcess(handle As IntPtr) As Boolean
        Return AssignProcessToJobObject(m_handle, handle)
    End Function

End Class

Public Enum JobObjectInfoType
    AssociateCompletionPortInformation = 7
    BasicLimitInformation = 2
    BasicUIRestrictions = 4
    EndOfJobTimeInformation = 6
    ExtendedLimitInformation = 9
    SecurityLimitInformation = 5
    GroupInformation = 11
End Enum

<StructLayout(LayoutKind.Sequential)>
Public Structure SECURITY_ATTRIBUTES
    Public nLength As Integer
    Public lpSecurityDescriptor As IntPtr
    Public bInheritHandle As Integer
End Structure

<StructLayout(LayoutKind.Sequential)>
Structure JOBOBJECT_BASIC_LIMIT_INFORMATION
    Public PerProcessUserTimeLimit As Int64
    Public PerJobUserTimeLimit As Int64
    Public LimitFlags As Int16
    Public MinimumWorkingSetSize As UInt32
    Public MaximumWorkingSetSize As UInt32
    Public ActiveProcessLimit As Int16
    Public Affinity As Int64
    Public PriorityClass As Int16
    Public SchedulingClass As Int16
End Structure

<StructLayout(LayoutKind.Sequential)>
Structure IO_COUNTERS
    Public ReadOperationCount As UInt64
    Public WriteOperationCount As UInt64
    Public OtherOperationCount As UInt64
    Public ReadTransferCount As UInt64
    Public WriteTransferCount As UInt64
    Public OtherTransferCount As UInt64
End Structure

<StructLayout(LayoutKind.Sequential)>
Structure JOBOBJECT_EXTENDED_LIMIT_INFORMATION
    Public BasicLimitInformation As JOBOBJECT_BASIC_LIMIT_INFORMATION
    Public IoInfo As IO_COUNTERS
    Public ProcessMemoryLimit As UInt32
    Public JobMemoryLimit As UInt32
    Public PeakProcessMemoryUsed As UInt32
    Public PeakJobMemoryUsed As UInt32
End Structure

End Namespace

And, in the class where you plan to implement:

<DllImport("user32.dll", SetLastError:=True)>
Public Shared Function GetWindowThreadProcessId(hWnd As IntPtr, ByRef lpdwProcessId As UInteger) As UInteger
End Function


'Implements
Dim oExcelApp = New Microsoft.Office.Interop.Excel.Application
Dim job As Jobs.Job = New Jobs.Job()
Dim pid As UInteger = 0
GetWindowThreadProcessId(New IntPtr(oExcelApp.Hwnd), pid)
job.AddProcess(Process.GetProcessById(pid).Handle)

oExcelApp.Workbooks.Open(RutaArchivoExcel)

'Code work code here

oExcelApp.Workbooks(1).Close()
oExcelApp.Quit()

'Then Dispose correctly the excel app and windows distroy appropiately the process
job.Dispose()

i test this with Microsoft Excel 2016.

Answer 5

I have insured that all objects are individually declared, used and released by using late binding and carefully declared every object with "ONE DOT". Insured that Excel ranges, sheets, workbooks and the application are all created as objects and closed or quit in the reverse order, being careful that no unanswered dialogs can stop the close or the quit. Used InteropServices.Marshal.FinalReleaseComObject and also put ReleaseComObject in a while loop waiting for count to go to zero in an effort to release every little thing. Always set the object to nothing in the Finally clause of a try/catch block. Done garbage collection and waited for the finalizers. Made sure that I was testing with compiled code and not in the debugger. In other words I have read every expert opinion and tried every expert recommendation. Still have orphaned Excel processes in the task manager that NEVER get cleaned up until the PC is rebooted. The only thing that really works is to KILL THE PROCESS when I am done. I don't kill it until I have done everything else to quit, release and garbage collect.But I cannot afford to have orphaned excel processes hanging around waiting for .NET garbage collection that never gets done.