Is the Write Value activity the fastest way to write formulas in Excel sheets? I’m asking you this because as far as I know when we are using this activity, we need to iterate between the excel rows in order to fulfill each row with the proper Excel formula, which means that it takes some time if the excel sheet contains too many rows (an average of 1 second per row, to write a new formula on a specific column).
If I use the Write Range activity, is there a way to accept formulas while working with Data Table? I’m asking it, because when we use the Write Range activity and I define a column with a formula, it actually doesn’t respect the formula and it write it as a String. The column will just contains the string from this formula and not the value that was expected from the formula itself.
Here we have a basic example when I’m using the Write Range activity.
I’m just changing the row number from the formula and I know that in this case I could make the calculation right into the UIPath workflow, but imagine the same scenario with more complex formulas. If you have any better ideas, please let us know.
Maybe of those approaches could help us to fulfill the entire column with the same formula, just replacing the row number from it.
In this case, I would just write the formula at the first cell with Write Value activity and then apply one of those approaches following the link examples to replace the remaining rows:
Using Send Hotkey activity to simulate the shortcuts that would help in this case.
Using Mouse Click event (Double Clicking, more prone to errors)
I tried the hotkey method and doesn’t do what we need, in the excel formulas we are implementing we are calling other workbooks (e.g. =VLOOKUP(A3;‘C:\somerute[Another workbook.xlsx]sheet1’!$A$1:$I$4097;9;0)). For some reason it only shows the value of the previos cell in the same workbook and not the one that we are looking for. I made a gif to make it more visual:
With the Datatable method, we tried both “Write Range” activities (Excel App Scope and Workbook) and with the Excel one it throws the error “Range does not exist”, with the Workbook one it paste the formula as a string (based on this).
For the “range does not exist error” I tried this and it does not work at all.
Please we need to solve this issue, your help is much appreciated.
I am trying to write this formula in a write cell inside of excel appl - it does not like the “” within the “” nor does it like : - any suggestions? “=SUMIF(K:K,”<>“,L:L)”
Another suggestion, not sure how practical it is, and will give it a try. How about creating a template Excel file with 2 worksheets, one with the formulas already created e.g. “Results” and the other with just the raw data “Raw Data”. Write the data to “Raw Data”, save the new Excel file with a new name, then the end user just reads “Results”.
To put “” within a string, there are multiple options I read about on this forum.
For me not all of them worked, so maybe try different ones.
Say, you want to print the following text in a message box: welcome"guest"
Options to put in your string:
“Welcome”+”“”“+“Guest”+”“”"
“Welcome”+”“”+“Guest”+“”"
“Welcome”+ Chr$(34) +“Guest”+Chr$(34)
Those are the options I found, for me only option 1 works
I am trying to use your solution but I can’t see where you click before you do the send hotkey. If I just send the hotkey it doesn’t work for me, neither does it if I first click in the general excel area. Could you help me out?
All are great solutions. I just record a macro of me autofilling the formula, then adjust the VBA slightly so it’s dynamic and execute the macro in my workflow.