Excel refreshing formulas

Help
, ,
1

Hi everyone!
I’m facing a weird fact in processing my robot, I’ll try to explain.
I want to automate a very technical process for a client that, in the final phase, makes use of an add-inn installed on Excel. This add-inn simply introduces a new set of formulas which extrapolate figures and data from a result file obtained externally. The template for downloading these data is just set up, I need only to update a cell and click Formulas → Calculate Now → Save.
The weird fact is that, nevertheless the robot performs correctly all the steps that I have indicated, including “Formulas → Calculate Now → Save” - I can see it in the screen -, when I open the file I find a message of error =NAME? in all cells, as if they were not refreshed. On the contrary, if I try to do all the steps manually, everything works.

Is there someone who can help me to understand?
Thank you in advance
Lorenzo

PS: consider that I have to keep Calculation Options = Manual and I can’t modify this setting.

2

Hi @D3BO_C3PO,

You can give shortcut “F9” to refresh the formula manually.

Please refer this link.

3

Hi @Vivek_Arunagiri,
thanks for your reply.
I have already tried this way. I have also tried to add a send hotkey activity Alt+mb, which should be the same, but nothing.

4

Or use the send hotkey activity to navigate the ribbon.

This example does what you need with a simple example and it works. I set the options on excel to only update formulas when the it is called.

RefreshCalculations.zip (8.3 KB)

Your error suggest some kind of formula syntax issue within the excel report itself. Are the inputs to your formula dependant on the refresh also perhaps?

5

Try click activity on Calculate now button under formula tab.

image

6

Hi @ronanpeter,
thanks for your reply. Yes, the cell that I update is an argument of the formulas I want refresh.

7

Have you tried attaching a simple Macro to the file and calling that?

recalculate all open workbooks
Application.Calculate

recalculate a specific worksheet
Worksheets(1).Calculate

recalculate a specific range
Worksheets(1).Columns(1).Calculate

8

In that case try the following. This rechecks dependent formulas before recalculating. It is sending hotkey shift+ctrl+alt+f9 to do so.

RefreshCalculations2.zip (59.9 KB)

9

I tried to adopt both RefreshCalculation.zip and RefreshCalculation2.zip model. The result is always the same: nothing. I’m starting to think there is something wrong (eg an optional flag marked in the Excel Option panel) in the template file.
Responding to your previous suggestion: yes, I have already though about a simple macro to run, but I would really prefer avoid it for a number of reasons as well as to do not overload the file.

10

Just tried unsuccessfully :-(.

11

Keep a break point at Refresh and debug. When bot is at break point, check if the formula is correct.

12

The fact your intended automation can be carried out using a simplified spreadsheet, as in my worked examples, shows that your issue is excel related and not UiPath. Possibly because of the nature of your formulas some are referencing other inputs which are not updated under the same circumstances.

It would useful to see a copy of your excel file.

13

I’m afraid I can’t upload attachments because I’m a new user of the forum. However, I think you could not see anything without the license of the Excel add-in.

14

Hi @D3BO_C3PO

the formula was shown in all col … after bot completed?

15

Hi,
I have several tables with formulas, in which both first row and column are a formula input. So, every table element is a formula.

16

did u tried data->Refresh All click button

17

I have tried all strategies you can immagine, except the macro.