How to find the named range of a cell - VSTO

Question

I have generated a series of rows using C# and VSTO. I have basically loaded a couple of rows with data and have given each cell a NamedRange. My question is how would I, knowing the beginning row and end row index, traverse each cell and retrieve it's NamedRange. I've tried Excel.Range range = (Excel.Range)m_worksheet.Cells[x,y]; which gets the range fine, but then when I do a range.Name.ToString(); I get "System.__COM...." instead of the name. Can anyone assist?

Thanks

Answer 1

You need loop through Names collection to find the namedrange

Answer 2

Here is the sample code how you can iterate through named range in Excel.

private Excel.Workbook m_workbook;
object missing = Type.Missing;

   public void testNamedRangeFind()
    {
        m_workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
        int i = m_workbook.Names.Count;
        string address = "";
        string sheetName = "";

        if (i != 0)
        {
            foreach (Excel.Name name in m_workbook.Names)
            {
                string value = name.Value;
                //Sheet and Cell e.g. =Sheet1!$A$1 or =#REF!#REF! if refers to nothing
                string linkName = name.Name;
                //gives the name of the link e.g. sales
                if (value != "=#REF!#REF!")
                {
                    address = name.RefersToRange.Cells.get_Address(true, true, Excel.XlReferenceStyle.xlA1, missing, missing);
                    sheetName = name.RefersToRange.Cells.Worksheet.Name;
                }
                Debug.WriteLine("" + value + ", " + linkName + " ," + address + ", " + sheetName);
            }
        }

    }

Answer 3

I know this sucker is old but I just needed this answer so now that I have it I will share: When you build your named ranges you want to handle their Change event, In that handler you'll need some code like this:

foreach (Excel.Name name in Globals.ThisWorkbook.Name)
{
if (Application.Intersect(name.RefersToRange, Target) != Null) //Target is the single parameter of our handler delegate type.
{
// FOUND IT!!!!
}
}

Application.Intersect determines the intersection of 2 ranges, and returns null if it doesn't find one.