Excel functions are not recalculated

other_s
question_s
excel
issues
#1

Hello, everybody,

I have a recording which reads prices from a website with Xpath, “caches” them into a string variable and then appends the cached prices into a list variable with “expression value”. In the end, I write the values of the list variables in Excel and save the Excel file. Up to here, everything works perfectly.

In Excel, the prices are copied as a string from the list variables. Since I calculate with the prices, I convert the prices (string) into prices (number) with Excel functions.

Unfortunately, the Excel functions (String -> Number) do not work immediately when I open the Excel files manually. I have to double click on the cells and then on Enter, after that the Excel functions will work again. This is only the case if I fill the Excel file with the RPA.

Is there a solution?

Filling an Excel file with the RPA. Excel functions do not work.

#2

Hello @jawohl.
What do you mean here?

Is your issue with specific Excel file or with RPA Express working with Excel?

#3

I think the small video will describe my problem very well.

2019-04-01 09-23-25.7z (4.0 MB)

#4

Does nobody have any idea how to avoid this error?

#5

Hi @jawohl.
To be honest, I didn’t see any error from RPA Express side. If you mean that Excel formulas are not recalculated automatically, you need to check your Excel settings.

#7

I have an RPA bot.
This bot writes the values A1 and B1 into cell A1 and B1. In cell C1 there is the Excel function “=A1&” and “&B1”

The expected function result is “A1 and B1”.
After writing via RPA-Bot the Excel function is not executed and the result is " and ".

Is there a solution?RPA_Konfiguration Ergebnis_nach_RPA

#9

Hi @jawohl

Here’s a little workaround:
Create one more empty string variable, and add “Get cell Value” to the cell where your formula is and save it in an empty variable. After this cell should be recalculated with formula:
image

Also don’t forget to put a check in “Open spreadsheet”
image
or add “Save spreasheet” action at the end.

3 Likes
#10

A little note:

Unfortunately this does not work with RANGE

#11

Hi, @jawohl

do you mean it doesn’t work when you use Get Range at the end? Or when you set new values in Excel using Set Range?

#13

I do the same actions as above but with RANGE instead of CELL.

Example:
Set Range
Set range
Get Cell Value

I’ve also tried this:
Set range
Set range
Get Range Value

All unfortunately without success.

1 Like
#14

What formulas do you use?

If these are formulas that only work with numeric data, like =SUM(A1,A2), for example, they will work only if you set the data from Number variables, and in Set Range they are inserted as string values.

#15

I have many formulas, one of them is the formula =A1

So a pure display of the value from cell A1 in cell H1. And that doesn’t work.

It seems as if something gets stuck after opening and saving the Excel. For example, if I delete a column in Excel, the Excel formulas will work again.

I uploaded a zipped video 22 days ago. Have a look at the short video, then you will see my problem

#16

I didn’t quite get the issue from the video as it doesn’t show the script in RPA Express.

Can you share your script to have a look?

I tested it on this case and it worked:

There is an excel file with formulas in columns C and D.
image

image

I have the following script:
the bot inserts values in columns A and B

and then reads recalculated values of C and D.

After executing the script the formulas are recalculated

image

#17

Thank you for taking so much time for my request :slight_smile:

Enclosed my script. Is this section enough?

#18

There are no Get Range actions in the script.
To recalculate the formulas with new values, you need to read them using Get Range, Ret Column (Row) or Get Cell Value, like on the example below.

#19

I deleted the GET RANGE because it does nothing.

I just inserted GER RANGE again. Unfortunately still negative result.

#20

Can you share the excel file you use?

#21

Sent you the Excel file. Check your mail inbox.

1 Like
#22

I tested on your file and got an error in the Excel actions.
And you are right, the values didn’t update :disappointed:

We’ll investigate further which formulas cause it.

I’ll let you know as soon as there is an update.

1 Like
#23

Hi every one …
I have some number i.e: 5,4,7 ect ,in a excel column(column ‘A’) and i want to MULTIPLY those no into a particular number e.g:- 8 or 5 or 7 etc. And print the result into respective cell of column ‘B’ .

For example : In my excel sheet in the first column(A) contains the value i.e (5 in A1) ,(4 in A2),(3 in A3) etc. So i want to multiply those no into ‘8’ ,(58),(48),(3*8) and print the result into column ‘B’ ,I.e:- (40 in B1),(32 in B2) and (24 in B3) .
exp

Please tell me how do i do that…

Thank you…