Get sheet name from a named range's Name object

Question

I have:

Microsoft.Office.Interop.Excel.Workbook wb;
Microsoft.Office.Interop.Excel.Name name;

Is there any way to get the worksheet name that the named range is on in the given workbook, assuming I've gotten the named range's Name object and wb already?

Solution 1

Yes, use the Parent property to work your way up the object hierarchy:

ws = name.RefersToRange.Parent.name;

Solution 2

Range.Worksheet is a self-documenting alternative to Range.Parent:

string wsName = name.RefersToRange.Worksheet.Name;

(Or in 2 steps:

Microsoft.Office.Interop.Excel.Worksheet ws = name.RefersToRange.Worksheet;
string wsName = ws.Name;

)

Reference:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.name.referstorange.aspx
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.worksheet.aspx
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._worksheet.name(v=office.15).aspx

Comments

[Shark]

I need to cast it to a Worksheet type before I can call the name property. Otherwise, this worked like intended.

[Lance Roberts]

He is asking for the worksheet name, not the worksheet object.

[Robert G. Schaffrath]

Note that attempting to access RefersToRange will fail with an error 0x800A03EC if the named range does not reference a sheet. For example, if the name was setup to reference "H2", the RefersTo property will return "=#REF!$H$2". Under those circumstances RefersToRange will fail. You will either need to use a Try/Catch wrapper or check the RefersTo string with a .StartsWith("=#REF!") before checking the property.