Copy the following and paste it into the Tools -> Execute Commands ->
Edit/Run Script dialog box.
'***************************************************************************
***
'* File: DumpPDMToExcel.vbs
'* Purpose: Scan through tables and column, outputting the data into an
Excel
'* spreadsheet.
'* The column list was specified by Sandy Lucas for import into
'* the Metadata repository.
'* Why/When: You would use this function to create an Excel spreadsheet
'* with 2 sheets named Table and Column with
'* a specific layout on each sheet. This is typically done to
'* export the table and column information to the Metadata
repository.
'* Title: Dump PDM to Excel
'* Category: Excel
'* Version: 1.0
'* Company: John Deere Credit
'* Author: Mike Nicewarner
'* 10/7/2003 MFN Initial Version
'***************************************************************************
***
Option Explicit
Dim mdl ' the current model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
MsgBox "There is no Active Model"
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then
MsgBox "The Active Model is not a PDM."
else
' Create Excel Document
Dim xlApp
Dim xlBook
Dim xlTSheet, xlCSheet
dim t
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
xlBook.Title = "PowerDesigner PDM Dump"
With xlBook.Styles.Add("Header1")
.Font.Bold = True
.Font.Name = "Times New Roman"
End With
Set xlCSheet = xlBook.Worksheets.Add
xlCSheet.Name = "Columns"
xlApp.DisplayAlerts = False
xlApp.Worksheets("Sheet1").Delete
if xlApp.Worksheets.count > 1 then
xlApp.Worksheets("Sheet2").Delete
end if
if xlApp.Worksheets.count > 1 then
xlApp.Worksheets("Sheet3").Delete
end if
xlApp.DisplayAlerts = True
Set xlTSheet = xlBook.Worksheets.Add
xlTSheet.Name = "Tables"
' Make the "Tables" sheet
xlTSheet.Visible = True
xlTSheet.Range("A1").Value = "Name"
xlTSheet.Range("B1").Value = "Code"
xlTSheet.Range("C1").Value = "Comment"
xlTSheet.Range("A1:C1").Style = "Header1"
' Make the "Columns" sheet
xlCSheet.Visible = True
xlCSheet.Range("A1").Value = "Name"
xlCSheet.Range("B1").Value = "Code"
xlCSheet.Range("C1").Value = "Comment"
xlCSheet.Range("D1").Value = "Data Type"
xlCSheet.Range("E1").Value = "Length"
xlCSheet.Range("F1").Value = "Precision"
xlCSheet.Range("G1").Value = "Primary"
xlCSheet.Range("H1").Value = "Table"
xlCSheet.Range("I1").Value = "Mandatory"
xlCSheet.Range("J1").Value = "Column Sequence"
xlCSheet.Range("A1:J1").Style = "Header1"
Dim nt, nc
Dim fldr
dim colseq
nt = 2
nc = 2
Set Fldr = ActiveDiagram.Parent
ListTables(fldr)
xlTSheet.Columns("A:C").EntireColumn.AutoFit 'To adjust the column's
width.
xlCSheet.Columns("A:J").EntireColumn.AutoFit 'To adjust the column's
width.
end if
Sub ListTables(fldr)
Dim tab ' running object
Dim f ' running folder
For Each tab In fldr.children
DescribeTable tab
Next
' go into the sub-packages
For Each f In fldr.Packages
ListTables f
Next
End Sub
Sub DescribeTable(CurTab)
if CurTab.ObjectType = "Table" then
xlTSheet.Range("A"+Cstr(nt)).Value = CurTab.Name
xlTSheet.Range("B"+Cstr(nt)).Value = CurTab.Code
xlTSheet.Range("C"+Cstr(nt)).Value = CurTab.Comment
nt = nt + 1
' Get columns for this table to Column sheet
dim col
colseq = 1
For Each col in CurTab.Columns
DescribeColumn col
next
end if
End Sub
Sub DescribeColumn(CurCol)
xlCSheet.Range("A"+Cstr(nc)).Value = CurCol.Name
xlCSheet.Range("B"+Cstr(nc)).Value = CurCol.Code
t = CurCol.Comment
if right(t, 1) = chr(10) then
t = left(t, len(t) - 2)
end if
xlCSheet.Range("C"+Cstr(nc)).Value = t
xlCSheet.Range("D"+Cstr(nc)).Value = CurCol.DataType
xlCSheet.Range("E"+Cstr(nc)).Value = Cstr(CurCol.Length)
xlCSheet.Range("F"+Cstr(nc)).Value = Cstr(CurCol.Precision)
xlCSheet.Range("G"+Cstr(nc)).Value = Cstr(CurCol.Primary)
xlCSheet.Range("H"+Cstr(nc)).Value = CurCol.Table.Code
xlCSheet.Range("I"+Cstr(nc)).Value = Cstr(CurCol.Mandatory)
xlCSheet.Range("J"+Cstr(nc)).Value = Cstr(colseq)
nc = nc + 1
colseq = colseq + 1
End Sub
--
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement
Is there a quick way to export the Table Attributes to an
EXCEL file?
Also, can I build a report that list the columns and their
attributes?