Need to get Worksheet names from ADO

[nate axtell]

I am opening a client-side Excel file using ADO in VBScript like so:

XLSfile = "path to file"
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = "DBQ=" & XLSfile & _ ";DRIVER=Microsoft Excel
Driver(*.xls);UID=admin;"
Conn.Open

To query from a default datasheet name you would make a SQL string like
"SELECT * FROM [Sheet1$]".
I need to get the name of the worksheet from the Conn object somehow, so
that my Sheet1 name can be dynamic. Is there a way to get a collection of
the worksheets in an Excel file using this connection object? That way I
could specify something like "Select * From ["&sheetname&"$]".
If I can't use this driver to do the above is there another ADO driver that
I could use?

Thanks,
Nate

 

[nate axtell]

Like my post says, I'm programming in VBScript and obviously in an ASP page.
No .NET, just regular ASP.
I have found the Excel.Application object out there that allows access to a
workbook and worksheets. But this is considered an Unsafe Activex Control
which we have IE prompt every time the page loads. This is undesirable,
hence we can't use it.
nate

 

[Guest]

Sorry, I haven't programmed ASP pages in 7 years. I thought you'd be able to use an OLEDb connection.

 

[onedaywhen]

Yes, use the connection object's OpenSchema method to create a
recordset which contains the table names e.g.

Set rsTables = oConn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "Table"))
MsgBox rs!TABLE_NAME

 

[nate axtell]

Thanks for pointing me in the right direction.
I ended up having to use the enumeration value for "adSchemaTables", which
is 20, otherwise i just couldn't get it to work.
Also, if I had more than 1 sheet in the file it would not work. So I had to
remove the Array(..."table") parameter.
The following is what I ended up with.

Dim rsSchema, field, line
Set rsSchema = Conn.OpenSchema(20)
rsSchema.MoveFirst
Do While Not rsSchema.EOF
For Each field In rsSchema.Fields
If field <> "" Then
line = line & field & " "
End if
Next
line = line & vbcrlf
rsSchema.MoveNext
Loop
rsSchema.Close
msgbox line

That will display a list of all the sheets in the excel file for me.
Thanks again,
nate