Skip to Content
avatar image
Former Member

vb navigate to columnmap from column

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jul 02, 2017 at 01:29 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 30, 2017 at 07:15 PM

    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
    
    
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 30, 2017 at 07:17 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded