Passing parameters dynamically to run a DM with VBA/Macros - VBA not working

[maleodillet]

Hi Gurus,

I have been doing extensive research on how to pass parameters dynamically to Run a Data Manager Package. However, even when I have followed all the instructions clearly explained on this document http://scn.sap.com/docs/DOC-32636 my VBA code keeps throwing an error "Run-time error 52: Bad file name or number" and it points out to this lines of code;

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

has any of you experienced such error and if so how did you go about debugging it?

here is the complete VBA code in case you'd like to see it;

Public Sub executeDmPackageWithParameters()
' Create the Answer Prompt file in the location
' specified in the Name Range "PACKAGE"
 createAnswerPromptFile "PACKAGE", "PARAMETERS"


' Get the DM Automation class instance
 Dim objDMautomation As EPMAddInDMAutomation
 Set objDMautomation = New EPMAddInDMAutomation
' Run the package specified in Excel Name Range "PACKAGE",
' using the promtp file specified in Name Range "PACKAGE"
 objDMautomation.RunPackage objPackageFromSheet("PACKAGE"), _
 strFilename("PACKAGE")
End Sub
Private Function strFilename(strRange As String) As String
' Get the range in which the DM package paramteres is set
 Dim rngPackageRange As Range
 Set rngPackageRange = Application.Names(strRange).RefersToRange




' Loop through the rows
 For i = 1 To rngPackageRange.Rows.Count
 If rngPackageRange(i, 1).Value = "PromptFile" Then
 strFilename = rngPackageRange(i, 2).Value
 Exit Function
 End If
 Next
End Function
Private Function strPackageDescription(strRange As String) As String
' Get the range in which the DM package paramteres is set
 Dim rngPackageRange As Range
 Set rngPackageRange = Application.Names(strRange).RefersToRange
' Loop through the rows
 For i = 1 To rngPackageRange.Rows.Count
 If rngPackageRange(i, 1).Value = "PackageId" Then
 strPackageDescription = rngPackageRange(i, 2).Value
 Exit Function
 End If
 Next
End Function
Private Function objPackageFromSheet(strRange As String) As ADMPackage
' Get the range in which the DM package paramteres is set
 Dim rngPackageRange As Range
 Set rngPackageRange = Application.Names(strRange).RefersToRange


' Create the ADM Package object
 Set objPackageFromSheet = New ADMPackage


' Loop through the rows
 For i = 1 To rngPackageRange.Rows.Count


 Select Case rngPackageRange(i, 1).Value


 Case "Filename"
 objPackageFromSheet.Filename = rngPackageRange(i, 2).Value


 Case "GroupId"
 objPackageFromSheet.groupId = rngPackageRange(i, 2).Value


 Case "PackageDesc"
 objPackageFromSheet.packageDesc = rngPackageRange(i, 2).Value


 Case "PackageId"
 objPackageFromSheet.packageId = rngPackageRange(i, 2).Value


 Case "PackageType"
 objPackageFromSheet.packageType = rngPackageRange(i, 2).Value


 Case "TeamId"
 objPackageFromSheet.teamId = rngPackageRange(i, 2).Value


 Case "UserGroup"
 objPackageFromSheet.UserGroup = rngPackageRange(i, 2).Value


 End Select
  Next
End Function
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 = "http://www.w3.org/2001/XMLSchema-instance"
 objRootElem.setAttributeNode objMemberRel


' XSD Attribute
 Set objMemberRel = objDOM.createAttribute("xmlns:xsd")
 objMemberRel.NodeValue = "http://www.w3.org/2001/XMLSchema"
 objRootElem.setAttributeNode objMemberRel




' Get the range of cells containing the parameters
 Dim rngParameters As Range
 Set rngParameters = ThisWorkbook.Names(strParametersName).RefersToRange
 'Excel.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
 Next


' 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
 objStream.Close
End Sub
Private Function addStringListPair(strVariableName As String, _
 strValue As String, _
objParent As IXMLDOMElement, _
objDOM As DOMDocument)
' Create the "StringListPair" node
 Dim objStringListPairElement As IXMLDOMElement
 Set objStringListPairElement = _
 objDOM.createElement("StringListPair")
 objParent.appendChild objStringListPairElement




' Create the "Str" element containing the variable name
 Dim objStrElement As IXMLDOMElement
 Set objStrElement = objDOM.createElement("str")
 objStringListPairElement.appendChild objStrElement
 objStrElement.Text = strVariableName


' Create the "lst" element
 Dim objLstElement As IXMLDOMElement
 Set objLstElement = objDOM.createElement("lst")
 objStringListPairElement.appendChild objLstElement


' Create the "string" element containing the variable value
 Dim objStringElement As IXMLDOMElement
 Set objStringElement = objDOM.createElement("string")
 objLstElement.appendChild objStringElement
 objStringElement.Text = strValue
End Function
Private Function getStringListPairParent(strVariableName As String, _
 objParent As IXMLDOMElement, _
objDOM As DOMDocument) As IXMLDOMElement
' Create the "AnswerPromptPersistingFormat" node
 Dim objAnswerPromptPersistingFormatElement As IXMLDOMElement
 Set objAnswerPromptPersistingFormatElement = _
 objDOM.createElement("AnswerPromptPersistingFormat")
 objParent.appendChild objAnswerPromptPersistingFormatElement


' Create the "_ap" node
 Dim objApElement As IXMLDOMElement
 Set objApElement = objDOM.createElement("_ap")
 objAnswerPromptPersistingFormatElement.appendChild objApElement


' Create the parameter name element
 Dim objParameterElement As IXMLDOMElement
 Set objParameterElement = objDOM.createElement("Name")
 objApElement.appendChild objParameterElement
 objParameterElement.Text = strVariableName


' Create the values element
 Dim objValuesElement As IXMLDOMElement
 Set objValuesElement = objDOM.createElement("Values")
 objApElement.appendChild objValuesElement


' Create the "_apc" node
 Set getStringListPairParent = objDOM.createElement("_apc")
 objAnswerPromptPersistingFormatElement.appendChild getStringListPairParent
End Function
Private Function addSingleSelectionParameterToXML(strVariableName As String, _
 strValue As String, _
 objParent As IXMLDOMElement, _
objDOM As DOMDocument)
' Create the "AnswerPromptPersistingFormat" node
 Dim objAnswerPromptPersistingFormatElement As IXMLDOMElement
 Set objAnswerPromptPersistingFormatElement = _
 objDOM.createElement("AnswerPromptPersistingFormat")
 objParent.appendChild objAnswerPromptPersistingFormatElement


' Create the "_ap" node
 Dim objApElement As IXMLDOMElement
 Set objApElement = objDOM.createElement("_ap")
 objAnswerPromptPersistingFormatElement.appendChild objApElement
' Create the parameter name element
 Dim objParameterElement As IXMLDOMElement
 Set objParameterElement = objDOM.createElement("Name")
 objApElement.appendChild objParameterElement
 objParameterElement.Text = strVariableName


' Create the values element
 Dim objValuesElement As IXMLDOMElement
 Set objValuesElement = objDOM.createElement("Values")
 objApElement.appendChild objValuesElement


' Create the string element with the value passed to the parameter
 Dim objStringElement As IXMLDOMElement
 Set objStringElement = objDOM.createElement("string")
 objValuesElement.appendChild objStringElement
 objStringElement.Text = strValue




End Function

and I am trying to pass parameters to run this DM;

if you'd like i could also provide the list of Prompts and my Name Ranges for "Parameters" and "Package". Thanks in advance.

[former_member186338]

P.S.

VBA code keeps throwing an error "Run-time error 52: Bad file name or number" and it points out to this lines of code;

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

Looks like you forgot to include ALL required references listed at the beginning of page 17 of this document:

http://scn.sap.com/docs/DOC-32636

Microsoft Scripting Runtime Etc...

But I don't recommend to use this document - it's an overcomplicated sample with a lot of useless fields in Excel.

[former_member186338]

Use my code: https://blogs.sap.com/2017/06/16/simple-vba-function-to-pass-parameters-to-dm-packages/

It's working!

[maleodillet]

Hi Vadim,

I have deleted all previous modules and created just one with your code, but I am a bit unclear on how to create the macros to execute your code could you please point in the right direction?

[maleodillet]

Also with your code we will still use the name ranges PARAMETERS and PACKAGE? or do I have to hard ode that info within the VBA you provided?

[former_member186338]

I have provided examples in my blog. You have to call the procedure with parameters described.

[former_member186338]

No, named ranges are not required. You can read values from Excel cells to get parameters values or hardcore some parameters in the code.