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?

Hypogeous

Answer 1

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

ws = name.RefersToRange.Parent.name;

Gilson

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

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. – Surrealism

Answer 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

Phototypography

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

Answer 3

wb.Names(name).RefersToRange.Parent.Name

Nealey