VB Forum / Database Access / April 2007

¤ Hi,
¤
¤ when i read some data from a excel-file via ado, then I can select the sheet
¤ over the sheet name: "SELECT  * FROM [" + SheetName + "]". So I must know
¤ the name of the sheet.
¤ It is possible to read the data, when I don't know the name of the sheet?
¤ For instance: read data from the first sheet in the excel file?

Well yes you have to know the name of the Sheet. However, it is possible to retrieve the names of
all Excel Worksheets in a Workbook (in alphabetical order).

Dim cnn As New ADODB.Connection
Dim rsSchema As New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Test Files\Book20.xls;Extended Properties=Excel 8.0;"

Set rsSchema = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, Empty))

While Not rsSchema.EOF
   
   Debug.Print rsSchema.Fields("TABLE_NAME").Value
   rsSchema.MoveNext

Wend
 
rsSchema.Close
cnn.Close

Now if you want the Worksheets in the order that they appear in the Workbook (instead of
alphabetical) you have to use DAO:

Dim xlWB As dao.Database
Dim tbl As dao.TableDef
Dim strFirstSheetName As String

Set xlWB = OpenDatabase("C:\Test Files\Book20.xls", False, True, "Excel 8.0;")

'Below returns name of first Worksheet in the Workbook
'Debug.Print xlWB.TableDefs(0).Name

'All Workbooks by ordinal position
For Each tbl In xlWB.TableDefs
   Debug.Print tbl.Name
Next tbl

xlWB.Close

Paul
~~~~
Microsoft MVP (Visual Basic)