topic RE: Run MAcro - How to give the file path dynamic in VBA Code in Product Forum

Run MAcro - How to give the file path dynamic in VBA Code

JananiPR1 — Wed, 02 Dec 2020 11:21:00 GMT

Hello everyone,

I have recorded the excel macro for pivot table exceution. In that macro code, the filepath was also recorded which i am referring the workbook file. But i need a dynamic file path to excute a macro for this pivot table.

Can anyone help me for this macro coding which i need to implement in blueprism

Thanks

------------------------------
Janani PR
------------------------------

RE: Run MAcro - How to give the file path dynamic in VBA Code

NicholasZejdlik — Wed, 02 Dec 2020 13:57:00 GMT

According to the Microsoft docs, you can pass up to 30 arguments to a macro. Blue Prism by default does not pass any parameters into macros, nor does it retrieve any return information.

You could try this code, which should handle a variable number of parameters, although I haven't actually tested it yet. It will also return a value from the macro, if any.

' Inputs: Handle (number), Macro Name (text), Parameters (collection)
' Outputs: Return Value (text)
'
' Package any arguments to pass into a single-row collection.

If Parameters.Columns.Count = 0 Then
	Return_Value = GetInstance(Handle).Run(Macro_Name)
ElseIf Parameters.Rows.Count < 1 Then
	Throw New Exception("Parameters must contain at least 1 row")
ElseIf Parameters.Columns.Count = 1 Then
	Return_Value = GetInstance(Handle).Run(Macro_Name, Parameters.Rows(0))
ElseIf Parameters.Columns.Count = 2 Then
	Return_Value = GetInstance(Handle).Run(Macro_Name, Parameters.Rows(0), Parameters.Rows(1))
ElseIf Parameters.Columns.Count = 3 Then
	Return_Value = GetInstance(Handle).Run(Macro_Name, Parameters.Rows(0), Parameters.Rows(1), Parameters.Rows(2))
ElseIf Parameters.Columns.Count = 4 Then
	Return_Value = GetInstance(Handle).Run(Macro_Name, Parameters.Rows(0), Parameters.Rows(1), Parameters.Rows(2), Parameters.Rows(3))
ElseIf Parameters.Columns.Count = 5 Then
	Return_Value = GetInstance(Handle).Run(Macro_Name, Parameters.Rows(0), Parameters.Rows(1), Parameters.Rows(2), Parameters.Rows(3), Parameters.Rows(4))
Else
	Throw New Exception("Parameters cannot contain more than 5 fields")
End If

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: Run MAcro - How to give the file path dynamic in VBA Code

AndreyKudinov — Wed, 02 Dec 2020 16:01:00 GMT

Also dumb and easy way - make macro read file path from some cell and update it before running macro.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Run MAcro - How to give the file path dynamic in VBA Code

NileshJadhavBP — Wed, 02 Dec 2020 17:54:00 GMT

good one Andrey .this is really nice idea

------------------------------
Nilesh Jadhav
Senior RPA Specialist
ADP
India
------------------------------

RE: Run MAcro - How to give the file path dynamic in VBA Code

sonuiiml — Thu, 03 Dec 2020 22:19:00 GMT

Hi Janani

Well at very first you need to tweak the macro to pass arguments like Macroname(filepath as string). Once that is done one needs to enter the below code stage in excel vbo

GetInstance(Handle).Run(Macro_Name,Param1)

you have to pass 3 inputs

1. handle
2. Macro name
3. Param1 'pass file path as a variable into param1 as input

good luck
------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: Run MAcro - How to give the file path dynamic in VBA Code

JananiPR1 — Fri, 04 Dec 2020 12:42:00 GMT

Thanks Susamy Halder.. Will try this logic

Regards,

Janani PR