Good day Group,

Found the code below from this group that helps me to retrieve the Sheet
names from
a closed Workbook. Is it possible to get the name of the columns (fields)
from
a Worksheet in a closed Workbook in the same way? Is it posssible to get
the format (data types) of each column? As new to this I´am grateful for
some
hints.

Brgds

CG Rosen

----------------------------------------------------------------------------------

'Sub GetSheetNames()

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim lRow As Long
Dim szBookName As String
Dim szConnect As String
Dim szTableName As String

szBookName = "C:\Users\test1\db_test1.xls"

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sConString & ";" & _
"Extended Properties=Excel 8.0;"

Set cnn = New ADODB.Connection
cnn.Open szConnect
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn

Sheets("data").Range("B1:B100").ClearContents

lRow = 1
For Each tbl In cat.Tables
szTableName = tbl.Name
''' Worksheet names always end in the "$" character.
If Right$(szTableName, 1) = "$" Then
Sheets("data").Cells(lRow, 2).Value = _
Left$(szTableName, Len(szTableName) - 1)
lRow = lRow + 1
End If
Next tbl


cnn.Close
Set cat = Nothing
Set cnn = Nothing

End sub