12-04-22 02:57 PM
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
------------------------------
Answered! Go to Answer.
19-04-22 01:09 PM
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.
13-04-22 06:19 PM
13-04-22 06:24 PM
14-04-22 01:50 AM
19-04-22 12:24 PM
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
------------------------------
19-04-22 12:57 PM
"=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,
19-04-22 01:09 PM
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.
20-04-22 07:39 AM
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.