Kyleb91
(Kyle Brown)
September 17, 2021, 10:07am
1
Hey guys!
Basically I have a Workbook with a macro that I am executing, and passing in VAR parameters through Uipath.
In the VB Code I am then setting Values from the workbook to Variables but I can’t seem to pass these out to UiPath from the execute macro activity.
Wondering how do you retrieve output from this activity, as any time I use it, the output is null.
Many Thanks
lakshman
(Ganta lakshman)
September 17, 2021, 10:45am
2
@Kyleb91
Execute Macro activity will give output of type Object .
Is Execute Marco activity executing properly on that excel file ?
1 Like
Kyleb91
(Kyle Brown)
September 17, 2021, 10:51am
3
Hi @lakshman Yes the activity works perfectly and executes the macro in the Workbook, but doesn’t give any output… Should I have something in the VB Code of the Macro to give output?
lakshman
(Ganta lakshman)
September 17, 2021, 10:53am
4
@Kyleb91
Yes it should be there in your Macro code. Then only it will give output else not.
1 Like
Hi @Kyleb91 ,
Can you show me your macro function?
Are you returning value from macro function?
1 Like
Kyleb91
(Kyle Brown)
September 17, 2021, 10:59am
6
Sub CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String)
Range("D19") = VAR1
Range("E19") = VAR2
VAR3 = Range("R19")
VAR4 = Range("L5")
End Sub
Basically I want to Return VAR3 and VAR4 back to UiPath from this Execute Macro Activity, via this macro code.
Kyleb91:
Sub CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String)
Range("D19") = VAR1
Range("E19") = VAR2
VAR3 = Range("R19")
VAR4 = Range("L5")
End Sub
Hi @Kyleb91 ,
Try like this
Sub CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String) As String
Range("D19") = VAR1
Range("E19") = VAR2
VAR3 = Range("R19")
VAR4 = Range("L5")
CRT_Paste = VAR3
End Sub
Try first for Var3
1 Like
prasath_S
(prasath S)
September 17, 2021, 11:13am
8
Hi @Kyleb91
Procedures cannot return a value in VBA, you have to use function for that.
FUNCTION CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String) as string
Range("D19") = VAR1
Range("E19") = VAR2
VAR3 = Range("R19")
VAR4 = Range("L5")
CRT_Paste = VAR3
End function
Thanks
2 Likes
Kyleb91
(Kyle Brown)
September 17, 2021, 11:16am
9
Brilliant, I thought that may have been the issue…
May I ask you how I would call on the value in UiPath form the output object after this? For VAR3
1 Like
prasath_S
(prasath S)
September 17, 2021, 11:24am
10
@Kyleb91 have you create a variable for output in execute macro, and check the value in message box.
Thanks
1 Like
Kyleb91
(Kyle Brown)
September 17, 2021, 11:40am
11
Thanks I have got VAR3, but how do I pass out VAR3 AND Var4?
prasath_S
(prasath S)
September 17, 2021, 11:48am
12
You can put like this in VBA,
FUNCTION CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String) as string
Range("D19") = VAR1
Range("E19") = VAR2
VAR3 = Range("R19")
VAR4 = Range("L5")
CRT_Paste = Var1 & "," & Var2 & "," & Var3 & "," & Var4
End function
And after getting the output from UiPath use split function to split the values by comma,like this, it will give an array of values.
arrVal - arrray of String (system.string )
arrVal = Output.split(","c)
Var1 = arrVal(0)
Var2 = arrVal(1)
Var3 = arrVal(2)
Var4 = arrVal(3)
If you want all 4 values
Thanks
2 Likes
ppr
(Peter)
September 17, 2021, 11:57am
13
Example:
Public Function test() As String
test = "1"
End Function
Function name is test, so with the same name test with value “1” will be returned
Returning multiple values we can do when complex datatypes (e.g. dictionary) is not supported ( and we do not want use arrays) by returning a serialized string e.g. {{“Key1”,“Val1”},{“Key2”,“Val2”}}
Afterwards we can easy bring this again in a dictionary
1 Like
Kyleb91
(Kyle Brown)
September 17, 2021, 12:04pm
14
Thanks! Although the below doesn’t seem to be accepted by VBA, do I need to change the CRT_Paste as String to something else first?
CRT_Paste = Var1 & “,” Var2 & “,” Var3 & “,” Var4
Kyleb91
(Kyle Brown)
September 17, 2021, 12:05pm
15
Hey Peter, thanks again!
Could you show your above code with an example of 2 VARs being passed out? Like the dictionary you say.
Many Thanks
ppr
(Peter)
September 17, 2021, 12:09pm
16
{{“Key1”,“Val1”},{“Key2”,“Val2”}} would be the string
prasath_S
(prasath S)
September 17, 2021, 12:10pm
17
@Kyleb91 try like this, typing in mobile so had some mistakes
FUNCTION CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String) as string
Range("D19") = VAR1
Range("E19") = VAR2
VAR3 = Range("R19")
VAR4 = Range("L5")
CRT_Paste = Var1 & "," & Var2 & "," & Var3 & "," & Var4
End function
Thanks
Kyleb91
(Kyle Brown)
September 17, 2021, 12:35pm
18
Thanks! But…
CRT_Paste = Var1 & “,” & Var2 & “,” & Var3 & “,” & Var4
This still does not work…
prasath_S
(prasath S)
September 17, 2021, 12:37pm
19
@Kyleb91 it should work, I just tested it, do you get any errors.
Thanks