Hyperlinks lost when using InsertRows

[keithblo]

Support,

It would appear that calling InsertRows, causes all previously inserted hyperlinks to be lost in the document.

Please could you advise a fix/workaround ASAP.


Regards,

Keith Blows

Code: Select all

program XLSHyperlinkErr;

{
  Demo showing what appears to be a bug in XLSReadWriteII V3

  1. Using InsertRows causes all hyperlinks below the inserted row to be lost
}

uses
  SysUtils,

  XLSReadWriteII2, SheetData2, CellFormats2, XLSFonts2, BIFFRecsII2,
  Cell2, Rows2;

{$R *.res}


var
  lXLS1,
  lXLS2: TXLSReadWriteII2;
  i: Integer;

CONST
  DEMO_FILE = 'C:\ProblemFile.xls';
  LINKED_TO_FILE = 'C:\DestFile.xls';

begin  
  lXLS1 := TXLSReadWriteII2.Create(nil);
  lXLS2 := TXLSReadWriteII2.Create(nil);
  try
    // Create a blank Excel spreadsheet, which we will hyperlink a cell to...
    lXLS1.FileName := LINKED_TO_FILE;
    lXLS1.Write;

    // If necessary, create a new spreadsheet, with a cell hyperlinked to the previously create Excel file
    if not FileExists(DEMO_FILE) then
    begin
      lXLS2.FileName := DEMO_FILE;
      lXLS2.Write;
    end;

    // Re-open the Excel file a few times and add a new hyperlink
    for i := 1 to 5 do
    begin
      lXLS2.Clear;
     // Open and add a new hyperlink
      lXLS2.FileName := DEMO_FILE;
      lXLS2.Read;

      lXLS2.Sheet[0].InsertRows(2, 1);

      with lXLS2.Sheet[0].Hyperlinks.Add do
      begin
        Col1 := 0;
        Col2 := Col1;
        Row1 := 2;
        Row2 := Row1;
        Description := 'Open file...';
        // Hyperlinks don't insert the cell value.
        lXLS2.Sheet[0].AsString[0, 2] := 'Click #' + IntToStr(i);

        lXLS2.Sheet[0].AsDateTime[1, 2] := Trunc(Now);
        lXLS2.Sheet[0].AsString  [2, 2] := 'Some data';
        lXLS2.Sheet[0].AsString  [3, 2] := '--> To be done <--';

        Address := LINKED_TO_FILE;
        ToolTip := 'Open ' + LINKED_TO_FILE;
      end;
      lXLS2.Write;

     end;
     
     { We should now have 5 hyperlinked cells, but only 1 works }

  finally
    lXLS1.Free;
    lXLS2.Free;
  end;
 end.

[/code]