Connect to Excel with ADO - don't know Sheet Names

Question

Hi,

I'm connecting to Excel with ADO, but it only works if I specify the sheet names. I won't know those at runtime. Is there any method to get the sheet names? Excel automation?

Thanks.

Answer 1

Once you've open the ADO connection, you need call the OpenSchema() method which returns a results set with the sheet names as "table_name"

I'm a bit rusty with VBA but that should look something like that

Dim oConn
DIm oRs
Set oConn = New ADODB.Connection
Dim sConn
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=somepathtoXLS; xxx not sure some other connection str props..."
oConn.Open sConn

Set oRS = oConn.OpenSchema(adSchemaTables)
Do While Not oRS.EOF 
   sSheetName = oRS.Fields("table_name").Value
   // do somethng with the sSheetName
   oRS.MoveNext()
Loop

Answer 2

Via automation:

(1) set a reference to the appropriate Excel library - I'm using Excel 2003 so it's "Microsoft Excel 11.0 Object Library"

(2) create an Excel Application object

(3) open the relevant Workbook

(4) iterate through the Worksheets collection

(5) get the Name property of each Worksheet

Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet

Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\foo.xls")

For Each ws In wb.Worksheets
    MsgBox ws.Name
Next ws

wb.Close
xlApp.Quit