How to drag a formula in the entire column in excel


#1

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.


#2

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.


#3

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


#4

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.


#5

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.


#6

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 .


#7

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.


#8

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.