topic Excel VBO escape character when passing a formula in Value in Deutschsprachige Community

Excel VBO escape character when passing a formula in Value

MarcoMatuttis1 — Tue, 12 Apr 2022 13:57:00 GMT

Whats the correct way to send a string with a semicolon ( ; ) as a Value in the e.g. Set Cell Value

I already tried some variations in the string but so far none of it worked.

I try to send the following formula as Value:

"=Teilergebnis(9;L2:L" & [Number of rows]-1+[Number of rows2]-4 & ")"

and get the following blueprism error:

It works when I use a more simple formula without a semicolon like

"=SUMME(L2:L" & [Number of rows]-1+[Number of rows2]-4 & ")"

So I'm pretty sure that I just somehow need to "escape" that semicolon.

Since the string is passed into the excel vbo code stage I guess that it is causing problems there.

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

RE: Excel VBO escape character when passing a formula in Value

ewilson — Wed, 13 Apr 2022 17:19:00 GMT

Hi @MarcoMatuttis1,

I take it this is a German localized version of Windows and that's why you need to use the semicolon? Is your Blue Prism instance also localized for German? Control of whether a comma, semicolon, or whatever is used as a parameter separator happens at the O/S level based on its Regional Settings.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel VBO escape character when passing a formula in Value

ewilson — Wed, 13 Apr 2022 17:24:00 GMT

I just took a look at the code behind that Code stage and it seems a bit odd to me. I'll run a few tests in a little bit and get back to you as I think that may need to change. The code that is.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel VBO escape character when passing a formula in Value

ewilson — Thu, 14 Apr 2022 00:50:00 GMT

@MarcoMatuttis1,

FWIW - There's a v6.10.5 of the Excel VBO available on the DX. It includes a new action called Set Value of Cells in Range. This action allows you to set the value of 1 or more cells using a Range. It's a bit cleaner than the Set Cell Value action in my opinion. You might give that action a try and see if you experience the same error.

Hint: If you just want to set the value of a single cell (ex. A13), set the value of the range input to "A13".

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel VBO escape character when passing a formula in Value

MarcoMatuttis1 — Tue, 19 Apr 2022 11:24:00 GMT

I just downloaded the new Version and tried the Set Value of Cells in Range Function but unfortunately it did not work.

I'm not sure why this should be a german localization problem. I mean all I'm doing is basically passing a string through the codestage.

Unfortunately that string contains a semicolon which does seem to break the Excel VBO Visual Basic syntax rules.

GetInstance(handle).ActiveCell.Value = value
Dim activeCell = GetInstance(handle).ActiveCell
SetProperty(activeCell, "Value", value)

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

RE: Excel VBO escape character when passing a formula in Value

ewilson — Tue, 19 Apr 2022 11:57:00 GMT

@MarcoMatuttis1,

Heres another format for the formula that you can try:

"=Teilergebnis(9" & Chr(59) & "L2:L" & (([Number of rows] - 1) + ([Number of rows2] - 4)) & ")"

Instead of placing the actual semicolon in the string I'm using the Chr() function to interpret it at runtime. I've also made your arithmetic a little more readable with parentheses.

The reason I mentioned localization is because at its heart, Excel is based on English localization which means commas are used to separate values, formatting of numbers, etc. If there's a difference in the localization setting of the O/S, Office, and/or Blue Prism, it can introduce strange errors when interpreting cell values and formulas.

Further, The error code 0x800A03EC means NAME_NOT_FOUND; in other words, you've asked for something that Excel can't find. This could be the function name, if it were a custom function, or a range of cells if the range specified isn't formatted correctly, etc. This error is being raised by Excel itself, not the VBO. It's just being returned via the VBO because Excel doesn't like what you're trying to have the VBO do.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel VBO escape character when passing a formula in Value

MarcoMatuttis1 — Tue, 19 Apr 2022 12:09:00 GMT

I will try that with the original function. But I just found a working solution.

It's kinda braindead solution but it works.

I just hardcoded the most part of the string into the visual basic code stage. And it looks like this now:

GetInstance(handle).ActiveCell.Value = value
Dim activeCell = GetInstance(handle).ActiveCell
SetProperty(activeCell, "Value", "=Teilergebnis(9;" & value & ")")

I only pass the range as value

L2:L" & (([Number of rows] - 1) + ([Number of rows2] - 4))

not the most elegant solution but it works.

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

RE: Excel VBO escape character when passing a formula in Value

MarcoMatuttis1 — Wed, 20 Apr 2022 06:39:00 GMT

I just tried the

Chr(59)

solution but it did not work. When I evaluate the expression the string is exactly the same as when I enter it directly.

I think I would need a solution for that semicolon character that will not be processed on the blueprism level but on the visual basic codestage level.

But since I have a working solution now it does not matter anymore.

But thanks for your support.

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