Skip to Content
Technical Articles
Author's profile photo Vadim Kalinin

Simple VBA procedure to pass parameters to DM packages

New! After EPM SP 29 Patch 01 the new subroutine “DataManagerAdvancedRunPackage” was introduced to run DM packages with answer file. At the end of this blog I will provide simplified code using  this sub. Updated: Universal code with late binding for EPM or AO is added to the code after EPM SP 29 Patch 01.

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>

Sample code to use this procedure to launch Load Transaction Data from BW InfoProvider UI (xml SELECTION is passed as xml text with replacement of “<” by “&lt;” and “>” by “&gt;”):

Public Sub TestRunDM()
    Dim strAnsw() As String

' Sample to run Load Transaction Data from BW InfoProvider UI DM package
    ReDim strAnsw(0 To 5)
    strAnsw(0) = "%InforProvide%V/XXX/ZZZZ" 'Infoprovider name
    ' Concatenating xml line (too long) all " to be replaced by ""
    strAnsw(1) = "%SELECTION%V<?xml version=""1.0"" encoding=""utf-16""?><Selections xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">"
    strAnsw(1) = strAnsw(1) & "<Selection Type=""Selection""><Attribute><ID>0CALDAY</ID><Operator>3</Operator><LowValue>20170801</LowValue><HighValue>20990101</HighValue></Attribute></Selection><Selection Type=""FieldList""><FieldID>/PKG/FP_ACCT</FieldID><FieldID>/PKG/FP_CCN</FieldID><FieldID>/PKG/FP_COOR</FieldID><FieldID>/PKG/FP_PCN</FieldID><FieldID>/PKG/FP_TDP</FieldID><FieldID>0BAL_FLAG</FieldID><FieldID>0CALDAY</FieldID><FieldID>0CALMONTH</FieldID><FieldID>0CHNGID</FieldID><FieldID>0CHRT_ACCTS</FieldID><FieldID>0COMP_CODE</FieldID><FieldID>0CO_AREA</FieldID><FieldID>0CURTYPE</FieldID><FieldID>0FISCPER</FieldID><FieldID>0FISCPER3</FieldID><FieldID>0FISCVARNT</FieldID><FieldID>0FISCYEAR</FieldID><FieldID>0FUNC_AREA</FieldID><FieldID>0RECORDTP</FieldID><FieldID>0REQUID</FieldID><FieldID>0SOURSYSTEM</FieldID><FieldID>0VERSION</FieldID></Selection></Selections>"
    ' Replace "<" by "&lt;" and ">" by "&gt;"
    strAnsw(1) = Replace(Replace(strAnsw(1), "<", "&lt;"), ">", "&gt;")
    strAnsw(2) = "%TRANSFORMATION%V\ROOT\WEBFOLDERS\ENVIRONMENTNAME\MODELNAME\DATAMANAGER\TRANSFORMATIONFILES\IMPORTTRDATA.XLS"
    strAnsw(3) = "%TARGETMODE%V2"
    strAnsw(4) = "%RUNLOGIC%V0"
    strAnsw(5) = "%CHECKLCK%V0"
    
    ExeceuteDM strAnsw, "", "Data Management", "Load Transaction Data from BW InfoProvider UI", "/CPMB/LOAD_INFOPROV_UI", "0001"

End Sub

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

New code using “DataManagerAdvancedRunPackage”

The answer file can be generated manually using another new sub “DataManagerAdvancedCreateLocalResponseFile”, but I prefer to generate this file myself.

Due to the fact that it’s not necessary to use FPMXLClient.ADMPackage FPMXLClient can be referenced with late binding for AO compatibility.

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, strPackageDesc 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"
' strPackageDesc - Package Description like "Clear Transaction Data"
' 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 objAddIn As COMAddIn
    Dim epm As Object
    Dim AOComAdd As Object
    Dim blnEPMInstalled As Boolean

    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

    'Universal code to get FPMXLClient for standalone EPM or AO
    For Each objAddIn In Application.COMAddIns
        If objAddIn.progID = "FPMXLClient.Connect" Then
            Set epm = objAddIn.Object
            blnEPMInstalled = True
            Exit For
        ElseIf objAddIn.progID = "SapExcelAddIn" Then
            Set AOComAdd = objAddIn.Object
            Set epm = AOComAdd.GetPlugin("com.sap.epm.FPMXLClient")
            blnEPMInstalled = True
            Exit For
        End If
    Next objAddIn
    
    If Not blnEPMInstalled Then
        MsgBox "EPM is not installed!"
        Exit Sub
    End If
  
    ' 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
    Set adoStream = Nothing
    
    ' Run DM package with answer file strAnswerFileName
    
    epm.DataManagerAdvancedRunPackage strChain, strPackageCroup, strPackageDesc, strPackage, "Process Chain", strTeam, strUserGroup, strAnswerFileName
    
    Set epm = Nothing
    
End Sub

Sample code to use this procedure:

Public Sub TestRunDM()
    Dim strAnsw() As String
    
'    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", "Clear Transaction Data", "/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\testtp1.txt"
    strAnsw(5) = "%ADDITIONINFO%V0"
    
    ExeceuteDM strAnsw, "", "Data Management", "Export Master Data to File", "Export Master Data to File Service", "/CPMB/EXPORT_MD_TO_FILE", "0001"

End Sub

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

 

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Hjiaj Karim
      Hjiaj Karim

      HI Vadim,

      I am following your work since 3 years now, and i would like to thank you for all your help.

      I need some help regarding the question below, can you please have a look and let me know if you have any idea on the resolution. Thank you in advance,

      https://answers.sap.com/questions/239652/bpc-script-logic-select-time-set-parameter-empty.html

      Author's profile photo Paul Joia
      Paul Joia

      Hello Vadim,

      Thanks for the post.

      Is it possible to execute a DataManager PackageLink from VBA and pass the prompt values?

      If it is, would you mind explaining how?

      Thank you in advance.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Unfortunately not possible!

       

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Theoretically you can create a custom logic badi based on the code of UJD_TEST_PACKAGE_LINK and pass answer prompt to this badi...

      Author's profile photo Iago Conde
      Iago Conde

      Hi Vadim,

       

      Tried to replicate this process picking up values from an input form and using the move package, but I don't seem to get it to work. I'm on SP31 so went with the second option, and just edited the second sub:

      Public Sub TestRunDM()
      Dim strAnsw() As String
      Dim MyString1 As String

      MyString1 = Range("E8").Value
      Dim MyString2 As String

      MyString2 = Range("E11").Value
      Dim MyString3, MyString4 As String

      MyString3 = "%SELECTION%P|DIMENSION:ACCOUNT_LAB|" & MyString1
      MyString4 = "%TOSELECTION%P|DIMENSION:ACCOUNT_LAB|" & MyString2
      ReDim strAnsw(0 To 4)
      strAnsw(0) = MyString3
      strAnsw(1) = MyString4
      strAnsw(2) = "%DIMENSIONMEMBERS_KEYDATE%V-1"
      strAnsw(3) = "%DIMENSIONMEMBERS_DATEFROM%V"
      strAnsw(4) = "%ADDITIONINFO%V0"

      ExeceuteDM strAnsw, "", "User Packages", "Move", "Move", "/CPMB/MOVE", "0001"

      End Sub

       

      Any help?

       

      Thanks,

      Iago

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Strange idea to provide code without providing contents of E8 and E11!

       

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Tested my code with Move package:

      Public Sub TestRunDM()
          Dim strAnsw() As String
          
          ReDim strAnsw(0 To 4)
          strAnsw(0) = "%SELECTION%P|DIMENSION:AUDIT_TRAIL|AT_BPC_INPUT|DIMENSION:CATEGORY|BUD|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|A_300000|DIMENSION:TIME|2013.02|DIMENSION:TRADING_PARTNER|TP_NONE"
          strAnsw(1) = "%TOSELECTION%P|DIMENSION:AUDIT_TRAIL|AT_BPC_INPUT|DIMENSION:CATEGORY|BUD|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|A_300000|DIMENSION:TIME|2013.03|DIMENSION:TRADING_PARTNER|TP_NONE"
          strAnsw(2) = "%SELECTION_KEYDATE%V-1"
          strAnsw(3) = "%TARGETMODE%V0"
          strAnsw(4) = "%CHECKLCK%V1"
          
          ExeceuteDM strAnsw, "", "Data Management", "Move", "Move Transaction Data", "/CPMB/MOVE", "0001"
      End Sub

      Working absolutely fine!

      P.S. Please be accurate creating strings and running subroutine. A lot of errors.

       

      Author's profile photo Rene Moreno
      Rene Moreno

      Hi Vadim,

      Is it possible to use this code for BPC Microsoft? I've been trying to run a package that runs a Script Logic file but seems like BPC Microsoft does not use the xml file.

      This is my selection:

      ReDim strAnsw(0)
      strAnsw(0) = "%SELECTIONFILE%P|DIMENSION:ENTITY|1112|DIMENSION:DEPARTMENT|10,15"

      This is the detail of the package:

      'DEBUG(ON)

      PROMPT(SELECTINPUT,,,"Please select Entity and Department: ","%ENTITY_DIM%,DEPARTMENT")
      TASK(Execute formulas,USER,%USER%)
      TASK(Execute formulas,Environment,%Environment%)
      TASK(Execute formulas,Model,%Model%)
      TASK(Execute formulas,SELECTION,%SELECTIONFILE%)
      TASK(Execute formulas,LOGICFILE,%ModelPath%\..\AdminApp\%Model%\Test_Logic.lgf)
      TASK(Execute formulas,RUNMODE,1)
      TASK(Execute formulas,LOGICMODE,1)
      TASK(Execute formulas,CHECKLCK,0)
      'TASK(Execute formulas,CHECKLCK,%CHECKLCK%)

      Thank you in advance.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      BPC MS use different syntax in advanced script like:

      TASK(Execute formulas,FORMULASCRIPT, *FUNCTION VARNAME=%VARNAME%)

      No idea, how to work with it.

      Author's profile photo Javier Pérez Herrero
      Javier Pérez Herrero

      Hi Vadim,

      We are trying to use this method in order to load data from other infoprovider, but we are getting the following error when ExeceuteDM is called.

      VBA%20Error

      VBA Error

       

      We are testing it with the following code:

      Public Sub TestRunDM()
          Dim strAnsw() As String
      
      ' Sample to run Load Transaction Data from BW InfoProvider UI DM package
          ReDim strAnsw(0 To 5)
          strAnsw(0) = "%InforProvide%/CPMB/S8IADIG" 'Infoprovider name
          ' Concatenating xml line (too long) all " to be replaced by ""
          strAnsw(1) = "%SELECTION%V<?xml version=""1.0"" encoding=""utf-16""?><Selections xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">"
          strAnsw(1) = strAnsw(1) & "<Selection Type=""Selection""><Attribute><ID>/CPMB/S8DGHBP</ID><Operator>1</Operator><LowValue>G0ARA</LowValue><HighValue /></Attribute></Selection><Selection Type=""FieldList""><FieldID>/CPMB/S8D1VZ4</FieldID><FieldID>/CPMB/S8D525T</FieldID><FieldID>/CPMB/S8DGHBP</FieldID><FieldID>/CPMB/S8DMCCJ</FieldID><FieldID>/CPMB/S8DP1QJ</FieldID><FieldID>/CPMB/S8DWQ6P</FieldID><FieldID>/CPMB/S8DXGV3</FieldID><FieldID>/CPMB/S8DY6CO</FieldID><FieldID>/CPMB/S8DZ8MY</FieldID></Selection></Selections>"    ' Replace "<" by "&lt;" and ">" by "&gt;"
          strAnsw(1) = Replace(Replace(strAnsw(1), "<", "&lt;"), ">", "&gt;")
          strAnsw(2) = "%TRANSFORMATION%V\ROOT\WEBFOLDERS\APP_PRESUP_FORCAST\FINANCE_BUDGET\DATAMANAGER\TRANSFORMATIONFILES\CONSOL_INFO.xls"
          strAnsw(3) = "%TARGETMODE%V0"
          strAnsw(4) = "%RUNLOGIC%V0"
          strAnsw(5) = "%CHECKLCK%V0"
          
          ExeceuteDM strAnsw, "", "Cargas", "Cargar Datos Balance", "/CPMB/LOAD_INFOPROV_UI", "0001"
      
      End Sub

       

      Could you help us?

       

      Thanks,

      Javi

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Sorry, but I am unable to understand what do you want to achieve!

      But I see error here:

      strAnsw(0) = "%InforProvide%/CPMB/S8IADIG" 'Infoprovider name

      Have to be

      strAnsw(0) = "%InforProvide%V/CPMB/S8IADIG" 'Infoprovider name

      V have to be between variable with %xxx% and the value

      Author's profile photo Javier Pérez Herrero
      Javier Pérez Herrero

      Thank you very much!! That was the problem. It works now.