Excel97 sheet references

[Moisha]

Hi.
I try to set reference in formula to another sheet but get error

Project Project3.exe raised exception class XLSRWException with message 'Error in formula 'Sheet2'!B1
Unknown name Sheet2'.

My code

Code: Select all

var
  excel: TXLSReadWriteII5;

begin
    excel := TXLSReadWriteII5.Create(nil);
    excel.Version := xvExcel97;
    excel.Add();
    excel[1].Name := 'Sheet2';
    excel[1].AsString[1, 1] := '1';
    excel[0].AsFormula[0, 0] := 'Sheet2!B1';
    excel[1].InsertColumns(0, 2);
    excel.SaveToFile('e:\1.xls');
    excel.Free();
end.

If I remove line "excel.Version := xvExcel97;" code works.
If I make file with cell Sheet1.A1 reference to Sheet2.B1 in Excel and open it with Axolot, i see in cell A1 formula "[ExternSheet]!B1".
And one more problem. If insert column A on Sheet2, formula in cell Sheet1.A1 is not udated.

Regards.
Anton.

[larsa]

Hello

When you add a sheet it will already have the name "Sheet2" but there is a bug preventing you from setting the name to the same name as the worksheet have.
The solution is to remove the line "XLS[1].Name := 'Sheet2';", or use another name than "Sheet2".

[Moisha]

sorry for double message

[Moisha]

Hi.
If I remove line "XLS[1].Name := 'Sheet2';" then new sheet really have name Sheet2, but I got error in line "excel[0].AsFormula[0, 0] := 'Sheet2!B1';"

Project Project3.exe raised exception class XLSRWException with message 'Error in formula Sheet2!B2
Unknown name SHEET2'.

If I set another name (Sheet3 for example) then in result file link from Sheet1!A1 to Sheet3!B1 is not updated when I insert columns on sheet Sheet3. Sheet3!B1 becomes Sheet3!D1, but link stays Sheet3!B1.

There is error in code, I write to cell Sheet2!B2 but make link to Sheet2!B1. It does not matter, link updating does not work any way.

Regards.
Anton.