[FAQ's: OD] How do I enumerate the sheets in a workbook without using Excel?

[RobDog888]

When performing automation on Excel many times we are faced with trying tofind out if a certain sheet exists before executing code against it. To solve this issue we can use ADO or ADO.NET to connect to the workbook and obtain the workbook schema including the table(s). A table is the equilivalent of an Excel sheet/worksheet.







Using ADO.NET we can get the schema of the workbook and retrieve the sheet names that are in the selected workbook.


VB.NET 2003 And Excel (97-2003) Code Example:

Option Explicit On 
Option Strict On
'Copyright © 2006 by RobDog888 (VB/Office Guru™). All Rights reserved.
'
'              You may not reproduce or publish this 
'              code on any web site, online service, 
'              or distribute as source on any media 
'              without express permission.
 
Public Class Form1
 
    Inherits System.Windows.Forms.Form
 
    "Windows Form Designer generated code"
 
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
 
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles MyBase.Closing
        Application.Exit()
    End Sub
 
    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
        Dim oCnn As New OleDb.OleDbConnection
        Dim schemaTable As DataTable
        Dim dlgOpenFile As New OpenFileDialog
        Dim strFileName As String
        With dlgOpenFile
            .CheckFileExists = True
            .CheckPathExists = True
            .Filter = "Excel Files Only (*.xls)|*.xls"
            .FilterIndex = 0
            .InitialDirectory = Application.StartupPath
            .Multiselect = False
            .Title = "Select an Excel Workbook File"
            If .ShowDialog = DialogResult.OK Then
                strFileName = .FileName
                oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & _
                ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
                oCnn.Open()
                schemaTable = oCnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                New Object() {Nothing, Nothing, Nothing, "TABLE"})
                oCnn.Close()
                ListBox1.Items.Clear()
                For i As Integer = 0 To schemaTable.Rows.Count - 1
                    ListBox1.Items.Add(schemaTable.Rows(i).Item("TABLE_NAME").ToString)
                Next
                Label1.Text = "Sheets in Workbook: " & strFileName.Substring(strFileName.LastIndexOf("\") + 1)
            End If
        End With
    End Sub
 
End Class