Keep calculated column formula intact while reading and writing into excel

Help
,
1

I am writing to read an excel sheet and update a column. But the formulas in the other column disappers. How do I keep the formulas intact even after reading and writing into the excel sheet?

3

If you read an entire excel sheet in Datatable and manipulate it to update something then you will loose the formulas in your excel sheet. However you can still do this by

  1. running for each loop(total rows in excel) and using “read cell” , “write cell” activities in order to update formula of the cell.
  2. In case if you need to update few columns which doesn’t contain formulas then instead of reading entire excel, manipulating it and then writing back to excel, you can use write range activity and specify the range of the columns which you need to update.
1 Like
4

@Priya_Dubey

doing this would take a lot of time for many small segment of rows. because I have a row after every small interval which also has a formula. So, I cannot read the entire column either.

5

You could try and make a duplicate of your excel file. Use Read Range and get the excel file as your datatable. Perform all the necessary logic for this DataTable but keep a record of every row/column change in a Collection. Then simply use a Write Cell Activity in a loop to add all your details from the Collection into your excel copy which should still have your formulas

6

But how do I loop the write cell activity when the order of the common column is different? Could you help me with an xaml file? @PaulKis

7

@MAHESH1 @balupad14 @arivu96

8

bump