Find list of all spreadsheet names of any excel file in vb.net

 
This example shows that how to extract all spread sheet names of any excel file in vb.net.

You can find all spreadsheet or worksheet names in a string array. Before using this code sample add reference of ‘Microsoft ADO Ext 6.0 for DLL and Security’ (you can find in the com references) and ‘adodb’.

Public Function GetExcelSheetfromExcelFile(ByVal strFileName As String) As String()
        Dim strTables As String()
        Dim oCatlog As New ADOX.Catalog()
        Dim oTable As New ADOX.Table()
        Dim oConn As New ADODB.Connection()
        If strFileName.Contains(".xlsx") Then
            oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " & strFileName & "; Extended Properties = ""Excel 12.0;HDR=Yes;IMEX=1"";", "", "", 0)
        Else
            oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " & strFileName & "; Extended Properties = ""Excel 8.0;HDR=Yes;IMEX=1"";", "", "", 0)
        End If
        oCatlog.ActiveConnection = oConn
        If oCatlog.Tables.Count > 0 Then
            Dim item As Integer = 0
            For Each tab As ADOX.Table In oCatlog.Tables
                If tab.Type = "TABLE" Then
                    If Not tab.Name.Contains("#") Then
                        ReDim Preserve strTables(item)
                        strTables(item) = tab.Name
                        item = item + 1
                    End If
                End If
            Next
        End If
        If oConn.State = ConnectionState.Open Then
            oConn.Close()
        End If
        oCatlog.ActiveConnection = Nothing
        oCatlog = Nothing
        oTable = Nothing
        Return strTables
    End Function

you can use this function like :

 Dim strSpreadsheets() As String = GetExcelSheetfromExcelFile("E:\AuthorCode\CodeAuthor Articels.xlsx")