Error executing command Calculate

[adrianosilva]

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

[adrianosilva]

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

[larsa]

Hello

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

[adrianosilva]

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

[larsa]

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.

[adrianosilva]

Hello,

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

[larsa]

Hello

Please send me the excel file you uses.

[adrianosilva]

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;

[adrianosilva]

Hello,

Any news on this issue?

[larsa]

Hello

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

[adrianosilva]

Hello,

The latest update solved the problem.

Tks.