The previous article introduced how to import the table structure written by Excel into PowerDesigner (see the link: PowerDesigner import table structure from Excel )In the actual work, it is often that the two transform into each other.

Here is the export script corresponding to the agreed Excel format in that article:

'******************************************************************************
'* File:     pdm2excel.txt
'* Title:    pdm export to excel
'* Purpose:  To export the tables and columns to Excel
'* Model:    Physical Data Model
'* Objects:  Table, Column, View
'* Author:   ziyan
'* Created:  2012-05-03
'* Version:  1.0
'******************************************************************************
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 is not an PDM model."
Else
 ' Get the tables collection
 'Establish EXCEL APP
 dim beginrow
 DIM EXCEL, SHEET
 set EXCEL = CREATEOBJECT("Excel.Application")
 EXCEL.workbooks.add(-4167)'Add sheet
 EXCEL.workbooks(1).sheets(1).name ="test"
 set sheet = EXCEL.workbooks(1).sheets("test")
 
 ShowProperties Model, SHEET
 EXCEL.visible = true
 'Set column width and wrap
 sheet.Columns(1).ColumnWidth = 40 
 sheet.Columns(2).ColumnWidth = 30 
 sheet.Columns(4).ColumnWidth = 30 
 sheet.Columns(5).ColumnWidth = 30 
 sheet.Columns(6).ColumnWidth = 20 
 sheet.Columns(7).ColumnWidth = 40 
 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 Each 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
      sheet.cells(rowsNum, 1) = tab.name
      sheet.cells(rowsNum, 2) = tab.code
	  sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,2)).Borders.LineStyle = "1"
	  sheet.Range(sheet.cells(rowsNum,1),sheet.cells(rowsNum,2)).Font.Size=20

	  
	  rowsNum = rowsNum + 1
      sheet.cells(rowsNum, 1) = "Field meaning"
      sheet.cells(rowsNum, 2) = "Field name"
      sheet.cells(rowsNum, 3) = "Field type"
      sheet.cells(rowsNum, 4) = "Is the primary key?"
      sheet.cells(rowsNum, 5) = "Default value"
      sheet.cells(rowsNum, 6) = "Is it available?"
	  sheet.cells(rowsNum, 7) = "Notes"
	  sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,7)).Borders.LineStyle = "1"
	  sheet.Range(sheet.cells(rowsNum,1),sheet.cells(rowsNum,7)).Font.Size=14


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.code
      sheet.cells(rowsNum, 3) = ""
      sheet.cells(rowsNum, 4) = ""
      sheet.cells(rowsNum, 5) = ""
      sheet.cells(rowsNum, 6) = ""
	  sheet.cells(rowsNum, 7) = col.name
      next
      sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,7)).Borders.LineStyle = "2"      
 
      Output "FullDescription: "       + tab.Name
   End If
End Sub

Just call up the command window with Crtl+Shift+X, and execute this script to export the table structure of PowerDesigner to an Excel file of the agreed format.

The exported Excel looks like this: