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

 

[Dave Patrick]

Maybe this helps.

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

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\database.mdb"
For Each tbl In cat.Tables
Sheets.Add
ActiveSheet.Name = tbl.Name
i = 1
With ActiveSheet
For Each clm In tbl.Columns
Cells(i, 1) = clm.Name
Cells(i, 2) = clm.Type
Cells(i, 3) = clm.DefinedSize
i = i + 1
Next
End With
Next tbl
End Sub

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[James]

Sh**

That is amazing!

Thanks very much

Maybe this helps.

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

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\database.mdb"
For Each tbl In cat.Tables
Sheets.Add
ActiveSheet.Name = tbl.Name
i = 1
With ActiveSheet
For Each clm In tbl.Columns
Cells(i, 1) = clm.Name
Cells(i, 2) = clm.Type
Cells(i, 3) = clm.DefinedSize
i = i + 1
Next
End With
Next tbl
End Sub

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

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

 

[Dave Patrick]

You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect