Connecting to Excel with ADO - I don’t know Sheet Names

Question

I connect to Excel with ADO, but it only works if I specify the sheet names. I will not know them at runtime. Is there any way to get sheet names? Excel Automation?

Thanks.

Answer 1

Once you open the ADO connection, you need to call the OpenSchema () method, which returns a result set with sheet names in the form "table_name"

I'm a little rusty with VBA, but it should look something like this.

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

Through automation:

(1) set the link to the corresponding Excel library - I use Excel 2003 so that it is "Microsoft Excel 11.0 Object Library"

(2) create an Excel Application object

(3) open the corresponding Workbook

(4) iterations over the Worksheets collection

(5) get the Name property for 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