Sheet names in Excel

  • Thread starter Thread starter Tommy Malone
  • Start date Start date
T

Tommy Malone

I appreciate everyone's help with my connection string for Excel. Here's a
follow-up or two.

How do you get the sheet names from Excel if you don't know them in advance
for your SELECT statement?

Also, is there a connection string that specifies no header row? HDR=NO
causes a "No installable ISAM" error.

Thanks.
 
I think it's no by default if I remember correctly. You need to specify Yes
if you want it included. I know you can get at the sheet names by looping
through the Worksheets collections via Com Interop WorkSheets[Index].Name.
I think you can similarly use just dataTable =
connectionName.GetOleDbSchemaTable() and extract the table names from that.
I'm running off of my memory here but I'm pretty sure that will work. You
just need to call Connection.Open first
http://www.knowdotnet.com/articles/datasetmerge.html. Let me verify it but
I'm pretty sure that will work

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
 
¤ I appreciate everyone's help with my connection string for Excel. Here's a
¤ follow-up or two.
¤
¤ How do you get the sheet names from Excel if you don't know them in advance
¤ for your SELECT statement?
¤
¤ Also, is there a connection string that specifies no header row? HDR=NO
¤ causes a "No installable ISAM" error.
¤

The installable ISAM error was addressed in a previous post. Please review the syntax again if
you're still having problems.

You can use GetOleDbSchemaTable to retrieve the Excel Worksheet names:

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book10.xls;Extended Properties=Excel 8.0;"

DatabaseConnection.Open()

SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, Nothing})

Dim RowCount As Int32

For RowCount = 0 To SchemaTable.Rows.Count - 1
Console.WriteLine(SchemaTable.Rows(RowCount)!TABLE_NAME.ToString)
Next RowCount

DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\BillTestBook.xls;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1""")

Dim dt As New DataTable

cn.Open()

dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing,
Nothing, Nothing, "Table"})

'dt.Rows(Index)(2) Will give you the Sheet Name of each sheet where Rows(0)
is the header row a

'nd each row represents one of the sheets.

cn.Close()

End Sub

This will definitely work


--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
 
New error "External Table Not In Expected Format"

_cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
OpenFileDialog1.FileName & ";Extended Properties=""Excel 8.0;HDR=YES;"""
 
You folks are most excellent. All works as advertised.

Strangely enough, when you don't try to use an Excel workbook, VB thinks the
table is not formatted correctly. Go figure. Garbage in, garbage out.

Problems solved.

Thank you both.
 
Back
Top