Hi Team,
I am using Excel macros in VBA and currently have many hardcoded values (file path, sheet name, etc.).
How can I use parameters to pass values dynamically in a macro instead of hardcoding?
Thank You!
- Define Workflow Parameters
First, you must create the parameters within the Workflow Properties of Process Studio.
Go to the Parameters tab in your workflow properties.
Define names such as filename, filePath, sheetName, or macroName.
Assign them default values or let them be populated dynamically at runtime.
- Access Parameters in “Execute VB Script”
Inside your Execute VB Script step, you can retrieve these workflow parameters using the following syntax:
VBScript
’ Declare variables to hold the parameter values
Dim filePath, sheetName, macroName
’ Retrieve values from AutomationEdge Workflow Parameters
filePath = “${filePath}”
sheetName = “${sheetName}”
macroName = “${macroName}”
’ Now use these variables in your Excel automation logic
’ Example:
’ objExcel.Workbooks.Open(filePath)
3. Pass Parameters to the Macro
If you are calling a specific sub-routine within your Excel file that requires arguments, use the Application.Run method:
VBScript
’ Calling a macro named “ProcessData” and passing the sheet name as an argument
objExcel.Run “ProcessData”, sheetName
