Get column name and data type from closed workbook

[CG Rosen]

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

 

[Tim Williams]

Have you tried here ?

http://msdn.microsoft.com/en-us/library/ms677529(VS.85).aspx

Tim

 

[joel]

The column headers are in Row 1 of each table. Using ADO metho
indexing starts at zero so you are lokking at row 0.

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, "B").Value = _
Left$(szTableName, Len(szTableName) - 1)
Set HeaderRow = tbl.Rows(0)
ColCount = 3
for each col in HeaderRow.Cells
Sheets("data").Cells(lRow, ColCount).Value = col
ColCount = ColCount + 1
next col
lRow = lRow + 1
End If
Next tbl

 

[Tim Williams]

See modified version of your code below.
You'll need to look up the column type enum values here:
http://www.w3schools.com/ADO/ado_datatypes.asp

Tim

'**********************************************
Sub GetSheetNamesAndColumnTypes()

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

szBookName = ThisWorkbook.Path & "\db_test1.xls"

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

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

Sheet1.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
Sheet1.Cells(lRow, 2).Value = _
Left$(szTableName, Len(szTableName) - 1)
For Each c In tbl.Columns
Sheet1.Cells(lRow, 3).Value = c.Name
Sheet1.Cells(lRow, 4).Value = c.Type
lRow = lRow + 1
Next c
lRow = lRow + 1
End If
Next tbl

cnn.Close
Set cat = Nothing
Set cnn = Nothing

End Sub
'***********************************************

 

[CG Rosen]

Many thanks Tim, works perfect!

brgds

CG Rosen

 

[joel]

Was it my code or Tim's website that solved your problem?