Excel / copy paste and losing formula

Help Studio
, , , ,
1

Hello everyone !

In a process where I have to write datas in an existing Excel file containing formulas, I use several write cells (I’m in a for each row, and dependings of the datas I don’t have to write in every columns).
So, it’s working fine, but it takes a lot of time.

To make it quicker, I would like to go with an add data row and write every lines at the end of the process.

But in the Excel file, there is forumlas in F,G and H. Whenever I write something in D, it triggers them.
image

The problem is that when I try to do it with the add data rows and the following array:
image

(The cells with formulas are: “”)

It delets the formulas, resulting in:

image
image702×138 1.72 KB

I tried to put the formula instead of the “” but the result is:

image
image717×162 3.11 KB

So if someone has an idea to share, you’re welcome =D

Have a nice day,

@ppr @Hiba_B
As you often have nice alternatives !

2

Hey @Geoffroy_Pantegnies

Did you try leaving it empty instead of writing formula ?

Thanks
#nK

3

some more details are needed as not all things are clear.

However give try and let the Bot write the English function names instead of the french ones

4

Hello @Geoffroy_Pantegnies

What does it mean by not needed to write in every column? so are you getting the data from some other source and you are writing that to excel file?

Thanks

5

Empty like with “” in the array instead of the forumla ?

6

What kind of details can I share ?

Tried in english but it’s not recognized.

7

Exactly, I have an Excel file in which i’m looking for some datas, if they are present I write them in some columns otherwise I write something else in another column.

So at first I wanted to write all datas with the add data row and then write in F2, G2 and H2 the formula and auto fill the column but it will erase some datas that are already written
(In my first screenshot, when there is no data in “D”, we are looking for other datas and we write them in G/H instead of the formula while when there is data in D, the formula will find value to wtire in the other cells)

8

Yep @Geoffroy_Pantegnies

9

Hi Bro.

You can build an datatable which include columns from A to D ( not include columns have fomular ).

Add data row into this datatable and then write range into the excel file.

10

Yes that’s what I’ve done initialy and it’s deleting the formulas already present in the cell.

11

That’s a nice idea but the problem is, depending on the results I have from the first Excel file (where I get the values), I may need to write in one of the next cell (and overwrite the formula)

12

we would try to replicate the issue. So everything which supports us on this will help us.

@Hiba_B ping me for joint work. Also one of my colleagues @kumar.varun2 will help us

13

Oh that’s very kind of you but if you have to spend time on it please don’t. I would have liked to know if something was on your mind but not to make you work on this.
It’s working, even if it could be done faster so it’s not a blocking point.

15

Hi @Geoffroy_Pantegnies !

Here is a suggestion to make it work: WriteRangeAndFormula.xaml (14.5 KB)
with this dummy file Geoffroy.xlsx (12.5 KB)

Does it work the way you were looking for ?

@ppr sorry as I changed work I am not as present as I used to :sweat_smile: would be glad to catch up at another occasion !

16

Hi Hiba,

Thanks for the efforts you’ve put into my problem, and your comments in the xaml are usefull.

But it does not solve the problem.

I’ve find some linq to help me to write into the Add data row the needed values.

But for that I had to delete the formulas inside the Excel sheet (they are done with the linq).

Thanks and have a nice day :slight_smile:

17

That’s great @Geoffroy_Pantegnies !
Do you mind sharing here the linq code ?
That would help people that have the same problem and find your post :slight_smile:

18

We have a couple of assign depending of the datas we find on the Excel sheet but they all look like:

Assign
To: List of String
Value: (
From row As DataRow In DataTable
Where convert.ToString(row(“Column”)).Equals(String)
Select convert.ToString(row(“Column2”))
).tolist

Then in another string:
To: String2
Value: List.FirstOrDefault

So we look into an other Excel File to look up for another value (what the formula was doing in the first place) and then put the String2 inside the add datarow.