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

Have you tried here ?

http://msdn.microsoft.com/en-us/libr...8VS.85%29.aspx

Tim

"CG Rosen" wrote in message
...


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

The column headers are in Row 1 of each table. Using ADO method
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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200033

http://www.thecodecage.com/forumz

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200033

http://www.thecodecage.com/forumz