Dump Table info into excel

Access 2003
Any way to dump table info into Excel?
I do not mean the data
I mean field names, data types, field length, Description....

Is this possible

Thanks

Dave

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5065 (20100427) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

According to what version of Access you have you can use the Documentor. Go
to TOOLS - DataBase Tools - Analyze - Documentor - select the table and the
options you want.

--
Build a little, test a little.


"Dave" wrote:

Access 2003
Any way to dump table info into Excel?
I do not mean the data
I mean field names, data types, field length, Description....

Is this possible

Thanks

Dave

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5065 (20100427) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Hi Dave,

Try the code below

Regards

Kevin

Sub TableAndFieldList()
Dim lngTable As Long
Dim lngField As Long
Dim db As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
lngRow = 1
On Error Resume Next
'Put out some column Headers
With wbExcel.Sheets(1)
.Range("A" & lngRow) = "Table"
.Range("B" & lngRow) = "FieldName"
.Range("C" & lngRow) = "FieldLen"
.Range("D" & lngRow) = "FieldType"
End With
Set ws = wbExcel.Sheets(1)
With ws.Range("A11").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A11").HorizontalAlignment = xlCenter
ws.Range("A11").Interior.ColorIndex = 15


ws.Range("A11").Borders(xlDiagonalDown).LineStyl e = xlNone
ws.Range("A11").Borders(xlDiagonalUp).LineStyle = xlNone
With ws.Range("A11").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True

'Loop through all tables
For lngTable = 0 To db.TableDefs.Count
'Do nothing if temporary or system table
If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
Left(db.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'Loop through each table, writing the table and field names
'to an Excel file
For lngField = 0 To db.TableDefs(lngTable).Fields.Count - 1
'For lngField = 0 To 2
lngRow = lngRow + 1
With wbExcel.Sheets(1)
.Range("A" & lngRow) = db.TableDefs(lngTable).Name
.Range("B" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Name
.Range("C" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Size
.Range("D" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Type
End With
Next lngField
lngRow = lngRow + 2
End If
Next lngTable
'Errors back in effect
On Error GoTo 0
ws.Columns("A:B").Select
ws.Columns("A:B").EntireColumn.AutoFit
'Set Excel to visible so user can save or let go
xlApp.Visible = True
xlApp.Quite
Set xlApp = Nothing
Set wbExcel = Nothing
Set db = Nothing

End Sub


"Dave" wrote in message
...
Access 2003
Any way to dump table info into Excel?
I do not mean the data
I mean field names, data types, field length, Description....

Is this possible

Thanks

Dave
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5065 (20100427) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

This is helpful but I did not see a way to dump into Excel (or a CSV) - it
just gives me a report

"KARL DEWEY" wrote in message
...
According to what version of Access you have you can use the Documentor.
Go
to TOOLS - DataBase Tools - Analyze - Documentor - select the table and
the
options you want.

--
Build a little, test a little.


"Dave" wrote:

Access 2003
Any way to dump table info into Excel?
I do not mean the data
I mean field names, data types, field length, Description....

Is this possible

Thanks

Dave

__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5065 (20100427) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5066 (20100427) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus signature database 5068 (20100428) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Thanks for the code but it is way over my head.
How do I run it?

Dave

"kc-mass" connearney_AT_comcast_DOT_net wrote in message
...
Hi Dave,

Try the code below

Regards

Kevin

Sub TableAndFieldList()
Dim lngTable As Long
Dim lngField As Long
Dim db As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
lngRow = 1
On Error Resume Next
'Put out some column Headers
With wbExcel.Sheets(1)
.Range("A" & lngRow) = "Table"
.Range("B" & lngRow) = "FieldName"
.Range("C" & lngRow) = "FieldLen"
.Range("D" & lngRow) = "FieldType"
End With
Set ws = wbExcel.Sheets(1)
With ws.Range("A11").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A11").HorizontalAlignment = xlCenter
ws.Range("A11").Interior.ColorIndex = 15


ws.Range("A11").Borders(xlDiagonalDown).LineStyl e = xlNone
ws.Range("A11").Borders(xlDiagonalUp).LineStyle = xlNone
With ws.Range("A11").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True

'Loop through all tables
For lngTable = 0 To db.TableDefs.Count
'Do nothing if temporary or system table
If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
Left(db.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'Loop through each table, writing the table and field names
'to an Excel file
For lngField = 0 To db.TableDefs(lngTable).Fields.Count - 1
'For lngField = 0 To 2
lngRow = lngRow + 1
With wbExcel.Sheets(1)
.Range("A" & lngRow) = db.TableDefs(lngTable).Name
.Range("B" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Name
.Range("C" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Size
.Range("D" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Type
End With
Next lngField
lngRow = lngRow + 2
End If
Next lngTable
'Errors back in effect
On Error GoTo 0
ws.Columns("A:B").Select
ws.Columns("A:B").EntireColumn.AutoFit
'Set Excel to visible so user can save or let go
xlApp.Visible = True
xlApp.Quite
Set xlApp = Nothing
Set wbExcel = Nothing
Set db = Nothing

End Sub


"Dave" wrote in message
...
Access 2003
Any way to dump table info into Excel?
I do not mean the data
I mean field names, data types, field length, Description....

Is this possible

Thanks

Dave
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5065 (20100427) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com






__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5068 (20100428) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus signature database 5068 (20100428) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com