Losing Formulas in Excel when inserting Collection

[NiklasFranke]

Hello,

currently i am facing the problem, that when in get values from an Excel as collection and insert it back, the Excelsheet loses all Formulas, which would be normally there in the cells.

Is there any way to preserve the Formulas or do i need to recreate these in Blue Prism?

------------------------------
Niklas Franke
------------------------------

[Mukeshh_k]

Hi @NiklasFranke : Thought we could amend the properties of set to retain and apply formulas along with values but it didnt work in my modified code, I might see that later but for time being here's the solution that should work for you - If I am retrieving a collection by default it comes in Text format and when I rewrite it back to Excel it goes as same Text format and formulas get erased with your updated collection text values, you should not be disturbing the particular column with formulas stored -

To avoid this -
1) Use Get worksheet range as collection (Identify the last cell of your table - 1 and do not disturb the column which has formulas)
2) Edit your collection with your data and rewrite this collection on excel sheet.




Alternatively- If you want to set formulas in any column of your collection - You need to identify the last row of your column where you need to put formulas , Use Set Range Value and pass as below :

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[NiklasFranke]

Hello, 

i just inserted the formulas back to the Cell, But after inserting the first formula, the action failes and the "normal values" without a formula cant be inserted. When i remove the Formulas and just isert normal values, it works...

------------------------------
Niklas Franke
------------------------------

[NiklasFranke]

I have the feeling, that i have to insert the whole Data etc. and than, in the last and second step, i have to insert the Formulas to the needet columns.

I tried to insert the Data with the Normal "insert Collection" but then BP just does not respond anymore.

------------------------------
Niklas Franke
------------------------------

[NiklasFranke]

And when i just use the formulas from the last cells, and delete the formulas from the 1. cells, its works, but BP still gives an error after insertig the last field with the formula.

------------------------------
Niklas Franke
------------------------------

[Mukeshh_k]

Hi Niklas- I suppose the formula value you are trying to pass is not correct or does not have correct syntax - cause Ideally if you do simple formula in one of your columns in collection - let's suppose =(B2+D2) it will work all time - its something to do with the syntax probably of which I am not sure - We would need to break it down further.

Can you simply paste blank values in place of formula and leave other plain data as is and try using Set cell value in one of the required formula cell - pass the formula as value in the specific cell first and see if it works or still gives the same error - if it generates an error can you show the error and value you are trying to put.

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[Mukeshh_k]

Also it does seem to me as if you are trying to paste a large volume 15k rows & it is becoming less responsive - Can we not directly operate on excel sheet itself rather than getting data in collection ? and use set cell values or set range values ? it would be good to understand the end to end scenario why we are reading the data in the collection in the first instance when we can directly amend values on the excel itself ?

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[NiklasFranke]

Hell Mukesh, when i work with excel, usually i get the data as collection, work with it, and isert it back. The Formulas, which i am using are from the Exceldata in where they work. F.e., =IF(G4624="";"";COUNTIF($G$5:$G$10003;G4624)) just looks till row 10003, if the number is double in the column. So thats on reason, why i cant just insert values, because when i would insert the number the 1. time, the amount would be 1 and when i insert the number the secont time below in the column, the 1. Value woult still be 1. So Formulas are easier. 15k rows are no problem with the "write collection fast" VBO.

But would it be better just to insert the rows i have worked with?

I tried the process with 1 Formula that defently worked (pictures attached) but even then, after inserting the Forumal the rest is missing 

------------------------------
Niklas Franke
------------------------------

[NiklasFranke]

Okay i found a work around. I look for the Cellreference with "Find in  Worksheet" and then use "Set Cell Value" where i use the needet Cell reference, F.e. the needed number is in Column C and i get something like C150 as reference, then i replace the C with the needed Column like A:  Replace([Matches.Cell]; "C"; "A")

So, I insert just the worked values und not the 15k rows of the whole collection.

Nonetheless, thank you very much for your time and the Help 🙂

------------------------------
Niklas Franke
------------------------------

[Mukeshh_k]

Glad you found a way through it Niklas, Cheers !

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------