I have tried to recreate the problem in a small example program.
When I write to an xls-file the references are ok the first time when I create them, but when I overwrite the definition it shows up i Excel as eg.: =#REFERENCE!$F$12:$H$44
When I use an Xlsx-file the references are ok both the first write and the following writes.
The code is migrated from XlsReadWriteII 4, and likely ealier versions before that. The customers tells me that the problem is new since the upgrade to version 5; before they could only use xls-files.
Here is the small program that recreated the problem:
Code: Select all
FXLSWriter: TXLSReadWriteII5;
....
procedure TForm49.MakeRef(AName, ADef: string);
var
Existing: TXLSName;
begin
Existing := FXLSWriter.Names.Find(AName);
if Assigned(Existing) then
begin
Existing.Definition := ADef;
end
else
FXLSWriter.Names.Add(AName, ADef);
end;
procedure TForm49.MakeSheet(AName: string);
var
SheetIndex: Integer;
begin
if Assigned(FXLSWriter.SheetByName(AName)) then
begin
SheetIndex := FXLSWriter.SheetByName(AName).Index;
FXLSWriter.Delete(SheetIndex);
end;
FXLSWriter.Add;
FXLSWriter.Sheets[FXLSWriter.Count-1].Name := AName;
end;
procedure TForm49.ResetReferences(ASheetName: string);
var
I: Integer;
begin
for I := FXLSWriter.Names.Count-1 downto 0 do
if (copy(FXLSWriter.Names[I].Name, 1, length(ASheetName)+1) = ASheetName + '_') and
not(FXLSWriter.Names[I].VBProcedure) then
FXLSWriter.Names[I].Definition := FXLSWriter.SheetByName(ASheetName).name + '!$A$1';
end;
procedure TForm49.WriteExcel(AFilename: string);
begin
FXLSWriter := TXLSReadWriteII5.Create(nil);
FXLSWriter.Filename := AFileName;
FXLSWriter.Read;
MakeSheet('Diverse');
MakeSheet('Andet');
ResetReferences('Diverse');
ResetReferences('Andet');
MakeRef('Diverse_A', 'Diverse!$B$5:$B$38');
MakeRef('Andet_omr', 'Andet!$F$12:$H$44');
FXLSWriter.Write;
FXLSWriter.Free;
end;