This project is read-only.

Ensuring your formula are recalculated on File-Open

One problem we came across with Excel 2007 is that it does not automatically re-calculate the spreadsheet when it is re-opened – even when the Calculate option set to automatic! This is because the existing cells in the template have both a formula and a value in the cell. So Excel just assumes the value is correct and does not attempt to re-compute the formula. Of course, we have just added twenty rows of data and updated the formula references in the XML – but Excel has no why of knowing this, so assumes the values must be right!

The only way to force the recalculation is to ensure the cell has no value – just a formula. So the RemoveValue() method becomes very useful for all formula in the worksheet. Hence:

worksheet.Cell(22, 5).RemoveValue();

Because of this phenomenon, we changed the ExcelCell.Formula property assignment code so that it removes the cell’s value when you assign the cell a formula.

Last edited Jan 3, 2007 at 5:47 PM by JohnTunnicliffe, version 1