8

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?

Aaron Thoma
  • 3,820
  • 1
  • 37
  • 34
Shark
  • 2,322
  • 5
  • 31
  • 44

3 Answers3

16

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

ws = name.RefersToRange.Parent.name;
Raymond Hettinger
  • 216,523
  • 63
  • 388
  • 485
  • 1
    I need to cast it to a Worksheet type before I can call the name property. Otherwise, this worked like intended. – Shark Nov 14 '11 at 21:26
  • 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. – Robert G. Schaffrath May 27 '13 at 11:26
5

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

Aaron Thoma
  • 3,820
  • 1
  • 37
  • 34
0
wb.Names(name).RefersToRange.Parent.Name
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115