Skip to Content

In this article I will provide universal VBA code to run DM package passing parameters. All parameters are passed as array of strings using some simple format. Actual values for strings can be copied from DM package log (looking also on advanced DM script).

The following references are required in Tools -> References:

ExecuteDM code:

' Execute DM package using array of strings strAnswer
Public Sub ExeceuteDM(ByRef strAnswerArr() As String, strTeam As String, strPackageCroup As String, _
    strPackage As String, strChain As String, strUserGroup As String)
' Developed by Vadim Kalinin based on:
' https://blogs.sap.com/2013/01/10/how-to-deal-with-bpc-data-manager-packages-programmatically/
' https://archive.sap.com/documents/docs/DOC-32636
' https://blogs.sap.com/2014/05/01/magic-button-to-create-a-dimension-member-from-an-input-form/
'
' strAnswerArr() - each line define a single prompt variable
' Line format: %VARIABLENAME%xVALUE
' x can be "V" for simple sting value or "P"/"D" for complex string containing dimension names and dimension members
' "P" is used for SELECTINPUT, SELECT, COPYMOVE, COPYMOVEINPUT, MEMBERFROMTOINPUT
' "D" is used for DIMENSIONMEMBER
' Single empty line - no answer prompt
' strTeam - Team, "" for Company
' strPackageCroup - Package Group like "Data Management"
' strPackage - Package name like "Clear"
' strChain - Process Chain name like "/CPMB/CLEAR"
' strUserGroup - User "0001", Admin "0000"

    'xml constants to prepare answer file
    Const H1 As String = "{param_separator}<?xml version=""1.0"" encoding=""utf-16""?>"
    Const B1 As String = "<ArrayOfAnswerPromptPersistingFormat xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">"
    Const B2 As String = "</ArrayOfAnswerPromptPersistingFormat>"
    Const B3 As String = "<ArrayOfAnswerPromptPersistingFormat xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" />"
    Const F1 As String = "  <AnswerPromptPersistingFormat>"
    Const F2 As String = "  </AnswerPromptPersistingFormat>"
    Const A1 As String = "    <_ap>"
    Const A2 As String = "    </_ap>"
    Const N1 As String = "      <Name>"
    Const N2 As String = "</Name>"
    Const V1 As String = "      <Values>"
    Const V2 As String = "      </Values>"
    Const V3 As String = "      <Values />"
    Const S1 As String = "        <string>"
    Const S2 As String = "</string>"
    Const S3 As String = "        <string />"
    Const C1 As String = "    <_apc>"
    Const C2 As String = "    </_apc>"
    Const P1 As String = "      <StringListPair>"
    Const P2 As String = "      </StringListPair>"
    Const T1 As String = "        <str>"
    Const T2 As String = "</str>"
    Const L1 As String = "        <lst>"
    Const L2 As String = "        </lst>"
    Const L3 As String = "        <lst />"
    Const R1 As String = "          <string>"
    Const R2 As String = "</string>"
    
    Dim epmDM As New FPMXLClient.EPMAddInDMAutomation
    Dim epmDMPackage As New FPMXLClient.ADMPackage
    Dim strAnswerFileName As String
    Dim adoStream As New ADODB.Stream ' to write UTF-8 text
    Dim lngTypePos As Long
    Dim strVar As String
    Dim strType As String
    Dim strValue As String
    Dim strDimArr() As String
    Dim strDimMemArr() As String
    Dim strMemArr() As String
    Dim lngTemp As Long
    Dim lngTemp1 As Long
    Dim lngTemp2 As Long
    
    Dim TypeLib As Object
    Dim strGuid As String

    ' Define DM package to run
    With epmDMPackage
        .Filename = strChain
        .groupId = strPackageCroup
        .packageId = strPackage
        .PackageDesc = ""
        .PackageType = "Process Chain"
        .teamId = strTeam
        .UserGroup = strUserGroup
    End With
    
    ' Answer xml file will be created in User's Documents folder
    ' Something like: DM2017061615305786_Export_Master_Data_to_File.xml
    ' It's not possible to delete this file at the end of this procedure - file locked for some time...
    strAnswerFileName = Environ$("USERPROFILE") & "\Documents\DM" & Format(Now(), "yyyymmddHhNnSs") & _
        Right(Format(Timer, "#0.00"), 2) & "_" & Replace(strPackage, " ", "_") & ".xml"
    
    ' Create stream for answer file with UTF-8 encoding
    adoStream.Charset = "UTF-8"
    adoStream.Open
    
    adoStream.WriteText strPackage & H1, StreamWriteEnum.stWriteLine
    If UBound(strAnswerArr) = 0 And strAnswerArr(0) = "" Then
        adoStream.WriteText B3, StreamWriteEnum.stWriteLine
    Else
        adoStream.WriteText B1, StreamWriteEnum.stWriteLine
        
        For lngTemp = 0 To UBound(strAnswerArr)
            lngTypePos = InStr(2, strAnswerArr(lngTemp), "%") + 1 ' Next after %
            strVar = Mid(strAnswerArr(lngTemp), 1, lngTypePos - 1)
            adoStream.WriteText F1, StreamWriteEnum.stWriteLine
            adoStream.WriteText A1, StreamWriteEnum.stWriteLine
            adoStream.WriteText N1 & strVar & N2, StreamWriteEnum.stWriteLine
            strType = Mid(strAnswerArr(lngTemp), lngTypePos, 1)
            strValue = Mid(strAnswerArr(lngTemp), lngTypePos + 1)
            If strType <> "V" Then
                strDimArr = Split(strValue, "|DIMENSION:")
                If strType = "D" Then
                    strDimMemArr = Split(strDimArr(1), "|")
                    adoStream.WriteText V1, StreamWriteEnum.stWriteLine
                    adoStream.WriteText S1 & strDimMemArr(0) & S2, StreamWriteEnum.stWriteLine
                    adoStream.WriteText V2, StreamWriteEnum.stWriteLine
                    adoStream.WriteText A2, StreamWriteEnum.stWriteLine
                    adoStream.WriteText C1, StreamWriteEnum.stWriteLine
                    adoStream.WriteText P1, StreamWriteEnum.stWriteLine
                    adoStream.WriteText T1 & strDimMemArr(0) & T2, StreamWriteEnum.stWriteLine
                    If strDimMemArr(1) <> "" Then
                        adoStream.WriteText L1, StreamWriteEnum.stWriteLine
                        strMemArr = Split(strDimMemArr(1), ",")
                        For lngTemp2 = 0 To UBound(strMemArr)
                            adoStream.WriteText R1 & strMemArr(lngTemp2) & R2, StreamWriteEnum.stWriteLine
                        Next lngTemp2
                        adoStream.WriteText L2, StreamWriteEnum.stWriteLine
                    Else
                        adoStream.WriteText L3, StreamWriteEnum.stWriteLine
                    End If
                    adoStream.WriteText P2, StreamWriteEnum.stWriteLine
                Else
                    adoStream.WriteText V3, StreamWriteEnum.stWriteLine
                    adoStream.WriteText A2, StreamWriteEnum.stWriteLine
                    adoStream.WriteText C1, StreamWriteEnum.stWriteLine
                    For lngTemp1 = 1 To UBound(strDimArr)
                        strDimMemArr = Split(strDimArr(lngTemp1), "|")
                        adoStream.WriteText P1, StreamWriteEnum.stWriteLine
                        adoStream.WriteText T1 & strDimMemArr(0) & T2, StreamWriteEnum.stWriteLine
                        If strDimMemArr(1) <> "" Then
                            adoStream.WriteText L1, StreamWriteEnum.stWriteLine
                            strMemArr = Split(strDimMemArr(1), ",")
                            For lngTemp2 = 0 To UBound(strMemArr)
                                adoStream.WriteText R1 & strMemArr(lngTemp2) & R2, StreamWriteEnum.stWriteLine
                            Next lngTemp2
                            adoStream.WriteText L2, StreamWriteEnum.stWriteLine
                        Else
                            adoStream.WriteText L3, StreamWriteEnum.stWriteLine
                        End If
                        adoStream.WriteText P2, StreamWriteEnum.stWriteLine
                    Next lngTemp1
                End If
                adoStream.WriteText C2, StreamWriteEnum.stWriteLine
            Else
                adoStream.WriteText V1, StreamWriteEnum.stWriteLine
                If strValue = "" Then
                    adoStream.WriteText S3, StreamWriteEnum.stWriteLine
                Else
                    adoStream.WriteText S1 & strValue & S2, StreamWriteEnum.stWriteLine
                End If
                adoStream.WriteText V2, StreamWriteEnum.stWriteLine
                adoStream.WriteText A2, StreamWriteEnum.stWriteLine
            End If
            adoStream.WriteText F2, StreamWriteEnum.stWriteLine
        Next lngTemp
        adoStream.WriteText B2, StreamWriteEnum.stWriteLine
    End If
    
    ' Save stream to file and close stream
    adoStream.SaveToFile strAnswerFileName, adSaveCreateOverWrite
    adoStream.Close
    
    ' Run DM package epmDMPackage with answer file strAnswerFileName
    epmDM.RunPackage epmDMPackage, strAnswerFileName
    
    Set epmDMPackage = Nothing
    Set epmDM = Nothing

End Sub

The string array format is described in the code comments at the beginning of the procedure code. The minor disadvantage is that I haven’t been able to delete the answer file after the package launch (file lock). As a result – the user has to periodically delete old answer files (timestamp is included in the file name). More about this issue can be read here: Bug with answer file access for VBA API RunPackage

Sample code to use this procedure:

' Test Sub to run Export Master Data to File DM package
Public Sub TestRunDM()
    Dim strAnsw() As String

' Sample to run Clear DM package    
'    ReDim strAnsw(0 To 3)
'    strAnsw(0) = "%SELECTION%P|DIMENSION:AUDIT_TRAIL|AT_BPC_INPUT|DIMENSION:CATEGORY|FH2|DIMENSION:COMPANY_CODE|CO_NONE|DIMENSION:COORDER|IO_NONE|DIMENSION:COST_CENTER|CC_NONE|DIMENSION:CURRENCY|LC|DIMENSION:FUNCTIONAL_AREA|FA_NONE|DIMENSION:PROFIT_CENTER|PC_NONE|DIMENSION:P_ACCOUNT||DIMENSION:TIME|2013.02,2013.03,2013.04|DIMENSION:TRADING_PARTNER|TP_NONE"
'    strAnsw(1) = "%SELECTION_KEYDATE%V-1"
'    strAnsw(2) = "%ENABLETASK%V1"
'    strAnsw(3) = "%CHECKLCK%V0"
    
'    ExeceuteDM strAnsw, "", "Data Management", "Clear", "/CPMB/CLEAR", "0001"
    
    ReDim strAnsw(0 To 5)
    strAnsw(0) = "%DIMENSIONMEMBERS%D|DIMENSION:TRADING_PARTNER|TP_NONE,TP_000001,TP_999999,TP_XX0098,TP_XX0099,TP_1,TP_CH00,TP_CH02,TP_CZ00,TP_CZ02,TP_DE00,TP_DK00,TP_ES00,TP_FR00,TP_GB00,TP_HK00,TP_HU00,TP_HQ00,TP_IT00,TP_MX00"
    strAnsw(1) = "%DIMENSIONMEMBERS_KEYDATE%V-1"
    strAnsw(2) = "%DIMENSIONMEMBERS_DATEFROM%V"
    strAnsw(3) = "%TRANSFORMATION%V\ROOT\WEBFOLDERS\XXX_Copy_20160620\XXXYTD\DATAMANAGER\TRANSFORMATIONFILES\IMPORT.XLS"
    strAnsw(4) = "%FILE%V\ROOT\WEBFOLDERS\XXX_Copy_20160620\XXXYTD\DATAMANAGER\DATAFILES\EXPORT\testtp.txt"
    strAnsw(5) = "%ADDITIONINFO%V0"
    
    ExeceuteDM strAnsw, "", "Data Management", "Export Master Data to File", "/CPMB/EXPORT_MD_TO_FILE", "0001"

End Sub

The resulting answer file generated by this procedure will be like (DM2017061616172365_Export_Master_Data_to_File.xml):

Export Master Data to File{param_separator}<?xml version="1.0" encoding="utf-16"?>
<ArrayOfAnswerPromptPersistingFormat xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <AnswerPromptPersistingFormat>
    <_ap>
      <Name>%DIMENSIONMEMBERS%</Name>
      <Values>
        <string>TRADING_PARTNER</string>
      </Values>
    </_ap>
    <_apc>
      <StringListPair>
        <str>TRADING_PARTNER</str>
        <lst>
          <string>TP_NONE</string>
          <string>TP_000001</string>
          <string>TP_999999</string>
          <string>TP_XX0098</string>
          <string>TP_XX0099</string>
          <string>TP_1</string>
          <string>TP_CH00</string>
          <string>TP_CH02</string>
          <string>TP_CZ00</string>
          <string>TP_CZ02</string>
          <string>TP_DE00</string>
          <string>TP_DK00</string>
          <string>TP_ES00</string>
          <string>TP_FR00</string>
          <string>TP_GB00</string>
          <string>TP_HK00</string>
          <string>TP_HU00</string>
          <string>TP_HQ00</string>
          <string>TP_IT00</string>
          <string>TP_MX00</string>
        </lst>
      </StringListPair>
    </_apc>
  </AnswerPromptPersistingFormat>
  <AnswerPromptPersistingFormat>
    <_ap>
      <Name>%DIMENSIONMEMBERS_KEYDATE%</Name>
      <Values>
        <string>-1</string>
      </Values>
    </_ap>
  </AnswerPromptPersistingFormat>
  <AnswerPromptPersistingFormat>
    <_ap>
      <Name>%DIMENSIONMEMBERS_DATEFROM%</Name>
      <Values>
        <string />
      </Values>
    </_ap>
  </AnswerPromptPersistingFormat>
  <AnswerPromptPersistingFormat>
    <_ap>
      <Name>%TRANSFORMATION%</Name>
      <Values>
        <string>\ROOT\WEBFOLDERS\XXX_Copy_20160620\XXXYTD\DATAMANAGER\TRANSFORMATIONFILES\IMPORT.XLS</string>
      </Values>
    </_ap>
  </AnswerPromptPersistingFormat>
  <AnswerPromptPersistingFormat>
    <_ap>
      <Name>%FILE%</Name>
      <Values>
        <string>\ROOT\WEBFOLDERS\XXX_Copy_20160620\XXXYTD\DATAMANAGER\DATAFILES\EXPORT\testtp.txt</string>
      </Values>
    </_ap>
  </AnswerPromptPersistingFormat>
  <AnswerPromptPersistingFormat>
    <_ap>
      <Name>%ADDITIONINFO%</Name>
      <Values>
        <string>0</string>
      </Values>
    </_ap>
  </AnswerPromptPersistingFormat>
</ArrayOfAnswerPromptPersistingFormat>

Hope that this procedure will simplify VBA automation of DM packages.

B.R. Vadim

 

References:

How to deal with BPC Data Manager packages programmatically

How to Call a BPC Data Manager Package from VB

Magic Button to create a dimension member from an Input Form

 

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply