Axolot Data

Hello,

I found a error when calculating a workbook.
If I try to put a value in a cell that was calculated and the formula has a error, I get the message "Formula is not compiled".
Here is the code:

var
sMsg : string;
sFileName : string;
xlsWorkSheet : TXLSWorksheet;
begin
sFileName := ExtractFilePath(Application.ExeName) + '\TestXLS.xlsx';
sMsg := '';
Try
xlsWorkbook.Clear;
xlsWorkbook.Version := xvExcel2007;
xlsWorkbook.Filename := sFileName;
xlsWorkSheet := xlsWorkbook.Sheets[0];
xlsWorkSheet.AsFormula[0, 2] := '15/0';
sMsg := 'First Calculate';
xlsWorkbook.Calculate;
xlsWorkSheet.AsFloat[0, 2] := 15;
sMsg := 'Second Calculate'; // Here when the error occurs
xlsWorkbook.Calculate;
xlsWorkbook.Write;
Except
on E : Exception do
begin
MessageBox(Application.Handle, PChar(E.Message + ' - ' + sMsg), 'TestXLS', MB_OK + MB_ICONERROR);
end;
end;
end;

Best regards,

Adriano Silva

Hello,

The error occurs even if I try to clear cell's content:

xlsWorkSheet.ClearCell(0, 2);
xlsWorkSheet.AsFormula[0, 2] := '';
xlsWorkSheet.AsFloat[0, 2] := 15;
xlsWorkbook.Calculate; // Here occurs the error.

Any news on this?

Best regards,

Adriano Silva

Hello

Do not add empty formulas. You have to implement check for that in your code.

And how can I clean the formula?
I put a code just to show you how the error is generated.
In my application I'm opening a Excel file created by other person using Excel.
When I open the file and try to recalculate I get the error "Formula is not compiled".

Best regards,

Adriano Silva

Hello

Delete the cell or if you want to keep the formatting, make it a blank cell. There is no such thing as empty formula cells in excel files.

Hello,

I tried to delete de cell, even clear the cell, and the error occurs the same way.

Hello

Please send me the excel file you uses.

Hello,

You just need to use the code below to reproduce the error:

var
sMsg : string;
sFileName : string;
xlsWorkSheet : TXLSWorksheet;
begin
sFileName := ExtractFilePath(Application.ExeName) + '\TestXLS.xlsx';
sMsg := '';
Try
xlsWorkbook.Clear;
xlsWorkbook.Version := xvExcel2007;
xlsWorkbook.Filename := sFileName;
xlsWorkSheet := xlsWorkbook.Sheets[0];
xlsWorkSheet.AsString[0, 0] := 'Value';
xlsWorkSheet.AsString[1, 0] := 'Formula';
xlsWorkSheet.AsString[2, 0] := 'Result';
xlsWorkSheet.AsFloat[0, 1] := 10;
xlsWorkSheet.AsString[1, 1] := 'A2*5';
xlsWorkSheet.AsFormula[2, 1] := 'A2*5';
xlsWorkSheet.AsFormula[0, 2] := '15/0';
xlsWorkSheet.AsString[1, 2] := 'A3/5';
xlsWorkSheet.AsFormula[2, 2] := 'A3/5';
sMsg := 'First Calculate';
xlsWorkbook.Calculate;
xlsWorkSheet.DeleteCell(0, 2);
sMsg := 'Second Calculate';
xlsWorkbook.Calculate; // Here is where the error occurs
xlsWorkbook.Write;
Except
on E : Exception do
begin
MessageBox(Application.Handle, PChar(E.Message + ' - ' + sMsg), 'TestXLS', MB_OK + MB_ICONERROR);
end;
end;
end;

Hello,

Any news on this issue?

Hello

I can't reproduce this. Have you tested with the latest update?

Hello,

The latest update solved the problem.

Tks.