Simple VBA procedure to pass parameters to DM packages

By admin On Jan 15, 2021

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}" Const B1 As String = "" Const B2 As String = "" Const B3 As String = "" Const F1 As String = " " Const F2 As String = " " Const A1 As String = " <_ap>" Const A2 As String = " " Const N1 As String = " " Const N2 As String = "" Const V1 As String = " " Const V2 As String = " " Const V3 As String = " " Const S1 As String = " " Const S2 As String = "" Const S3 As String = " " Const C1 As String = " <_apc>" Const C2 As String = " " Const P1 As String = " " Const P2 As String = " " Const T1 As String = " " Const T2 As String = "" Const L1 As String = " " Const L2 As String = " " Const L3 As String = " " Const R1 As String = " " Const R2 As 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") & "DocumentsDM" & 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%VROOTWEBFOLDERSXXX_Copy_20160620XXXYTDDATAMANAGERTRANSFORMATIONFILESIMPORT.XLS" strAnsw(4) = "%FILE%VROOTWEBFOLDERSXXX_Copy_20160620XXXYTDDATAMANAGERDATAFILESEXPORT       esttp.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}   <_ap> %DIMENSIONMEMBERS%  TRADING_PARTNER   <_apc>  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      <_ap> %DIMENSIONMEMBERS_KEYDATE%  -1     <_ap> %DIMENSIONMEMBERS_DATEFROM%       <_ap> %TRANSFORMATION%  ROOTWEBFOLDERSXXX_Copy_20160620XXXYTDDATAMANAGERTRANSFORMATIONFILESIMPORT.XLS     <_ap> %FILE%  ROOTWEBFOLDERSXXX_Copy_20160620XXXYTDDATAMANAGERDATAFILESEXPORT esttp.txt     <_ap> %ADDITIONINFO%  0

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" strAnsw(1) = strAnsw(1) & "0CALDAY32017080120990101/PKG/FP_ACCT/PKG/FP_CCN/PKG/FP_COOR/PKG/FP_PCN/PKG/FP_TDP0BAL_FLAG0CALDAY0CALMONTH0CHNGID0CHRT_ACCTS0COMP_CODE0CO_AREA0CURTYPE0FISCPER0FISCPER30FISCVARNT0FISCYEAR0FUNC_AREA0RECORDTP0REQUID0SOURSYSTEM0VERSION" ' Replace "<" by "<" and ">" by ">" strAnsw(1) = Replace(Replace(strAnsw(1), "<", "<"), ">", ">") strAnsw(2) = "%TRANSFORMATION%VROOTWEBFOLDERSENVIRONMENTNAMEMODELNAMEDATAMANAGERTRANSFORMATIONFILESIMPORTTRDATA.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.

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

Would you like to access Full Article?

If you are already a member in this website, Please Click here to loginIf you are not yet a member, Please Click here to Sign up

if you have any questions don’t hesitate to contact us from the Button bellow

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !