How to apply formula in a column and then Write by using LINQ query

How to write by cell in entire column by using linq.
I need to apply this formula in particular column.
“=Mid(F2,2,1)”
and it should apply like F3, F4 likewise to all the row in that particular column.

Hi @ashokkumar_e,

Kindly go through the below link, it will help you

Regards,

I assume you are refer to Excel instead of Datatable.

Refer to the example below. Yo can write formula to one cell then use “Auto Fill Range” to add the formula to other cells in the column.

image

Hi,

Another solution:

First create blank datatable which has single string column using BuildDataTable activity (Let’s say dt)

Next, use following expression using Assign actiivty.

dt = Enumerable.Range(2,100).Select(Function(i) dt.LoadDataRow({"=Mid(F"+i.ToString+",2,1)"},False)).CopyToDataTable()

Note: Range(2,100) means to output formula for row #2-#101.

Then, Write the datatable to warksheet using ExcelApplicationScope and WriteRange

Regards,

in this how can we iterate the row count,cz formula has to be apply as per the value in the other column

While doing copy paste the range it just copy paste the formula as text and original text is not coming up as solution, any other suggestion please

Hi,

Can you elaborate your situation? I suggested to use LINQ expression, ExcelApplicationScope and WriteRange in my previous post.

Regards,

“Auto Fill Range” activity will fill formula to all the cells in the defined range. There is no need to apply iteration for other rows. However, if you want to apply formula to other COLUMNS, you can use iteration.

Another good method suggested by Yoici is using Datatable operation.

With LINQ query I am able to write in new sheet, while doing copy paste the range its just copying the value and desired output is not coming.

Tried choosing only formula option in the copy paste range activity but still it didnt work.
It would be greate if you have any other solution for this please

Hi,

In my environment, i couldn’t reproduce your problem. So, if possible, can you share your workflow and excel worksheet? It’s no problem if dummy data which we can reproduce it.

Regards,

Compliance_Dashboard20220206.xlsx (14.7 KB)
Test.xaml (66.2 KB)
Here is the data

Hi,

Can you try to use not System-File-Workbook-WriteRange but AppIntegration-Excel-WriteRange activity?

Regards,

This time new sheet has only formula not value but when i am doing copy paste the range there is no value those cells filled with this formula =Mid(J2,2,1)

Hi,

There are some WriteRange activity in UiPath.
System-File-Workbook-WriteRange activity writes data as not formula but string.
So, in this case, we need to use AppIntegration-Excel-WriteRange or WriteDataTable to Excel, as the following image.

img20220214-6

Regards,

Yeah i got you and i used this app integration activity , this time in my new sheet it wrote only formula and not as string.

Now only issue is while copy pasting the range it didnt give desired output,any other suggetion/advise is highly appreciated.

Thanks once again for looking in depth on this

Hi,

Can you try AppIntegration-Excel-Proceassing-Copy/Paste Range activity?

Regards,

This only I used mate, still let me try once again and let you know asap

Hi,

I think it might be better to use WriteRange activity to destination area in the worksheet directly. If the above doesn’t work, can you try this way?

Regards,

Got it and appintegration solution worked,this is resolved and thanks a ton mate for all your help.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.