Set xlsx to recalculate formulae on open

If calculation mode is set to automatic, Excel always (re)calculates workbooks on open.

So, just generate your files with calculation mode set to "Automatic".

In xl/workbook.xml, add following node to workbook node:

<calcPr calcMode="auto"/>

Also check Description of how Excel determines the current mode of calculation.

You can use macros as suggested, however you will create a less secure and less compatible workbook without avoiding user interaction to force calculation.

If you opt by using VBA, you may Application.Calculate in Workbook_Open event.


The Python module XlsxWriter sets the formula <v> value to 0 (unless the actual value is known) and the <calcPr> fullCalcOnLoad attribute to true in the xl/workbook.xml file:

<calcPr fullCalcOnLoad="1"/>

This works for all Excel and OpenOffice, LibreOffice, Google Docs and Gnumeric versions that I have tested.

The place it won't work is for non-spreadsheet applications that cannot re-calculate the formula value such as file viewers.

Tags:

Openxml

Xlsx