Inserting rows in a named ranges does not modify formulas?

[marius]

I'm evaluating TXLSReadWriteII5 version 5.20.03a on Delphi XE. We want to use excel templates with a named ranges to 'feed' pivottables and charts.

As an example, the pivottable and charts are on sheet 1 and all the data will be in sheet 2. The data consists of 2 columns name and value, has initially 2 rows (so we can place range and some example data) and some formula's below it (in this case a simple sum). We then insert thousands of lines and that part works with Sheet[sheet].InsertRows(row,count) etc or xls.InsertRows(sheet,row,count)). However the range isn't adjusted (it is not extended). Do i use the wrong insertdata or is there another trick to accomplish this? Or does TXLSReadWriteII5 not support any of this

Code: Select all

name      value
a           2  
b           4
         =SUM(B2:B3)

Range data=Sheet2!$A$1:$B$3

Thanks,
Marius

(A happy owner of good old version 4)

[larsa]

Hello

This is fixed in update 5.20.04

[marius]

Thank you!

I will re-test later this week

[marius]

Hello Lars,

I upgraded to version 5.20.04a, but problems again. Rows are being inserted, but the named range is not extended and the sum formula's are not adjusted.
i used Samples\Delphi\CopyMoveDelete as a demo, adjusted the copy code to this, run, button "read", button "Copy, Move and Delete cells", button "write".
(The colored area in MoveCopyTest.xlsx is a named range and I added a couple of sum formula in the bottom)

Code: Select all

procedure TfrmMain.btnCopyClick(Sender: TObject);
begin
  XLS[0].InsertRows(10,10);
end;

Do you have a solution for me again ?

Regards,
Marius

[larsa]

Hello

Can you please give me a complete code sample?

[marius]

Hello

Can you please give me a complete code sample?

Example code send to support email..

Thanks,
Marius

[larsa]

Hello

This is fixed in update 5.20.06

[marius]

Thanks, its indeed fixed.

Now I can start my testing with template excel files in combination with pivot tables!