Simple VBA procedure to pass parameters to DM pack...

former_member186338 · ‎06-16-2017

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 "<" and ">" by ">"):

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

Simple VBA procedure to pass parameters to DM packages

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z