Excel automation properly using objects primer

The following code shows three different methods to open a Microsoft Excel file using early binding. The code in Button1 will set Sheet3 as the active worksheet then get how many rows are used. If Sheet3 does not exists we bail and release all objects. Button2 reads sheet 3 by setting the worksheet by ordinal position in the current workbook. This is bad for two reasons, if sheet 3 is moved say to where sheet 1 is and sheet 1 where sheet 3 is we are reading the wrong sheet. If there are not three sheets an exception is thrown. When Button2 code completes the objects used remain in memory, worst yet each time you run button 2 another instance of Excel is kept in memory. Button3 is the same as button 2 but we force the garbage collector to release our objects. The garbage collector works for you and you should not manually invoke memory cleanup but that is your choice.

Note: The used row count can be deceiving in that if you have A7 to A10 filled with data the count is 4, not 10.

To use the code in a project, add a reference to Microsoft.Offce.Interop.Excel then in the main form place three buttons with code behind each button using the code below. Compile, run then have Windows Task Manager open while running the code to see that button 1 and button 3 dispose their objects while button 2 does not dispose objects. When the application closes the objects for button 2 are disposed of.

Form code (change the name of the form to your form name)

Code:

---

Option Strict On

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Public Class frmDemoUsedRange

    Private FileName As String = IO.Path.Combine(Application.StartupPath, "YourExcel_FileName_Goes_Here")

    ''' <summary>
    ''' How to open an Excel file, get some info for a WorkSheet then properly
    ''' dispose all objects once the procedure has finished.
    '''
    ''' If the sheet in question does not exists do nothing.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim Proceed As Boolean = False

        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim UsedRange As Excel.Range = Nothing
        Dim RowRange As Excel.Range = Nothing

        Dim SheetNameToUse As String = "Sheet3"

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False

        xlWorkSheets = xlWorkBook.Sheets

        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

            If xlWorkSheet.Name = SheetNameToUse Then
                Proceed = True
                Exit For
            End If

            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        Next

        If Proceed Then
            UsedRange = xlWorkSheet.UsedRange
            RowRange = UsedRange.Rows
            Console.WriteLine("Sheet name: {0} Row range: {1}", xlWorkSheet.Name, RowRange.Count)
        Else
            Console.WriteLine("Sheet not found")
        End If

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        If Not RowRange Is Nothing Then
            Marshal.FinalReleaseComObject(RowRange)
            RowRange = Nothing
        End If
        If Not UsedRange Is Nothing Then
            Marshal.FinalReleaseComObject(UsedRange)
            UsedRange = Nothing
        End If

        If Not xlWorkSheets Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheets)
            xlWorkSheets = Nothing
        End If

        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If

        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If

        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If

        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If

        Console.WriteLine(Now)
    End Sub
    ''' <summary>
    ''' How to open an Excel file, get some info for a WorkSheet but not dispose
    ''' objects properly. Each time this code runs Excel objects are not released.
    '''
    ''' If the sheet in question does not exists the code will throw an exception
    ''' and leave objects in memory until the user reboots or kills them in
    ''' task manager
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim UsedRange As Excel.Range = Nothing
        Dim RowRange As Excel.Range = Nothing

        Dim SheetNameToUse As String = "Sheet3"

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False

        xlWorkSheet = CType(xlWorkBook.Sheets(3), Excel.Worksheet)
        UsedRange = xlWorkSheet.UsedRange
        RowRange = UsedRange.Rows

        Console.WriteLine("Sheet name: {0} Row range: {1}", xlWorkSheet.Name, RowRange.Count)

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        If Not RowRange Is Nothing Then
            Marshal.FinalReleaseComObject(RowRange)
            RowRange = Nothing
        End If
        If Not UsedRange Is Nothing Then
            Marshal.FinalReleaseComObject(UsedRange)
            UsedRange = Nothing
        End If

        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If

        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If

        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If

        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If

        Console.WriteLine(Now)

    End Sub
    ''' <summary>
    ''' Same as Button2 but we invoke the GC.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim UsedRange As Excel.Range = Nothing
        Dim RowRange As Excel.Range = Nothing

        Dim SheetNameToUse As String = "Sheet3"

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False

        xlWorkSheet = CType(xlWorkBook.Sheets(3), Excel.Worksheet)
        UsedRange = xlWorkSheet.UsedRange
        RowRange = UsedRange.Rows
        Console.WriteLine("Sheet name: {0} Row range: {1}", xlWorkSheet.Name, RowRange.Count)

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        If Not RowRange Is Nothing Then
            Marshal.FinalReleaseComObject(RowRange)
            RowRange = Nothing
        End If
        If Not UsedRange Is Nothing Then
            Marshal.FinalReleaseComObject(UsedRange)
            UsedRange = Nothing
        End If

        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If

        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If

        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If

        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

        Console.WriteLine(Now)

    End Sub
    Private Sub frmDemoUsedRange_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Button2.DataBindings.Add("Enabled", Button1, "Enabled")
        Button3.DataBindings.Add("Enabled", Button1, "Enabled")
        Button1.Enabled = IO.File.Exists(FileName)
    End Sub
End Class

---