Skip to Content
0

vb navigate to columnmap from column

Jun 30, 2017 at 01:41 AM

71

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Phillip Lam
Jul 02, 2017 at 01:29 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Bill S. Jun 30, 2017 at 07:15 PM
0

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




Share
10 |10000 characters needed characters left characters exceeded
Bill S. Jun 30, 2017 at 07:17 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded