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.)
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.
@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!
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
Just added the additional option for force recalc of all formulas (disabled by default)
The Update should be availible in a few minutes
Not sure how the Excel Writer handles the option - but another workaround could be the options in Excel itself:
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.
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:
*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):
Hope this fixes your problem - at least with my Test-Excels it works.
(should it not work - could you please provide an Test Excel - so I can check in detail?)
