Dynamic named ranges

[OlivierR]

Hi,

I would like to know if xlsReadWriteII is able to handle dynamic named ranges.
I mean named ranges that are using the OFFSET() function for their definition, for example:

Code: Select all

OFFSET(ref;0;0;nblines;nbcols)

where nblines and nbcols are numerical expressions depending on another (static) named cell.

Best regards,

[larsa]

Hello

Yes, that is supported.

[OlivierR]

Thanks !

[OlivierR]

Hello,
I am still trying to make dynamic named ranges work. But whenever I do TXLSName.Area I get a nil.
Has anyone got a solution?

[OlivierR]

Maybe an example will help understanding my problem.
In my excel file, I have only named ranges whose size is defined by OFFSET(ref, 0, 0, height, width).
In the code, I need to read the values of those named ranges. To do so, I have tried the following code:

Code: Select all

function TfMain.CheckExcelFile(AExcelFile: UnicodeString): Boolean;
var
  i: Integer;
  xName: TXLSName;
  sheetIndex, row1, row2, col1, col2: integer;
begin
  xlsReadWriteII5.LoadFromFile(AExcelFile);

  for i:=0 to xlsReadWriteII5.Names.Count-1 do
  begin
    xName:= xlsReadWriteII5.Names.Items[i];
      
    sheetIndex:= xName.Area.SheetIndex;
    col1:= xName.Area.Col1;
    Row1:= xName.Area.Row1;
    col2:= xName.Area.Col2;
    row2:= xName.Area.Row2;
    
    //getting the values using sheetIndex, row1, row2, col1 and col2
	
  end;
end;

When I run this code, I am getting an access violation error because the instruction xName.Area returns nil.
I have been investigating in XLSReadWriteII 5 source code and it seems that my named range are not recognized as Area as the attribute FSimpleName is equal to xsntNone and not xsntArea. I canont figure why though.
Can anyone help me understand?

[OlivierR]

Any clues? Anyone?
It is becoming a bit urgent.

Thanks,

[larsa]

Hello

You have to use the Definition property of the name if you want to read names with formulas. Simple names are names that are either a cell reference or a cell ares.

[OlivierR]

Hi Lars,

Thank you for your answer.
I have understood that the Definition property returns the formula used to set the dimension of the named range but I can't figure out how to use it to get the sheet index, the columns and the rows. Can you help me once more?

Here is an example of definition I have to deal with: OFFSET([ExternSheet]$G$115;0;0;3;3*[EXTERNNAME]+4)
ExternSheet and EXTERNAME are not my variables but do replace an excelsheet name and a named cell name.

Thank you in advance,

Olivier

[OlivierR]

I am still stuck.
Can anyone help me?

[larsa]

Hello

If you have the sheet name, you can find it's index with this:

Code: Select all

  Sht := XLS.SheetByName('MySheet');
  i := Sht.Index;

[OlivierR]

It does not work.
So now to solve this problem I don't use XLSReadWrite but directly EXCEL_TLB.

Olivier.