topic RE: Excel Interpretation of Blueprism Inputs in Product Forum

Excel Interpretation of Blueprism Inputs

MarcoMatuttis1 — Tue, 10 Nov 2020 10:55:00 GMT

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
------------------------------

RE: Excel Interpretation of Blueprism Inputs

Walter.Koller — Wed, 11 Nov 2020 08:59:00 GMT

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
------------------------------

RE: Excel Interpretation of Blueprism Inputs

MarcoMatuttis1 — Wed, 11 Nov 2020 10:31:00 GMT

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
------------------------------

RE: Excel Interpretation of Blueprism Inputs

MarcoMatuttis1 — Wed, 11 Nov 2020 11:11:00 GMT

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
------------------------------
-------------------------------------------

RE: Excel Interpretation of Blueprism Inputs

Walter.Koller — Wed, 11 Nov 2020 11:25:00 GMT

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
------------------------------

RE: Excel Interpretation of Blueprism Inputs

Rafeeq__Mohammad — Wed, 11 Nov 2020 11:44:00 GMT

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
------------------------------

RE: Excel Interpretation of Blueprism Inputs

MarcoMatuttis1 — Wed, 11 Nov 2020 12:08:00 GMT

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
------------------------------

RE: Excel Interpretation of Blueprism Inputs

MarcoMatuttis1 — Wed, 11 Nov 2020 12:16:00 GMT

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
------------------------------

RE: Excel Interpretation of Blueprism Inputs

AndreyKudinov — Thu, 12 Nov 2020 23:05:00 GMT

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
------------------------------

RE: Excel Interpretation of Blueprism Inputs

MarcoMatuttis1 — Fri, 13 Nov 2020 07:35:00 GMT

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
------------------------------
-------------------------------------------

RE: Excel Interpretation of Blueprism Inputs

AndreyKudinov — Fri, 13 Nov 2020 12:09:00 GMT

If you want my opinion - I wouldn't expect Write Collection to work with formulas at all, because you might also want it to write text starting with "=".
In fact, my version explicitly escapes all such strings with '

If you want to set formulas, you can do it explicitly in separate action with code stage like this:

Dim xlInstance = GetInstance(handle)
Dim xlWorkSheet As Object = GetWorksheet(handle, workbookname, worksheetname)
xlWorkSheet.Range(cellref).Formula = value

cellref can be a range. And FormulaLocal works here too if you really want to use locale specific version, although I wouldn't do that.

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