PowerDesigner physical model to generate Excel files

Source: http://ray-allen.iteye.com/blog/1893347

Shortcut keys ctrl+alt+x or Tools->execute commands->edit/run Script

‘******************************************************************************

' * File:pdm2excel.txt

' * TITLE:PDM Export to Excel

' * purpose:to export the tables and columns to Excel

' * model:physical Data Model 16.5, Microsoft Office 2007

' * objects:table, Column, View

‘******************************************************************************

Option Explicit

Dim Rowsnum

Rowsnum = 0

‘-----------------------------------------------------------------------------

' Main function

‘-----------------------------------------------------------------------------

' Get the current active model

Dim Model

Set Model = Activemodel

If (Model is Nothing) Or (not Model.iskindof (Pdpdm.cls_model)) Then

MsgBox "The current model isn't an PDM model."

Else

' Get the Tables collection

' Create Excel APP

Dim Beginrow

DIM EXCEL, SHEET

Set EXCEL = CreateObject ("Excel.Application")

EXCEL.workbooks.add (-4167) ' Add worksheet

Excel.Workbooks (1). Sheets (1). Name = "Test"

Set sheet = excel.workbooks (1). Sheets ("test")



ShowProperties Model, SHEET

Excel.visible = True

' Set column widths and wrap lines

Sheet. Columns (1). ColumnWidth = 20

Sheet. Columns (2). ColumnWidth = 40

Sheet. Columns (4). ColumnWidth = 20

Sheet. Columns (5). ColumnWidth = 20

Sheet. Columns (6). ColumnWidth = 15

Sheet. Columns (1). WrapText =true

Sheet. Columns (2). WrapText =true

Sheet. Columns (4). WrapText =true

End If

‘-----------------------------------------------------------------------------

' Show Properties of tables

‘-----------------------------------------------------------------------------

Sub showproperties (MDL, sheet)

' Show tables of the current Model/package

Rowsnum=0

Beginrow = rowsnum+1

' For each table

Output "Begin"

Dim tab

For all tab in Mdl.tables

Showtable Tab,sheet

Next

If Mdl.tables.count > 0 Then

Sheet. Range ("a" & Beginrow + 1 & ": A" & Rowsnum). Rows.group

End If

Output "End"

End Sub

‘-----------------------------------------------------------------------------

' Show Table Properties

‘-----------------------------------------------------------------------------

Sub showtable (tab, sheet)

If isobject (tab) then

Dim Rangflag

Rowsnum = rowsnum + 1

' Show Properties

Output "================================"

Sheet.cells (rowsnum, 1) = "Entity name"

Sheet.cells (Rowsnum, 2) =tab.name

Sheet.cells (Rowsnum, 3) = ""

Sheet.cells (Rowsnum, 4) = "Table name"

Sheet.cells (Rowsnum, 5) = Tab.code

Sheet. Range (Sheet.cells (Rowsnum, 5), Sheet.cells (Rowsnum, 6)). Merge

Rowsnum = rowsnum + 1

Sheet.cells (rowsnum, 1) = "Property name"

Sheet.cells (Rowsnum, 2) = "description"

Sheet.cells (Rowsnum, 3) = ""

Sheet.cells (Rowsnum, 4) = "Field Chinese name"

Sheet.cells (Rowsnum, 5) = "Field name"

Sheet.cells (Rowsnum, 6) = "Field type"

' Set border

Sheet. Range (Sheet.cells (rowsNum-1, 1), Sheet.cells (Rowsnum, 2)). Borders.LineStyle = "1"

Sheet. Range (Sheet.cells (RowsNum-1, 4), Sheet.cells (Rowsnum, 6)). Borders.LineStyle = "1"

Dim Col ' running column

Dim Colsnum

Colsnum = 0

For each col in Tab.columns

Rowsnum = rowsnum + 1

Colsnum = colsnum + 1

Sheet.cells (rowsnum, 1) = Col.name

Sheet.cells (Rowsnum, 2) = Col.comment

Sheet.cells (Rowsnum, 3) = ""

Sheet.cells (Rowsnum, 4) = Col.name

Sheet.cells (Rowsnum, 5) = Col.code

Sheet.cells (Rowsnum, 6) = Col.datatype

Next

Sheet. Range (Sheet.cells (rowsnum-colsnum+1,1), Sheet.cells (rowsnum,2)). Borders.LineStyle = "2"

Sheet. Range (Sheet.cells (rowsnum-colsnum+1,4), Sheet.cells (rowsnum,6)). Borders.LineStyle = "2"

Rowsnum = rowsnum + 1



Output "fulldescription:" + TAB. Name

End If

End Sub

PowerDesigner physical model to generate Excel files