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:
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.
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.
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.
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
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:
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.
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.
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.
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.
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.
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).
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.’.