The following should give you a recordset of table infomation for the
connection:

Dim rstTables As ADODB.Recordset
Set rstTables = cnnConn.OpenSchema(adSchemaTables)

I'll leave it for you to look at the field names/values returned and
to decide how to present them for the user to choose a table name
(combobox on a userform, perhaps?)

--

(masayoshi hayashi) wrote in message . com...
I have created ADO connection sub below but I want to generate a list
of tables in the selected file, so that users don't have to know table
names of the selected Excel or Access file in advance. If you could
give me some hints or links for further study of excel ado, I would
be appreciated.

(undefined variables and objects are defined elsewhere.)

Sub ConnectToDB()
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim DBFileName As Variant
Dim TableName As String
Dim FilterList As String

' Get an access filename from wherever you want.
FilterList = "Access Files (*.mdb),*.mdb, " & "Excel Files
(*.xls),*.xls"
DBFileName = Application.GetOpenFilename(FileFilter:=FilterList ,
Title:="Database File")

' Exit if GetOpenFilename dialog box canceled
If DBFileName = False Then
MsgBox "No file was selected."
Cancel = True
Exit Sub
End If

' Request a table name in the DBFileName
Do Until TableName < ""
TableName = InputBox("Enter a table name in the database " &
DBFileName)
Loop

' Open a connection to the database file.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open DBFileName
End With

' SQL query to the Access database
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select * From " & TableName
.CommandType = adCmdText
.Execute
End With

' Open the TableName table as a recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create a pivot cache
Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
Set PC.Recordset = rstRecordset

Call CrossTabConnect

' Close the connection to the database.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing
End Sub