How to automatically refresh excel formulas?

Question

I'm experiencing big problems using ExcelPackage, because sometimes the formulas are not updated (even if I remove the value).

I've tried using ExcelInterop to save xlsx as xls, thinking that this could resolve the issue, but it didn't.

The way I've found to resolve it is by pressing CTRL + ALT + F9. The user will not like doing it everytime, so, I want to do it programatically. Do you know any effective way to ensure all xlsx formulas are updated?

Answer 1

Call Application.CalculateFull from a VBA macro (or over the COM object)

Answer 2

I have found this to work for me before:

for (int iWorksheet = 1; iWorksheet <= workbook.Worksheets.Count; iWorksheet++)
                {
                    ((Worksheet)workbook.Worksheets[iWorksheet]).UsedRange.Formula = ((Worksheet)workbook.Worksheets[iWorksheet]).UsedRange.Formula;
                    ((Worksheet)workbook.Worksheets[iWorksheet]).Calculate();
                }

Answer 3

SpreadsheetGear for .NET can open, calculate and save xls and xlsx workbooks without Excel and tends to be easier to work with from .NET applications because it is entirely .NET code (written in C#).

You can see some examples here and download the free trial here if you want to try it out.

Disclaimer: I own SpreadsheetGear LLC

Answer 4

You can always set Application.Calculation = xlAutomatic. That way you don't rely on your own code to perform the calculation and it's called only when needed.

Note that this setting affects the entire Excel instance. So if your client doesn't want other workbooks work like this - they should open it in a separate Excel instance.

Answer 5

I have a master sheet with a formulas refers to other sheets which are not exist during design time. I create a macro to get or refresh these sheets.

These formulas always return #REF! as there references are not exist yet. I used the below technique and was excellent.

Sheets(1).UsedRange.Formula = Sheets(1).UsedRange.Formula

Many thanks