Read Sheets Name from Excel using OLEDB Connection

In this article I am going to explain how the read the sheets name form excel using OLEDB connection

‘Declarations

Dim connect As System.Data.OleDb.OleDbConnection = Nothing

Dim dtExcelSheets As Data.DataTable = Nothing

Dim index As Integer = 0

Dim excelSheetNames As [String]() = Nothing

'this connection string will change the excel file into the file which we will search

Dim strconnection As String

strconnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strPath + "; Extended Properties=Excel 8.0;" 

Try

   connect = New System.Data.OleDb.OleDbConnection(strconnection)

   connect.Open()

  'get the data table containing the schema

   dtExcelSheets = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

   'check if datatable got the correct schema

    If dtExcelSheets Is Nothing Or dtExcelSheets.Rows.Count = 0 Then

     Return Nothing

    ElseIf dtExcelSheets.Rows.Count <> 0 Then

     excelSheetNames = New [String](dtExcelSheets.Rows.Count - 1) {}

    'add the sheet names to the string array

     For Each row As Data.DataRow In dtExcelSheets.Rows

      excelSheetNames(index) = row("Table_Name").ToString()

      index += 1

     Next

    End If

Catch ex As Exception

        Finally

        connect.Close()          

End Try

Using this code you can retrieve sheet name from the excel.

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  

“Grow more trees to save the Earth”