Excel Interpretation of Blueprism Inputs

[MarcoMatuttis1]

I have a excel file that I open with blueprism to add some simple data like text in certain cells.

I use get excel sheet as collection for this.

I edit the collection and add texts and formulas

I can successfully enter text/formulas like =L2 in a cell and when I open the excel file in the MS Excel Software the result gets displayed correctly.

But when I enter more "complex" (not really complex) formulas like =SUMME(L2:L4) excel will display #NAME? in the cell.

When I double click the cell it switches the #NAME? to the correct result of the formula.

Is there a way to enter Text into cells that Excel interprets the input correct right away?

Or is there a button in excel that solves this. Maybe a way to format all the cells at once?

​​

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------

[Walter.Koller]

Since the cell hold valid content and works after refresh/recalc you could try to manually initiate the recalculation. It will be a sure guess there is a VB method for this but I don't think it is already implemented in Excel VBO, but maybe I am wrong. 
Quickest way to test this approach could be to use shortcuts:
https://support.microsoft.com/en-us/office/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f#bkmk_datawin

------------------------------
Walter Koller
Solution Manager
Erste Group IT International GmbH
Europe/Vienna
------------------------------

[MarcoMatuttis1]

Unfortunately it did not work with the shortcuts I tried. The most promising were the ones with F9 to force excel to recalculate everything. Sadly the #NAME? cells stay untouched, I figured out that when Press F2 and ENTER in these cells i dont have to double click them. Maybe I can do that with an excel Macro.

Anyone knows a good tutorial for this ? I never used excel macros before.​

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------

[MarcoMatuttis1]

I just experimented with excel macros a bit and I might have found a solution. (I'm testing it right now). When I enter the formula =SUMME(L1:L5) with a macro i also get the name error but when I use =SUM(L1:L5) it seems to work.

Even though my excel version is german he does not accept a Scripted input with german language commands. When I type it in myself SUMME works as well as SUM. But with a Macro only SUM did work.

EDIT
I'm just done with testing. That also did not change the problem.

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------
-------------------------------------------

[Walter.Koller]

Thanks for sharing your findings.
Since I am working with Office in different languages, this might be very helpful in future.

------------------------------
Walter Koller
Solution Manager
Erste Group IT International GmbH
Europe/Vienna
------------------------------

[Rafeeq__Mohammad]

The possible reason could be mismatch between data type of return value of formula and data type of cell/column.

------------------------------
Rafeeq Mohammad
Automation Engineer
Q4 Associates Ltd
------------------------------

[MarcoMatuttis1]

Seems I was happy to early ...

I'm just done with testing. That also did not change the problem.

For some strange reason it works within macros:

Range("L3").Value = "=SUM(L5:L10)"

Range("L4").Value = "=SUMME(L5:L10)"

L3 displays correct result
L4 displays #NAME? 

​

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------

[MarcoMatuttis1]

But how can I set the right data type ?

In blueprism I write text into a collection and then use the write collection to Excel action from the VBO ...

In Excel I tried formatting the cells a certain way but it does not change anything 😞

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------

[AndreyKudinov]

Actually VBA has Formula / FormulaLocal / FormulaR1C1 / FormulaR1C1Local.

Excel VBO should be using system culture, but I'm usually writing collections as arrays anyway (much faster) and specifically chose to use EN_US locale, otherwise it becomes inconsistent - same robot might work on one resourcepc and not the other if they have different language settings. 

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

[MarcoMatuttis1]

But how does this Formula FormulaLocal etc. help me when I write text with a calculation stage into the collection.
Do I have to edit the code stages in the MS Excel VBO for this to work?

I'm not a real expert in this field and learned everything by tríal&error so far.

I solved my problem not the elegant way but I use a excel macro that presses F2 and enter in every selected cell. 

That changes the #NAME? to the result I was looking for.

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------
-------------------------------------------

​