How to execute Macro with arguments

excel
activities
macro

#1

Hello, for the last few hours I’ve been trying to use Execute Macro activity and pass arguments but the macro doesn’t work as expected and I would appreciate some help.

What I want to achieve is to copy a specific row, insert the copied row just below it, and insert a value in a cell of the new row. I’ve created a macro that works fine when running it from excel or when using Execute Macro activity without passing any arguments. But when I pass in a few arguments, even though the arguments are passed in correctly (I can display them inside a message box in excel), the macro doesn’t copy the row and inserts the new value in whatever the current active cell is.

Here’s the VBA code for the macro:

Sub Macro1(intInsertPosition As Integer, strColumnLetter As String, strInsertValue As String)
    Rows(intInsertPosition - 1).Select
    Selection.Copy
    Rows(intInsertPosition).Select
    Selection.Insert Shift:=xlDown
    Range(strColumnLetter + CStr(intInsertPosition)).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = strInsertValue
End Sub

And here’s a screenshot of the Execute Macro activity:

Capture

I have no idea what could be the issue. I have checked these two links to see how to pass arguments: link1 and link2, but since I can display the arguments in excel with a message box I think the passing of the arguments is fine. In these threads 2 other methods of running a macro were mentioned: using a VBScript file with a Start Process activity and using Invoke Code activity but I don’t know if it’s actually possible. If someone could provide an example that would be great.

I am also uploading the .xaml and excel files here: MacroWithArguments.zip (19.4 KB)

Thank you kindly!


How to run macro with argument
#2

For VBscript to work you would need to create a .vbs file in notepad or a VB editor (preferably), then you would need to create an Excel object and open the excel file and also adjust some of the syntax. It’s quite possible but there could be a slight learning curve if you’re not familiar. This scripting method does work better than using .xlsm files in my opinion since you can just double click the file and run it.

As for Executing the Macro, did you say it is passing the Argument correctly from UiPath, where it outputs the variables using “MsgBox intInsertPosition” in the Macro itself but kicked off from Studio? That eliminates that UiPath is the problem.

"the macro doesn’t copy the row and inserts the new value in whatever the current active cell is."
So are you saying the Row is not being selected? You might need to adjust your code, and I also see you use insertPosition instead of intInsertPosition in one line.

What if you use something similar to:

ActiveSheet.Range("A" & Cstr(intInsertPosition-1)).EntireRow.Copy
ActiveSheet.Range("A" & Cstr(intInsertPosition)).EntireRow.Insert Shift:=xlDown

It looks like you are Executing the Macro correctly.

Regards


#3

Could you please provide an example or a link to an example becuse I don’t know how to do this. What do you mean by creating an Excel object?

Yes, so the arguments are passed in as expected.

Yes, the row is not copied and is not inserted below, it simply inserts in the current active cell a string value; this value should be inserted in the newly inserted row in a specific cell.

Sorry about that, I corrected it, that wasn’t the issue.

I tried this but it still doesn’t work as expected.

Have you tried running the robot to see if it works for you? Thank you for your help.


#4

I looked at it and seems some of the Excel features are not working correctly.
.Copy and .Select for example are not working. However, .Value is working.

So this is strange.

If I find a workaround for this I will let you know.


Here is a snippet of one of my .vbs files on a part that creates an Excel object then creates a new workbook
image

As you can see it’s similar to VBA but with a few differences to some syntax; one key difference is you can declare variables in one line. Also, I would recommend using a VB edit tool; one free one is vbsedit. For resources on how to do something like Open or Save for example, there are many answers online that can be found through Google.
https://www.google.com/search?q=vbscript+help&rlz=1C1GGRV_enUS751US751&oq=vbscript+help&aqs=chrome..69i57.4544j0j1&sourceid=chrome&ie=UTF-8

Although, it does require a little bit of knowledge on how loops and if statements can be used, but it’s mostly logic anyway :stuck_out_tongue:

Hope this helps answer your question on VB scripting.

And, I’ll get back to you if I find any answers on the problem with Excel Macro.

Regards.


#5

@florin.stan

Here is an alternative coding for what I think you were trying to do.
Since .Value is working (.Copy and .Select wasn’t working) I used it with these 3 lines:

Rows(intInsertPosition).Insert Shift:=xlDown
Rows(intInsertPosition).Value = Rows(intInsertPosition - 1).Value
Range(strColumnLetter + CStr(intInsertPosition)).FormulaR1C1 = strInsertValue

So, it inserts a blank row, then sets the .Value of the previous row to the inserted row, and replaces the cell on the last line.

Regards


#6

Thanks a bunch for your time, it does what I need! Strange though that it doesn’t work the way I tried, as soon as I add arguments to the Sub procedure, it stops working as expected when executing the macro with UiPath. It doesn’t even matter if I use those arguments inside the procedure. But if I run the macro with arguments from excel it works. Also thank you for the VBScript solution, I will look into it.

EDIT: Actually now I realize I would also want to copy the existing formulas from the copied row which .Copy would have done but I suppose I can insert the formulas myself.


#7

Hmm, .Insert doesn’t work either, I tried inserting a row between two other rows with no luck. It seems there’s not much you can do with macros if you want to pass in arguments from UiPath. Which is frustrating because inserting a new row is a pretty common task. Shouldn’t there be an activity for this? Because using Write Range to re-write thousands of rows is not an option.


#8

Hi,

You might consider LINQ or lambda expressions (in vb.net) to manipulate your table quickly, which you can use in UiPath. I’m sure there are some examples on this forum that will help or you can google on information of the functions.

I’m no expert and don’t have time to go in to it too much but you can basically jump to a row instantly based on conditions using AsEnumerable.Where() then Clone the row and Insert it.
datarow1 = dt.AsEnumerable.Where(Function(row) row(0)=text1 and row(1).Contains(text2)).ToArray(0)
datarow2 = datarow1.Clone
Invoke Insert or Add Row method

You can also alternatively use Output Data Table to store the data to a string with comma delimitter, and use the text like an array where you can edit data instantly.
Not the best example,
String.Join(System.Environment.Newline,text.Split(System.Environment.Newline(0)).Where(Function(row) row(0)=text1).Select(Function(row) row.Split(",“c)(0)+”,"+newvalue+","+row.Split(","c)(2)).ToArray)

Write Text File with .CSV extension
Read CSV to datatable to get back to table

However, for formulas you will need to use Write Cell and a Filldown Ctrl+d hotkey
Write Cell on first cell
Select Range activity of column
TypeInto “[d(ctrl)]d[u(ctrl)]”

I hope this gives you some other ideas and not too complicated.

Regards.


#9

Thank you for your time, @ClaytonM. I am already using LINQ to manipulate the DataTable, the problem is when I have to write back the changes to excel. I basically have to overwrite the whole excel file with data from the manipulated DataTable, which isn’t desirable because of performance and because I would lose formatting/formulas. I also tried to use Invoke on Add Row method and lost the formulas.

I’m not sure how this could be done but ideally there should be a Copy Row activity that would copy all formatting and (relative) formulas and an Insert Row activity that would take the copied row and insert it anywhere in excel (basically what I tried doing with macros). I would then only need to use Write Range to change the values that are different from the previous row. Or it would be great if macros would function properly. For the time being I resorted to a combination of Desktop Recording for inserting rows and write range for modifying only certain consecutive cells from those rows.

Does anyone know why macros aren’t working as expected or how to use them properly (@aksh1yadav @Lucas.Pimenta @andrzej.kniola @ovi @badita @vvaidya @Cosin @acaciomelo)? Thank you for all your help.


#10

Apparently there’s a bug when sending parameters with Execute Macro activity that causes the macro to not function properly. The development team is aware of the bug and is presumably working on it.


#11

S’that a fact? Any place we can look to see details about this defect? I’m having a terrible time trying to get a macro to even run when there are multiple parameters (single parameters work fine).


#12

Yes, you can assign a macro to a button (or other excel controls/menu actions) and pass constant OR variable arguments to it.

In the ‘Assign Macro’ window (right-click on object and select ‘Assign Macro’):

Enclose the macro name in single quotes e.g. to pass 2 constants: 'Button1_Click(“A string!”, 7)'
Select ‘This Workbook’ for the ‘Macros in’ field
If you wish to pass a variable (like the value of a cell), enclose the parameter in Evaluate()
For example, to pass the value of Sheet1!$A$1 to a button function, you would have the following text in the ‘Macro name:’ field:

‘Button1_Click(Evaluate(“Sheet1!$A$1”))’

If you don’t enclose your variable argument with an ‘Evaluate’ function, excel returns the error ‘Formula is too complex to be assigned to an object.’.