How to discover worksheet names in Excel via ODBC

  • Thread starter Thread starter Darwin S.
  • Start date Start date
D

Darwin S.

I am writing an application that will allow users to
merge data from an Excel worksheet into an existing
database. I don't know before hand what the name of the
xls file will be nor what how many worksheets there will
be nor what the name of the worksheets will be so I am
having difficulty building the SelectCommand sql
statement without knowing the worksheet names. I have
not been able to determine how to get the OdbcDataAdapter
to tell me what worksheet names are in the worksheet so
that I can build the select command. Maybe there is an
easier way. I would appreciate any help.

TIA,
Darwin
 
This is code posted originally by Paul Clement---I think it addresses your
problem. The Post Title is Excel Worksheet Name in ADO on 9/22.



Use ADO.NET:

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"})

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)
 
¤ I am writing an application that will allow users to
¤ merge data from an Excel worksheet into an existing
¤ database. I don't know before hand what the name of the
¤ xls file will be nor what how many worksheets there will
¤ be nor what the name of the worksheets will be so I am
¤ having difficulty building the SelectCommand sql
¤ statement without knowing the worksheet names. I have
¤ not been able to determine how to get the OdbcDataAdapter
¤ to tell me what worksheet names are in the worksheet so
¤ that I can build the select command. Maybe there is an
¤ easier way. I would appreciate any help.
¤

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"})

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)
 
Back
Top