I have a workflow where I overwrite an Excel sheet. In another Excel sheet from the same .xlsm-document, I use the Data from the overwritten sheet f.e. (A4) =sheetX!B6. The problem is that the cells do not really refresh by themselves. I have to manually click in the Textbos from every cell to update it. The Refrehing options in Excel via F2 or F9 do not work for this. I think this is because KNIME delets the old “sheetX” and creates a new “sheetX” containing the new data. In Alteryx the data in the sheet gets deleted and then the new data gets written in the same old sheet, so the cells do not have to be updated.
I need this for 10000+ cells so manually refreshing is not an option. Do you have a solution for this?
Recalculations in large Excel sheets can be quite tricky and maybe they have been fully resolved since I was a heavy Excel user.
You should try the hardcore combination forcing the recalculation of all formulas: Ctrl + Alt + Shift + F9
In former times I used to write a VBA macro that would send this command via keyboard so to force the calculation.
Concerning Alteryx: are you absolutely sure the formulas get refreshed? How would that happen? I would assume it also does write the data to your “sheetX”?
Then we have discussions about options to use Python to refresh the formulas. But that might not be so easy.
And then there is this project in a later beta phase that promises to enable calculation of formulas with Python (you would have to see if it can handle large datasets):
Glad it worked. And strange these recalculation problems still persist even after all these years (I remember a small consulting job in the 90ies where I encountered this thing …).
What I remember from the days was that I sent the key combination via VBA actually emulating keystrokes to make sure the comand was sent (I would have to see if I can find an example).
And I sometimes did it two in a row with some wait time inbetween - just to be sure…