Fastest way to write an excel formula into an entire column

excel

#1

Hello everyone,

  1. 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).

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

Thanks and Regards,
Acacio.


Making faster the activity "Write value"
Write Cell - Formula Not Calculating
How to write data into excel in parallel
How to write a formula in csv cell?
Uiautomation
#3

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:

  1. Using Send Hotkey activity to simulate the shortcuts that would help in this case.
  2. Using Mouse Click event (Double Clicking, more prone to errors)

For example:

Nevertheless, I’m still analyzing what would be the fastest way to set formulas properly to the whole column.


Excel Vlookup Using UiPAth
#4

Example of a temporary solution using the Send Hotkey activity.

Fill Excel Column with Formula - Shortcuts.zip (7.8 KB)


#5

Is there any definitive solution to this?


#6

I think your definitive solution is to use hotkey automation with the above approach.

Ctrl+D is equal to dragging down the formula for entire selected cells.


#7

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.

@vvaidya @acaciomelo


Insert excel formulas from Datatable into Excel as formulas and not as strings
#8

Hi @nicolas.alejo,

There is one more thing that you may try:

  1. Get the total records of your sheet
  2. Iterate between them using a counter starting with 0 or 1
  3. Use the Write Cell activity to write your formula into the cell
  4. Replace the semicolons in your formula with commas because UiPath doesn’t work well if the string formula contains semicolons (";")

It’s going be pretty much slower than using the hotkey strategy, but it will work as well.


#9

Hey @acaciomelo learned many things from your posts man.try to be more active and enlighten people.


#10

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


#11

hey there. Three quotes I believe will get you embedded quotes that you need.
For example,

“=SUMIF(K:K,""”<>""",L:L)"

Also, if you want to Fill Down, may I suggest using the Ctrl+d key combination.
For example,
Select Range
Type Into "[d(ctrl)]d[u(ctrl)]

‘d’ for down and ‘u’ for up.
Hope that answers your issues!


#12

Worked - Perfect! Don’t need to copy down but will keep this information for when I do. Thanks!


#13

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


#14

I want my robot to write my velue in excel cell from A2 but it is writing from A20. Any Help?


#15

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:

  1. “Welcome”+”"""+“Guest”+""""
  2. “Welcome”+”""+“Guest”+"""
  3. “Welcome”+ Chr$(34) +“Guest”+Chr$(34)

Those are the options I found, for me only option 1 works


#16

Hi @acaciomelo,

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?

Thanks!


#17

Somehow the hotkey now does work when I first click the general excel worksheet area. So, don’t know how or why, but problem is solved :slight_smile:


#18

i want to use the hotkey methode but the is always problem with selector, because the excel file appear and desappear in few second


#19

Perhaps this one doesn’t work for you as I don’t think you need the $. Chr(34) works perfectly for me.


#20

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.


#21

hi @vvaidya,

do you have any solution on it?