Hi Mike,

You can use a combination of ADO and ADOX to do this:

Sub test()
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog

cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=c:\test.xls"
Set cat.ActiveConnection = cnn
For Each tbl In cat.Tables
Debug.Print "Table: " & Left$(tbl.Name, Len(tbl.Name) - 1)
For Each col In tbl.Columns
Debug.Print " Column: " & col.Name
Debug.Print " Type: " & col.Type
Next col
Next tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub

The Type property will give you a long value - you'll have to match those up
with the DataTypeEnum enumeration, which is part of the ADOX library.

For this code to work, you must set a reference to "Microsoft ActiveX Data
Objects 2.x Library" and "Microsoft ADO Ext. 2.x for DDL and Security".

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


mike wrote:
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel file?