Open Excel via Early Binding (VS2010 project included)

Seeing many post (on this and other forums) on working with Excel via early binding I thought it might be a good idea to provide a simple no frills sample project for the basics. I was not going to post this now but need to address a question today on early binding.

All code within the attached project has been tested to function as intended and never leave an instance of Excel in memory once any code has completed. Note there is an image in the lower right hand corner of the form. If the image is square green Excel is not in memory from the code provided while a image of Excel indicates Excel is currently in memory. For more information about properly disposing of objects used to access Excel see the link below for a good explanation on how to create and dispose of automation objects.

Resource for working with early automation and creating/disposal of objects http://siddharthrout.wordpress.com/vb-net-and-excel/

A small peek at code

From OpenWorkSheets.vb

Code:

---

''' <summary>
''' Read three cells into a StringBuilder. Lesson here is how
''' we work with cell references.
''' </summary>
''' <param name="FileName"></param>
''' <param name="SheetName"></param>
''' <remarks></remarks>
Public Sub OpenExcel(ByVal FileName As String, ByVal SheetName As String)

    If IO.File.Exists(FileName) Then

        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 xlCells As Excel.Range = Nothing

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

        xlApp.Visible = False

        xlWorkSheets = xlWorkBook.Sheets


        '
        ' For/Next finds our sheet
        '
        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

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

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

        Next
        If Proceed Then
            Dim sb As New System.Text.StringBuilder

            Dim Cells As String() = {"B2", "B3", "B4"}
            For Each cell In Cells
                xlCells = xlWorkSheet.Range(cell)
                sb.AppendLine(String.Format("{0} = '{1}'", cell, xlCells.Value))
                ReleaseComObject(xlCells)
            Next
            MessageBox.Show(sb.ToString)
        Else
            MessageBox.Show(SheetName & " not found.")
        End If


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

        ReleaseComObject(xlCells)
        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)
    Else
        MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
    End If
End Sub

---

From UtilityRoutines.vb

Code:

---

Public Sub ReleaseComObject(ByVal sender As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(sender)
        sender = Nothing
    Catch ex As Exception
        sender = Nothing
    End Try
End Sub

---

Attachment 90477Attachment 90479