Excel: Existing formulas are not updated in templates

Andreas_Wenzel

Hi board,

I'm struggling with the export of data from Navision 2013 to Excel using the Excel Buffer functions. I want to update an existing Excel file with (new) data. Therefore I open the existing file, add additional data (let’s say, A1..A10), write and close the file again. That works well.

But if there’s a formula in the existing sheet (e. g. SUM(A1:A10) in cell A11) this formula is not updated during the process. Instead the original value is still displayed. Even pressing F9 doesn’t work. The only way to update the formula is either retyping one of the values in A1..A10 or retyping the formula itself.

Any help is highly appreciated.


Thank you,
Andreas

FOR i := 1 TO 10 DO BEGIN
  recExcelBuffer.NewRow;
  recExcelBuffer.AddColumn(FORMAT(i),FALSE, '', FALSE, FALSE, FALSE, '0', recExcelBuffer."Cell Type"::Number);
END;
recExcelBuffer.UpdateBook('C:\temp\Book1.xlsx', 'test');
recExcelBuffer.WriteSheet('', COMPANYNAME, USERID);
recExcelBuffer.CloseBook;

Andreas_Wenzel

I found a work around.
The trick is to use CTRL+ALT+F9 instead of F9 to force a recalculation of all formula in the Excel sheet. This can be automated by running the command XLApp.CalculateFull.

Luc_VanDyck

Thx for sharing