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

[JananiPR1]

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
------------------------------

[NicholasZejdlik]

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
------------------------------

[AndreyKudinov]

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
------------------------------

[NileshJadhavBP]

good one Andrey .this is really nice idea

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

[sonuiiml]

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
------------------------------

[JananiPR1]

Thanks Susamy Halder.. Will try this logic

Regards,

Janani PR