Using the Connection object's OpenSchema method:

Sub test()
Dim Con As Object
Dim rs As Object
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\db.xls;" & _
"Extended Properties=Excel 8.0"
.Open
Set rs = .OpenSchema(20) ' adSchemaTables
End With
With rs
Do While Not .EOF
Debug.Print !TABLE_NAME
.MoveNext
Loop
.Close
End With
Con.Close
End Sub

--

ChaunceyMo wrote in message ...
Hi all,

I am importing excels into code via an adodb connection, but have run
into the problem of many of the excels not having standard sheet names
(ie- "import" instead of "Sheet1"). Is there a way to do a SELECT
query without knowing the name of the sheet? This is further
complicated by the workbooks potentially having two sheets rather than
just one, again with an unknown name.

A method that iterates through the sheets sequentially would be
terrific.

Thanks in advance,
Mo


---
Message posted from http://www.ExcelForum.com/