How to drag a formula in the entire column in excel

Hey, I have a problem, I want to drag a formula in the entire column in excel, but I don’t know how to realize that action in UiPath if someone can help me, will be a big help

Thanks.

Hello,

The shortcut to “Filldown” is Ctrl+D, so if you can select the range you want the formula to drag down to, then just use TypeInto.

For example,
Write Cell "formula"
Select Range
TypeInto "[d(ctrl)]d[u(ctrl)]"

‘d’ for down and ‘u’ for up to simulate the hotkey. (You might be able to use Send Hotkey too.

Selecting the range will require that you know the number of rows in your table. You can use dtVar.Rows.Count to find that number, then use it inside the Range field, like
"A2:A"+(dtVar.Rows.Count+1).ToString

Hope that gets you in the right direction.

4 Likes

Well, I only have problem with the range, thanks Clayton

Hi,
I need small help,Suppose i have blank excel file and saved it.In that file we have to create 15000 of re cords,Suppose we enter 1 and 2 by manually we will drag that two rows to down and we can generate 15000 records.So by automating can we get that result.

Hi @karthik89

I have trouble understanding your problem, but here’s an ideal way to create your records using a data table. (assuming it’s not too slow)

psuedocode:

If System.IO.File.Exists(filepath)
    Excel Read Range // to get existing records in the file
Else Build Data Table // to create new table with headers

For each item In itemlist // loop through list of items to be added to table
    Add Data Row // use ArrayRow property to place each column in the correct position

Excel Write Range

If there is no list of items that you can loop for the For each, then alternatively you can use a Do While with a counter (possibly surrounded by a Try/Catch if you don’t know the end number)

Try
  Do
      Add Data Row
      itemIndex=itemIndex+1
  While True
Catch

If you have already a structured table of the 15000 records stored in a data table variable, then you can make it faster by not looping and rather instead just using the Merge Data Table. However, you need to manipulate the column names to match your new table.

That solution would look like this:

If System.IO.File.Exists(filepath)
    Excel Read Range // to get existing records in the file
    Merge Data Table // merge records to new table variable
Else Assign dtNew = dtSource // store records to new table variable

Excel Write Range

However, if you need to change values in each row, then you will need to loop through each record regardless.

I hope any of this is helpful.

Regards.

1 Like

Thanks for the information.For range which value should i have to be mentioned.Because i need 15000 of records by using 1st and 2nd record dragging to the bottom.For that can you send any class file for the reference.it would be more useful for me.Please give a suggestion for me .

Hi,

Can I know how can we take range value,Suppose i Need 15000 records in excel by using serial number column, i need 15000 records .What type of activity can be used and how can we generate in the excel sheet.

hi can you pls send me the class file.Actually i am trying to drag Sr.No records up to 15000 by using automation.But am not able to execute.Could you please help.

Dear @Clayton: could you please further explain the type into actvity? we have to indicate where we type the value of “[d(ctrl)]d[u(ctrl)]” but you did not explain this how to do let the bot knows that we want it to type this to specified cell.

Thank you for your help.

Hi @Nazarius

You will want to use rctrl or lctrl, because ctrl has been known to get stuck down.

You just need to use the selector of the excel window so it types in that specific window. Let’s say your filename is “file1.xlsx”. A selector for that window will generally look like "<wnd app='excel.exe' cls='XLMAIN' title='Microsoft Excel - file1' /> or with the extension "<wnd app='excel.exe' cls='XLMAIN' title='Microsoft Excel - file1.xlsx' />, and sometimes it won’t even have “Microsoft Excel” in the title; this depends on the environment configuration from my understanding.

So, let’s assuming you have a full filepath to this excel file stored in some string variable like filePath. If we edit this selector as an expression by clicking in the Property ‘Selector’ (while not editing in the Selector editor), the selector will be surrounded by double quotations, which means you can concatenate and manipulate the string. - this breaks the selector though so no more Selector editor and Validation

Using GetFilenamewithoutextension(), we can use this variable for a dynamic selector:
"<wnd app='excel.exe' cls='XLMAIN' title='*"+System.IO.Path.GetFileNameWithoutExtension(filePath)+"*' />"

Therefore, you can use that in the selector of your Type Intos. It might make sense to store this in either a string or element variable at some point before you interact with Excel, so you can reuse it in either the element or selector property box.

Finally, just place "[d(rctrl)]d[u(rctrl)]" in your Type Into
and that will perform the keystroke in your Excel window.

But before this step, you must also select the range of cells you wish to perform the ‘Fill Down’ on. You should use the Select Range activity for this. To find the range, this will require some calculations such as the last cell in the range and optionally the column letter. If you have a Data Table, you can simply get the last cell by doing dt1.Rows.Count+1. The ‘+1’ is because the first row would be the header in most cases. To find the letter if it will only ever be within A-Z, you can just convert the numerical value (column index) to a character by doing this: Convert.ToChar(dt1.Columns.IndexOf("columnname")+65). The ‘+65’ is because normally you would do 64 (64 is the first letter ‘A’) but you need +1 cause the index starts on 0.

So, the Select Range will end up having a range similar to this: firstCell+":"+Convert.ToChar(dt1.Columns.IndexOf("columnname")+65).ToString+(dt1.Rows.Count+1).ToString

I hope all this makes sense.

Regards.

1 Like

Dear Clayton,

Thank you very much for your throrough explanation, i really appreciate the time that you take to help me. Im going to do your method now, and if it works, i will let you know.

Regards,

Nazarius

Hi @NITHIN_CH

You can use Excel’s FillDown feature.
You can store the formulas to variables that would be placed in the first cell of the range (like S2 or U2), then select the range in that column you want to fill down and perform the fill down. If the formula is correct with relative references, then the formula will be calculated correctly for each cell.

Look over my previous post where I explain this in better detail using the Select Range and FillDown keystroke (ctrl+d)

You may also opt to use the FillDown feature in VBA using the Invoke VBA activity with the code in a .vbs file.

If you choose not to use Excel’s FillDown feature, then the other other option is to run the rows through a loop and use the index property as the incremented counter that calculates the formula, then Assign each calculated formula to the column in the row using basic uipath knowledge. - I don’t recommend this method, though.

Regards.

you could write this equation in a for each row loop, it will only apply it to data rows not the whole sheet