Excel writer does not evaluate excel formulas

Hi there,

I want to copy values from one excel file to another (existing) excel file with two sheets (one with the copied values (input sheet), the other one with excel formulas related to that input sheet (output sheet)).
For that purpose I use the “Excel Writer” node and activated the check boxes “append” (to the existing file), “overwrite” (the existing sheet in the exitsing file with the same already existing sheet name) as well as “Evaluate formulas”.

But somehow the already implemented excel formulas are not evaluated after executing the node. They still show the old values. They are not evaluated before I directly go into the cells confirming with “enter”. (I use KNIME 4.3.2.)

How can I solve that issue?

Thanks in advance for help!

@rohdchri welcome to the KNIME forum

There is this node by @AnotherFraudUser that has such a function to write Excel formulas to a template of they have an English syntax. Maybe you give it a try.

Other than that you might have to use python, you can find examples in this collection.

2 Likes

@mlauber71 at first thank you for your quick help! Your recommended node works well. My only and still existing problem is that in my second Excel sheet in which (only) Excel formulas are already implemented (related to the first/input sheet) are not evaluated automatically. I have to click into each cell with a formula to calculate the new values. I only update the first (input) sheet with current data via KNIME. The second sheet with the formulas is not touched by the copy-and-paste-workflow. (Within the Excel options the automatically evaluation of formulas is activated, too.)
Do you have an idea what might be the reason the Excel formulas in the second sheet do not evaluate automatically after updating data in the first sheet by KNIME?
Thank you in advance!

Hi @rohdchri,

at least in my Write to Excel Template Node - I set it to only evaluate cells which are written by the node - i wanted to keep all the template part the same as much as possible :thinking:

Just added the additional option for force recalc of all formulas (disabled by default)
The Update should be availible in a few minutes :hugs:

Not sure how the Excel Writer handles the option - but another workaround could be the options in Excel itself:

4 Likes

Hello @rohdchri ,
I faced a similar problem in a previous test a while ago.

Depending on the type of process, your needs of speed, complexity of the excel structure… an ‘easy’ way to face the problem is: by opening the excel sheets in a loop by using the ‘CmdWinput’ and then, collecting the outputs at the end of the loop. This may not scale for some processes and ambitions.

Loop Start > Excel Sheet Appender > CmdWinput [ excel.exe ] > read data > Loop End

You will have to use a piece of VisualBasic in your reference excel aiming to: auto recalculate when opening, save and close. This approach will also may give you some difficulties to handle the excel; as you will have to block the macro to be executed when opening it from the file explorer.

I hope this helps you. Regards

3 Likes

Hi @AnotherFraudUser,

great! Thank you! But where did you implement that additional option for force recalc?
I updated KNIME, but in the node dialog has not appeared an additional option:

Where can I chose that option?

The Excel-options itself do not seem to be the problem. I have already tried all possible settings with no success.

Sorry for that many questions which might be easy for others and thank you for help and patience!

Hi @rohdchri,

do you have the lastest update?
In this screen it should show the new option:

*if this is not the case even with the latest update - try to start knime with the knime.ini parameter -clean (sometimes it does not want to show the updates):
grafik

Hope this fixes your problem - at least with my Test-Excels it works. :slight_smile:
(should it not work - could you please provide an Test Excel - so I can check in detail?)

5 Likes

Hi @AnotherFraudUser,

yes, now it works! Indeed starting knime with knime.ini parameter “-clean” was the issue.

Thank you very much for this new additional option and your support :blush:

4 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.