How do I find all named ranges in VB.NET?

[John Brock]

I am using VB.NET to read Excel workbooks which have various named
ranges, some of which may not exist in any given workbook. I am
trying to get a list of all the range names -- otherwise I need to
use a Try block to avoid throwing an exception when I try to read
data from ranges which aren't there, and this slows things down
considerably.

I am able to find the *number* of named ranges in a particular
workbook using this expression:

xlApp.ActiveWorkbook.Names.Count

(where xlApp is my open Excel application handle, typed as
Microsoft.Office.Interop.Excel.Application). And knowing the names
I can retrieve the ranges themselves using:

xlApp.ActiveWorkbook.Names.Item("MyRangeName").RefersToRange

But try as I might I can't seem to figure out how to get a list of
the actual names! I tried giving the Item property an integer
index, but that throws an exception. Can anyone give me a clue?

(I'd also like to get a list of styles in the workbook, and I
suspect this is the same problem, since I can get the number of
styles defined using xlApp.ActiveWorkbook.Styles.Count).

 

[Harlan Grove]

John Brock wrote...
....

But try as I might I can't seem to figure out how to get a list of
the actual names! I tried giving the Item property an integer
index, but that throws an exception. Can anyone give me a clue?

VB.NET provides an object type for Excel defined names, doesn't it? If
that type were Microsoft.Office.Interop.Excel.Name, then try


Dim n As Microsoft.Office.Interop.Excel.Name

For Each n In xlApp.ActiveWorkbook.Names
MsgBox Prompt:=n.RefersToRange.Address, Title:=n.Name
Next n


If you're coding in Visual Studio, doesn't it provide an object
browser? If so, you should be able to find all the Excel object types
in it.

 

[John Brock]

John Brock wrote...
...

VB.NET provides an object type for Excel defined names, doesn't it? If
that type were Microsoft.Office.Interop.Excel.Name, then try


Dim n As Microsoft.Office.Interop.Excel.Name

For Each n In xlApp.ActiveWorkbook.Names
MsgBox Prompt:=n.RefersToRange.Address, Title:=n.Name
Next n


If you're coding in Visual Studio, doesn't it provide an object
browser? If so, you should be able to find all the Excel object types
in it.

Visual Studio shows me plenty of object types, I just can't get
anything to work.

I tried your suggestion, but it still doesn't work. I didn't want
to throw up messages boxes, so I did it this way:

Dim n As Microsoft.Office.Interop.Excel.Name

For Each n In xlApp.ActiveWorkbook.Names
Console.Debug("Name is: " & n.Name)
Next

I get a COMException exception, with the additional information
"Member not found".

I wonder if this could be related to the fact that I couldn't even
define an enumerator for the Names property:

Dim e as IEnumerator = xlApp.ActiveWorkbook.Names.GetEnumerator

This statement also throws an the same exception, and yet GetEnumerator
is one of the methods that Visual Studio suggests for Names.