My Blog

Retrieve worksheet names from excel file

In continuation from here

We created a file earlier here.

Now we will try to retrieve the sheet names from that file. The code is pretty straight forward. The below code can also be converted to a function to return an array of sheet names

Imports System.Data.OleDb

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim olecon As OleDbConnection = New OleDbConnection
        Dim olecmd As OleDbCommand = New OleDbCommand
        Dim FilePath As String = "C:\Temp\"
        Dim FileName As String = "EmployeeDatabase.xlsx"
        Dim dt As New DataTable
        Dim i As Integer = 0

        '~~> Construct your connection string
        Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                   "Data Source=" & FilePath & FileName & ";" &
                                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

        olecon.ConnectionString = connstring
        olecon.Open()

        olecmd.Connection = olecon

        '~~> Get the Schema Table
        dt = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

        '~~> Loop through it to get the sheet names
        For Each row As DataRow In dt.Rows
            If row("TABLE_NAME").ToString.Contains("$") Then MessageBox.Show(row("TABLE_NAME").ToString)
        Next

        '~~> Close the connection
        olecon.Close()
    End Sub
End Class