Excel.calculateformula not working

Aspose.Cells Product Family
1

I have the following code in VB.net: The template file.xls has a formula in cell m11 (=m4)...

Dim Excel as new Excel
Excel.open("c:\file.xls")
Excel.Worksheets(0).Cells("m4").PutValue(99)
Excel.CalculateFormula
Excel.Save("c:\file.xls")

Then later on I use aspose.cells to check the same Excel file for the value of cell m11. There is no value. If I open the file.xls with Excel, the cell calculates as expected. Then if I once again use aspose.cells to check m11 obviously I get '99'.

How can I get the routine above to force execution of formulae in cells without opening the file in Excel to look at it?

Thanks, --Mike

2

Hi Mike,

CalculateFormula method only provides a way to retrieve data correctly at run time but it doesn't save the formula result into Excel file. It lets MS Excel to calculate it automatically.

You just need to call CalculateFormula method before you access formula result at run time. So please change your code to:

Dim Excel as new Workbook
Excel.open("c:\file.xls")
Excel.Worksheets(0).Cells("m4").PutValue(99)
'When saving the file, you don't need to call CalculateFormula method
Excel.Save("c:\file.xls")

Excel.Open("c:\file.xls");

'Only before retriveing data in cells, call CalculateFormula method

Excel.CalculateFormula
Console.WriteLine(Excel.Worksheets(0).Cells("m11").Value)

3

That did the trick. Thanks!