I'm trying to create a little vb program to export source 2 target to excel. So far I have the target columns working, but I'm not sure how to navigate from a column to a column mapping and then from a column in the column in the column mapping to that column. Thanks in Advance!
Sub %Method%(obj) 'on error resume next DIM vExcel ' Implement your method on <obj> here Set vExcel = CreateObject("Excel.Application") ' dim colmap dim lineNum dim Column vExcel.Visible = True vExcel.Workbooks.Add vExcel.Range("A1").Value = "ID" 'Source Column Order vExcel.Range("B1").Value = "Source Table" vExcel.Range("C1").Value = "Source Column" vExcel.Range("D1").Value = "Source Data Type" vExcel.Range("E1").Value = "Source Key" vExcel.Range("F1").Value = "Source Nulls Allowed" vExcel.Range("G1").Value = " " vExcel.Range("H1").Value = "Sample Data " vExcel.Range("I1").Value = " " vExcel.Range("J1").Value = "ID " 'Target Column Order vExcel.Range("K1").Value = "Target Table" vExcel.Range("L1").Value = "Target Column" vExcel.Range("M1").Value = "Target Data Type" vExcel.Range("N1").Value = "Target Key" vExcel.Range("O1").Value = "Target Mandatory" vExcel.Range("P1").Value = " " vExcel.Range("Q1").Value = "Source Transformation Rules" lineNum=2 for each column in obj.Columns 'msgbox "Column " + column.table.code 'vExcel.Range("A"+Cstr(lineNum)).Value = CStrObj(colmap.DataSource ) ' source column id 'vExcel.Range("B"+Cstr(lineNum)).Value = colmap.Table ' source Table 'vExcel.Range("C"+Cstr(lineNum)).Value = CStrObj(obj.Column ) 'source column 'vExcel.Range("D"+Cstr(lineNum)).Value = "" 'source data type 'vExcel.Range("E"+Cstr(lineNum)).Value = "" ' source key 'vExcel.Range("F"+Cstr(lineNum)).Value = "" 'source Nulls Allowed 'vExcel.Range("G"+Cstr(lineNum)).Value = "" 'vExcel.Range("H"+Cstr(lineNum)).Value = "" 'Target vExcel.Range("J"+Cstr(lineNum)).Value = lineNum-1 vExcel.Range("K"+Cstr(lineNum)).Value = column.table.code vExcel.Range("L"+Cstr(lineNum)).Value = column.code vExcel.Range("M"+Cstr(lineNum)).Value = column.DataType If column.primary Then vExcel.Range("N"+Cstr(lineNum)).Value = "P" Else If column.foreignKey Then vExcel.Range("N"+Cstr(lineNum)).Value = "F" Else vExcel.Range("N"+Cstr(lineNum)).Value = "NA" End If End If If column.mandatory Then vExcel.Range("O"+Cstr(lineNum)).Value = "N" Else vExcel.Range("O"+Cstr(lineNum)).Value = "Y" End If 'vExcel.Range("P"+Cstr(lineNum)).Value = colmap.Expression lineNum = lineNum + 1 next vExcel.Columns("A:Q").EntireColumn.AutoFit End Sub
From your code, it sounds like a mapping between PDM and CDM. Assume you have already set up the mapping.
1) Open CDM.
2) Open PDM.
Execute the following code. You need modify it to meet your need
set model=activeModel
set tables=model.tables
for each tab in tables
set columns=tab.columns
for each c in columns
set sourceMaps=c.sourceMaps
for each m in sourceMaps
SourceColumn = split(m.displayName,".")
SourceName= tab.name & "." & SourceColumn(1)
output Sourcename & " <-------------> " & m.MappedTo
next
next
next
figured out the first part
Sub %Method%(obj) ' Implement your method on <obj> here '****************************************************************************** '* File: Export_model_to_excel.vbs '* Purpose: Scan CDM Model and display objects information ' Export object desc to Excel '* Title: '* Category: '* Version: 1.0 '* Company: Sybase Inc. '****************************************************************************** 'Option Explicit on error resume next Dim nb dim cm dim col dim lineNum ' ' get the current active model ' Dim mdl ' the current model Set mdl = ActiveModel If (mdl Is Nothing) Then MsgBox "There is no Active Model" End If Dim fldr Set Fldr = ActiveDiagram.Parent nb =2 Dim HaveExcel Dim RQ HaveExcel= True ' Open & Create Excel Document Dim x1 ' Set x1 = CreateObject("Excel.Application") x1.Visible = True x1.Workbooks.Add x1.Range("A1").Select x1.Range("A1").Value = "Line" x1.Range("B1").Value = "Table" x1.Range("C1").Value = "Column" x1.Range("D1").Value = "DataType" x1.Range("E1").Value = "Key" x1.Range("F1").Value = "Mandatory" x1.Range("G1").Value = "Map Code" x1.Range("H1").Value = "Map Name" x1.Range("I1").Value = "Class Type" x1.Range("J1").Value = "Mapped Table" x1.Range("K1").Value = "Mapped Column" x1.Range("L1").Value = "Mapped To" x1.Range("M1").Value = "Mapped Expression" lineNum = 2 for each col in obj.Columns x1.Range("A"+Cstr(lineNum)).Value = lineNum-1 x1.Range("B"+Cstr(lineNum)).Value = col.table.code x1.Range("C"+Cstr(lineNum)).Value = col.code x1.Range("D"+Cstr(lineNum)).Value = col.DataType If column.primary Then x1.Range("E"+Cstr(lineNum)).Value = "P" Else If column.foreignKey Then x1.Range("E"+Cstr(lineNum)).Value = "F" Else vx1.Range("E"+Cstr(lineNum)).Value = "NA" End If End If If column.mandatory Then x1.Range("F"+Cstr(lineNum)).Value = "N" Else x1.Range("F"+Cstr(lineNum)).Value = "Y" End If for each cm in col.Mappings if cm.ClassName = "External Column Mapping" then for each cm in col.ExternalColumnMapping x1.Range("G"+Cstr(lineNum)).Value = cm.code x1.Range("H"+Cstr(lineNum)).Value = cm.name x1.Range("I"+Cstr(lineNum)).Value = cm.ClassName x1.Range("J"+Cstr(lineNum)).Value = cm.Table x1.Range("K"+Cstr(lineNum)).Value = cm.Column x1.Range("L"+Cstr(lineNum)).Value = cm.MappedTo x1.Range("M"+Cstr(lineNum)).Value = cm.Expression else x1.Range("G"+Cstr(lineNum)).Value = cm.code x1.Range("H"+Cstr(lineNum)).Value = cm.name x1.Range("I"+Cstr(lineNum)).Value = cm.ClassName x1.Range("J"+Cstr(lineNum)).Value = cm.Table x1.Range("K"+Cstr(lineNum)).Value = cm.Column x1.Range("L"+Cstr(lineNum)).Value = cm.MappedTo x1.Range("M"+Cstr(lineNum)).Value = cm.Expression end if next lineNum = lineNum + 1 next x1.Columns("A:P").EntireColumn.AutoFit 'To adjust the column's width. End Sub
I worked through the first part for get the mappings within the model. Now I'm trying to figure out how to get the externalcolumnmapping.