Returning Access Table properties to Excel

[James]

Hi,

I have the following code which does what i need , except it returns the
values to the debug window.

Sub Table_properties()

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim clm As ADOX.Column


cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\database.mdb"

For Each tbl In cat.Tables
Debug.Print "Table: " & " " & tbl.Name, tbl.Type

For Each clm In tbl.Columns
Debug.Print clm.Name, clm.Type,
clm.DefinedSize
Next
Next tbl
End Sub

If i had a recordset I could use :

While not myRS.EOF
i = i + 1
ws.[a1].cells(i) = MyRS ...
MyRS.movenext
Wend




I need to return this data somehow into excel,

Does anyone have any ideas please ?

Thanks

 

[Debra Dalgleish]

Something like this:

Sub Table_properties()

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim clm As ADOX.Column
Dim lRow As Long

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\database.mdb"
lRow = 0

For Each tbl In cat.Tables
For Each clm In tbl.Columns
lRow = lRow + 1
With ActiveSheet
.Cells(lRow, 1).Value = tbl.Name
.Cells(lRow, 2).Value = tbl.Type
.Cells(lRow, 3).Value = clm.Name
.Cells(lRow, 4).Value = clm.Type
.Cells(lRow, 5).Value = clm.DefinedSize
End With
Next
Next tbl
End Sub

Hi,

I have the following code which does what i need , except it returns the
values to the debug window.

Sub Table_properties()

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim clm As ADOX.Column


cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\database.mdb"

For Each tbl In cat.Tables
Debug.Print "Table: " & " " & tbl.Name, tbl.Type

For Each clm In tbl.Columns
Debug.Print clm.Name, clm.Type,
clm.DefinedSize
Next
Next tbl
End Sub

If i had a recordset I could use :

While not myRS.EOF
i = i + 1
ws.[a1].cells(i) = MyRS ...
MyRS.movenext
Wend




I need to return this data somehow into excel,

Does anyone have any ideas please ?

Thanks

 

[James]

Thanks Debra you are a star - thats exactly what i was looking for!

Regards
J

Something like this:

Sub Table_properties()

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim clm As ADOX.Column
Dim lRow As Long

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\database.mdb"
lRow = 0

For Each tbl In cat.Tables
For Each clm In tbl.Columns
lRow = lRow + 1
With ActiveSheet
.Cells(lRow, 1).Value = tbl.Name
.Cells(lRow, 2).Value = tbl.Type
.Cells(lRow, 3).Value = clm.Name
.Cells(lRow, 4).Value = clm.Type
.Cells(lRow, 5).Value = clm.DefinedSize
End With
Next
Next tbl
End Sub

Hi,

I have the following code which does what i need , except it returns the
values to the debug window.

Sub Table_properties()

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim clm As ADOX.Column


cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\database.mdb"

For Each tbl In cat.Tables
Debug.Print "Table: " & " " & tbl.Name, tbl.Type

For Each clm In tbl.Columns
Debug.Print clm.Name, clm.Type,
clm.DefinedSize
Next
Next tbl
End Sub

If i had a recordset I could use :

While not myRS.EOF
i = i + 1
ws.[a1].cells(i) = MyRS ...
MyRS.movenext
Wend




I need to return this data somehow into excel,

Does anyone have any ideas please ?

Thanks