Axolot Data

Posted: **Fri Jul 12, 2019 1:24 pm**

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.

Posted: **Wed Aug 07, 2019 11:45 am**

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".

Posted: **Fri Aug 09, 2019 12:31 pm**

sorry for double message

Posted: **Fri Aug 09, 2019 12:35 pm**

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.

Axolot Data

Excel97 sheet references

Excel97 sheet references

Re: Excel97 sheet references

Re: Excel97 sheet references

Re: Excel97 sheet references