Page 1 of 1
Dynamic named ranges
Posted: Tue Nov 04, 2014 9:13 am
by 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:
where nblines and nbcols are numerical expressions depending on another (static) named cell.
Best regards,
Re: Dynamic named ranges
Posted: Tue Nov 04, 2014 2:02 pm
by larsa
Hello
Yes, that is supported.
Re: Dynamic named ranges
Posted: Wed Nov 05, 2014 1:27 pm
by OlivierR
Thanks !
Re: Dynamic named ranges
Posted: Mon Dec 01, 2014 2:56 pm
by 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?
Re: Dynamic named ranges
Posted: Thu Dec 04, 2014 2:10 pm
by 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?
Re: Dynamic named ranges
Posted: Fri Dec 12, 2014 9:05 am
by OlivierR
Any clues? Anyone?
It is becoming a bit urgent.
Thanks,
Re: Dynamic named ranges
Posted: Fri Dec 12, 2014 1:42 pm
by 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.
Re: Dynamic named ranges
Posted: Tue Dec 16, 2014 4:05 pm
by 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
Re: Dynamic named ranges
Posted: Tue Jan 06, 2015 1:17 pm
by OlivierR
I am still stuck.
Can anyone help me?
Re: Dynamic named ranges
Posted: Tue Jan 20, 2015 7:57 pm
by 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;
Re: Dynamic named ranges
Posted: Wed Feb 25, 2015 5:13 pm
by OlivierR
It does not work.
So now to solve this problem I don't use XLSReadWrite but directly EXCEL_TLB.
Olivier.