Technical Articles
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 SubThe 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 SubThe 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 "<" and ">" by ">"
strAnsw(1) = Replace(Replace(strAnsw(1), "<", "<"), ">", ">")
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 SubSample 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 SubB.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
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
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.
Unfortunately not possible!
Theoretically you can create a custom logic badi based on the code of UJD_TEST_PACKAGE_LINK and pass answer prompt to this badi...
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
Strange idea to provide code without providing contents of E8 and E11!
Tested my code with Move package:
Working absolutely fine!
P.S. Please be accurate creating strings and running subroutine. A lot of errors.
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:
This is the detail of the package:
Thank you in advance.
BPC MS use different syntax in advanced script like:
TASK(Execute formulas,FORMULASCRIPT, *FUNCTION VARNAME=%VARNAME%)
No idea, how to work with it.
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 Error
We are testing it with the following code:
Could you help us?
Thanks,
Javi
Sorry, but I am unable to understand what do you want to achieve!
But I see error here:
Have to be
V have to be between variable with %xxx% and the value
Thank you very much!! That was the problem. It works now.