Excel automation: get sheet and range names

[kevininstructor]

The following code shows how to get a list of sheet names and range names within an Excel file using early binding. This can be done easier in OleDb but OleDb sorts the object names while the automation method returns object names in their ordinal position in the workbook.

Create a new windows project and add a reference to Microsoft.Offce.Interop.Excel, set Option Strict On. Add a new class with the following code

Class file

Option Strict On

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices

''' <summary>
''' Used to obtain both worksheet and named range names from a valid Excel file.
''' </summary>
''' <remarks></remarks>
Public Class ExcelInfo
    Public Property LastException As Exception

    Private Extensions As String() = {".xls", ".xlsx"}
    Private mFileName As String
    ''' <summary>
    ''' Valid/existing Excel file name to work with.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property FileName() As String
        Get
            Return mFileName
        End Get
        Set(ByVal value As String)
            If Not Extensions.Contains(IO.Path.GetExtension(value.ToLower)) Then
                Throw New Exception("Invalid file name")
            End If
            mFileName = value
        End Set
    End Property
    Private mNameRanges As New List(Of String)
    ''' <summary>
    ''' List of named ranges in current file
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property NameRanges() As List(Of String)
        Get
            Return mNameRanges
        End Get
    End Property
    Private mSheets As New List(Of String)
    ''' <summary>
    ''' List of work sheets in current file
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property Sheets() As List(Of String)
        Get
            Return mSheets
        End Get
    End Property
    Public Sub New()
    End Sub
    ''' <summary>
    ''' File to get information from
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <remarks>
    ''' The caller is responsible to ensure the file exists.
    ''' </remarks>
    Public Sub New(ByVal FileName As String)
        Me.FileName = FileName
    End Sub
    ''' <summary>
    ''' Retrieve worksheet and name range names.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetInformation() As Boolean
        Dim Success As Boolean = True

        If Not IO.File.Exists(FileName) Then
            Dim ex As New Exception("Failed to locate '" & FileName & "'")
            _LastException = ex
            Throw ex
        End If

        mSheets.Clear()
        mNameRanges.Clear()

        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlActiveRanges As Excel.Workbook = Nothing
        Dim xlNames As Excel.Names = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing

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

            xlActiveRanges = xlApp.ActiveWorkbook
            xlNames = xlActiveRanges.Names

            For x As Integer = 1 To xlNames.Count
                Dim xlName As Excel.Name = xlNames.Item(x)
                mNameRanges.Add(xlName.Name)
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlName)
                xlName = Nothing
            Next

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                Dim Sheet1 As Excel.Worksheet = CType(xlWorkSheets(x), Excel.Worksheet)
                mSheets.Add(Sheet1.Name)
                Runtime.InteropServices.Marshal.FinalReleaseComObject(Sheet1)
                Sheet1 = Nothing
            Next

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

        Catch ex As Exception
            _LastException = ex
            Success = False
        Finally

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

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

            If Not xlActiveRanges Is Nothing Then
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlActiveRanges)
                xlActiveRanges = Nothing
            End If
            If Not xlActiveRanges Is Nothing Then
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlActiveRanges)
                xlActiveRanges = 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
        End Try

        Return Success

    End Function
End Class

In the main form add two ListBox controls, names; ListBoxSheetNames1 and ListBoxRangeNames1. Add the code below

Public Class frmExcelAutomationGetInfo
    Private FileName As String = "Your file name goes here"
    Private Sub ExcelAutomationGetInfo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        AutomationLoad()
    End Sub
    Private Sub AutomationLoad()
        Dim Info As New ExcelInfo With {.FileName = FileName}

        If Info.GetInformation Then
            ListBoxSheetNames1.DataSource = Info.Sheets
            ListBoxRangeNames1.DataSource = Info.NameRanges
        Else
            MessageBox.Show(String.Format("Failed to get back information{0}{1}",Environment.NewLine,Info.LastException.Message)                                     )
        End If
    End Sub
End Class

[walterwjl]

Hi there, I'm struggling with an issue trying to do something similar to what you are doing in this code, so I tried your version and I seem to be getting the same issue.

When stepping through the code, when it gets to this line in the function getInformation xlWorkBook = xlWorkBooks.Open(FileName)

The form frmExcelAutomationGetInfo is displayed on the screen and nothing further happens.

Do you have any idea what might cause this?

[kevininstructor]

Hi there, I'm struggling with an issue trying to do something similar to what you are doing in this code, so I tried your version and I seem to be getting the same issue.

When stepping through the code, when it gets to this line in the function getInformation xlWorkBook = xlWorkBooks.Open(FileName)

The form frmExcelAutomationGetInfo is displayed on the screen and nothing further happens.

Do you have any idea what might cause this?

There are several reasons for your code not working but as you have not posted any code I am at a lose to make a suggestion to change your code to work.
Best to look at the following article under VB.NET Code bank.which shows how to use Excel automation without showing an instance of Excel. Also included is a small OleDb code example but the majority of the code is Excel early binding. Even thou the code is in a VS2010 project the logic will work in VS2008 also.