I have 2 workbooks, FRefBook and FWorkbook. I want to copy a formula from the FRefBook instance. The formula is:

[demo.xls]Sheet1!C1

I get errors trying to write the external reference to the new workbook.

two questions:

1. Is there any way to read a formula with an external file reference?

2. Is there any way to write a formula with an external file reference?

Here is some sample code in Delphi.

- Code: Select all
`vFormula := FRefBook.Sheets[0].AsFormula[0, 0];`

// vFormula should be exactly '[demo.xls]Sheet1'!C1

// but vFormula as read from FRefBook is demo.xlsSheet1!C1 and that is totaly incorrect

if vFormula <> '' then begin

// raises exception - error in formula demo.xlsSheet1!C1

FWorkbook.Sheets[0].AsFormula[2, 8] := vFormula;

// fix formula to exactly what is in working spreadsheet

vFormula := '''[demo.xls]Sheet1!''C1';

// raises exception - error in formula illegal character

FWorkbook.Sheets[0].AsFormula[2, 8] := vFormula;

// try a different formual without quotes

vFormula := '[demo.xls]Sheet1!C1';

// raises exception - eConvertError demo.xls is not a valid floating point value

FWorkbook.Sheets[0].AsFormula[2, 8] := vFormula;

end;

Thanks very much.