get Excel table names

[Nikolay Petrov]

I have to read data from MS Excel files and I use OLEDB reader for
that.
The problem I encounter is that I don't preliminary know the names of
the sheets in the Excel files, so I can include them in my connection
string.

Any way to do that?

TIA

 

[Paul Clement]

¤ I have to read data from MS Excel files and I use OLEDB reader for
¤ that.
¤ The problem I encounter is that I don't preliminary know the names of
¤ the sheets in the Excel files, so I can include them in my connection
¤ string.
¤

You can use GetOleDbSchemaTable:

Public Function ListExcelTablesNET() As Boolean

Dim ExcelConnection As System.Data.OleDb.OleDbConnection
Dim ExcelTables As DataTable

Try

ExcelConnection = New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book10.xls;Extended Properties=Excel 8.0;")

ExcelConnection.Open()

ExcelTables =
ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing,
Nothing, Nothing, "TABLE"})

Dim RowCount As Int32
For RowCount = 0 To ExcelTables.Rows.Count - 1
Console.WriteLine(ExcelTables.Rows(RowCount)!TABLE_NAME.ToString)
Next RowCount

frmMain.DataGrid1.DataSource = ExcelTables

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ExcelConnection.Close()

End Try

End Function


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)