Skip to Content

Issue useing VBA to call data manager package in BPC 10

Hi Expert,

I’m wondering if anyone can help with this. We are using BPC NETWEAVER V10, EPM 10 SP22, EXCEL 2007.

I try to use visual basic to help user run a DMP directly from Excel input sheet.The following article provided very good step by step instructions.I have followed the steps illustrated and got it to work with one issue where it runs on ALL scoped out categories, rather on the specific category listed in the PARAMETERS range.I have only one Prompt for this DMP and the screen shot provides the setup of the name range.I also included the VBA scripts which I suspected might have some issue.If you can give any comment, that would be great.Thanks!

How to Call BPC Data Manager Package from Visual Basic


Private Sub createAnswerPromptFile(strPackageName As String, strParametersName As String)

'Create a new XML document

Dim objDOM As DOMDocument

Set objDOM = New DOMDocument

'Set the processing instruction of the XML document

Dim objProcessingInstruction As IXMLDOMProcessingInstruction

Set objProcessingInstruction = objDOM.createProcessingInstruction("xml", " version='1.0' encoding='utf-16'")

objDOM.appendChild objProcessingInstruction

'Create root element

Dim objRootElem As IXMLDOMElement

Set objRootElem = objDOM.createElement("ArrayOfAnswerPromptPersistingFormat")

objDOM.appendChild objRootElem

'XSI Attribute

Dim objMemberRel As IXMLDOMAttribute

Set objMemberRel = objDOM.createAttribute("xmlns:xsi")

objMemberRel.NodeValue = ""

objRootElem.setAttributeNode objMemberRel

'XSD Attribute

Set objMemberRel = objDOM.createAttribute("xmlns:xsd")

objMemberRel.NodeValue = ""

objRootElem.setAttributeNode objMemberRel

'Get the range of cells containing the parameters

Dim rngParameters As Range

Set rngParameters = ThisWorkbook.Names(strParametersName).RefersToRange



Dim objCurrentStringPairParent As IXMLDOMElement

'Loop through each row

For i = 1 To rngParameters.Rows.Count

'See which type of parameter is being passed

Select Case rngParameters(i, 2).Value

'If it is a single Parameter, then add a parameter node to the root node

Case "Parameter"

addSingleSelectionParameterToXML rngParameters(i, 1).Value, rngParameters(i, 4).Value, objRootElem, objDOM

'If it is a list of values

Case "StringListPairs"

'If it's a new set of String List Pairs, then create a new parent

If rngParameters(i, 1).Value <> strCurrentStringPair Then

strCurrentStringPair = rngParameters(i, 1).Value

Set objCurrentStringPairParent = getStringListPairParent(rngParameters(i, 1).Value, objRootElem, objDOM)

End If

'Add the Dimension Name and Value to the parent

addStringListPair rngParameters(i, 3).Value, rngParameters(i, 4).Value, objCurrentStringPairParent, objDOM

End Select


'Create the File object

Dim objFile As FileSystemObject

Set objFile = New FileSystemObject

'Create a stream to create and write to the file

Dim objStream As TextStream

Set objStream = objFile.OpenTextFile(strFilename(strPackageName), ForWriting, True)

'Write the name of the DM package first and then the XML output

objStream.WriteLine strPackageDescription(strPackageName) & "{param_separator}" & objDOM.XML

'Close the file


End Sub

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    Hi Benjamin,

    Before moving to the solution given in the document......would first like to know...what is your requirement??? Are you also trying to run Copy Package? Please explain what is it that you are trying to achieve?


  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 16, 2017 at 08:18 AM

    You have to debug yourself VBA code and check the contents of generated answer file!

    Also Prompt is incorrect - %CATEGORY_DIM% is wrong!

    Please provide your advanced DM script.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 16, 2017 at 07:26 AM

    Hi JP,

    We have scripts to be run by DMP to calculate certain expenses. If run by DMP it works fine. Because the users are finance background and probably going through all these steps to run a DMP is likely a bit cumbersome for them, I found the method described in the document is very useful so I follow the steps to run our DMP. I could get the DMP run but the prompt value is not passed.

    Hope this explains.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 16, 2017 at 12:18 PM

    Thanks Vadim!

    The advanced scripts are as following:


    The real name of Type C dimension is Category.

    Add comment
    10|10000 characters needed characters exceeded