Getting sheet names from excel without named ranges.

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Is there a way to specify only table (sheet) names without getting the named
ranges?

oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null,
null, null, "TABLE"} );

I haven't been able to find anyway to distinguish between the two.

Thanks,
Joe
 
¤ Is there a way to specify only table (sheet) names without getting the named
¤ ranges?
¤
¤ oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null,
¤ null, null, "TABLE"} );
¤
¤ I haven't been able to find anyway to distinguish between the two.

The only way I've ever determined how to do it is to use the MSDASQL provider with ADOX. The type
returned for a Range is "Table" and Worksheet "System Table". Unfortunately the MSDASQL provider is
not supported by the .NET OLEDB library so you can't use the below connection string and
GetOleDbSchemaTable.

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

cnn.ConnectionString = "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=E:\My Documents\Book10.xls; ReadOnly=False;"

cnn.Open

Set cat.ActiveConnection = cnn

For Each tbl In cat.Tables
Debug.Print tbl.Name
Debug.Print tbl.type
Next tbl


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top