jon_rs
asked on
Getting Excel Names using OLE Automation
I am working on a Delphi project (in XE3) that links to Excel spreadsheets (Excel 2013/365) using OLE Automation.
I am successfully connecting and reading/writing information to cells in a variety of workbooks and worksheets.
I want to be able to read in the list of defined Names that are present in an Excel workbook, and have written the following function to read them in.
I can successfully get the NUMBER of defined names, but when I try and access the Nth element I get an OLE exception 'Member not found'.
I can successfully ADD more name definitions into the list.
So basically .Names.Count exists, but .Names[n] does not
Does anyone know what the correct syntax is to access the Nth defined name?
Thanks
Jon
I am successfully connecting and reading/writing information to cells in a variety of workbooks and worksheets.
I want to be able to read in the list of defined Names that are present in an Excel workbook, and have written the following function to read them in.
I can successfully get the NUMBER of defined names, but when I try and access the Nth element I get an OLE exception 'Member not found'.
I can successfully ADD more name definitions into the list.
So basically .Names.Count exists, but .Names[n] does not
Does anyone know what the correct syntax is to access the Nth defined name?
Thanks
Jon
function TXLrun.ExtractNames(AWorkbook : TXLworkbook) : boolean;
var
Nobj : Variant;
Robj : Variant;
Ncount : integer;
n : integer;
begin
Result := false;
try
DebugLog('Extracting named ranges');
{ create an object pointing at source worksheet }
Ncount := AWorkbook.WBobj.Names.Count;
// WORKS TO HERE
// Ncount is set successfully to the number of defined names that exist in the XLS
for n := 1 to Ncount do
begin
AWorkbook.WBobj.Names[n]; //EXCEPTION HERE ... 'Member not found'
DebugLog('Name '+Robj.Name);
end;
for n := 1 to 2 do
AWorkbook.WBobj.Names.Add('Name'+IntToStr(n),'A1'); // THIS LINE WORKS THOUGH ...
DebugLog('Name extraction completed: '+
IntToStr(Ncount)+' Named Ranges');
Result := true;
except on E:Exception do
LogException('TXLrun.ExtractNames',E.Message);
end;
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
probably a zero-origin list. Try from 0 to count-1
Hi Jon,
What are you assigning "AWorkbook.WBobj.Names[n]" to?
Are you able to show Values or Names in that loop?
What are you assigning "AWorkbook.WBobj.Names[n]"
for n := 1 to Ncount do
begin
AWorkbook.WBobj.Names[n]; //EXCEPTION HERE ... 'Member not found'
DebugLog('Name '+Robj.Name);
end;
Are you able to show Values or Names in that loop?
AWorkbook.WBobj.Names[n].Value;
AWorkbook.WBobj.Names[n].Name;
ASKER
jimyX:
Apologies in hacking the code around trying to find a solution the version I posted had an error in the very line that the exception occured:
Code should have read:
aikimark:
It's definitely a 1 origin list ...
Qlemo:
Thanks ... that's the solution:
The exact syntax is:
Using [] throws a 'Method Items not support by Automation object' Exception
Interesting (given your comment about relying or using default action/property) then that
WBobj.Worksheets[n] IS allowed and DOES work
and
WBobj.Worksheets.Item(n) throws an exception.
Jon
Apologies in hacking the code around trying to find a solution the version I posted had an error in the very line that the exception occured:
Code should have read:
Robj := AWorkbook.WBobj.Names[n];
aikimark:
It's definitely a 1 origin list ...
Qlemo:
Thanks ... that's the solution:
The exact syntax is:
Robj := AWorkbook.WBobj.Names.Item(n);
Using [] throws a 'Method Items not support by Automation object' Exception
Interesting (given your comment about relying or using default action/property) then that
WBobj.Worksheets[n] IS allowed and DOES work
and
WBobj.Worksheets.Item(n) throws an exception.
Jon
AWorkbook is a TXLworkbook, and might be implemented different in Delphi than in other languages, so particular modifications to the "default" behaviour of COM objects might apply. Using Worksheets[n] is non-standard, and leads in e.g. PowerShell (exposing the raw COM object) to the same issue as with Names.