How to find the named range of a cell - VSTO

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 i开发者_开发百科t'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

Here is the sample code (take from here) 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);
            }
        }

    }

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.

I used a cast:

 ((Excel.Name)target.Name).Name

Where "target" is a Microsoft.Office.Interop.Excel.Range; The Name included the name of the sheet.

I tried this way : Works if the cell is part of first sheet, but the moment I click on a cell in some other sheet and use the function gives me Exception from HRESULT: 0x800A03EC Error

My code is this way:

Microsoft.Office.Interop.Excel.Workbook _workbook = Globals.ThisAddIn.Excel.CurrentWorkbook.InteropReference;
            Microsoft.Office.Interop.Excel.Range Target = (Microsoft.Office.Interop.Excel.Range)Globals.ThisAddIn.Application.ActiveCell;
            foreach (Microsoft.Office.Interop.Excel.Name name in _workbook.Names)
            {
                Microsoft.Office.Interop.Excel.Range intersectRange = Globals.ThisAddIn.Excel.CurrentWorkbook.InteropReference.Application.Intersect(Target, name.RefersToRange);

                if (intersectRange != null)
                {
                    rangeName = name.Name;
                    break;
                }
            }

You need loop through Names collection to find the namedrange

This worked for me

        var ranges = activeworkBook.Names;
        int i = 1;
        while (i <= ranges.Count)
        {
            String currentName = ranges.Item(i, Missing.Value, Missing.Value).Name;
            if (currentName.Equals(namedRangeToBeDeleted))
            {
                ranges.Item(i, Type.Missing, Type.Missing).Delete();
            }
            else
            {
                i++;
            }
        }

Took reference from here and replaced the refereTo as that was giving actual cell addresses.

The sample above uses m_workbook.Names. In my case I needed to find named ranges on a specific worksheet (ActiveWorksheet). However, this does not seem to work: the names-list remains empty. When using the workbook to access the named ranges, it DID work. (VS 2015, Office 2016)

this seems to work fairly quickly for me, NamedRange helper does the searching, Range name helper returns the name. here is an example of how im using it basically the user can chose to override the value in the cell or clear it and have the default re-populate the formula. in this example their are 5 different cells that are being tracked...

    private void Cell_Change(Range Target) {
        string formula = Target.Cells.Count > 1 ? Target.Formula[1, 1] : Target.Formula;
        if (formula == "" || formula == " ") {
            string name = this.RangeName(Target);
            if (name.Contains("1")) { Target.Formula = "=default formula"; }
            if (name.Contains("2")) { Target.Formula = "=default formula"; }
            if (name.Contains("3")) { Target.Formula = "=default formula"; }
            if (name.Contains("4")) { Target.Formula = "=default formula"; }
            if (name.Contains("5")) { Target.Formula = "=default formula"; }
        }
    }

    public static String RangeName(this WorksheetBase thisWorkSheet, Excel.Range range)
         => ((Name)NamedRange(thisWorkSheet, range)?.Name).Name ?? "";

    public static NamedRange NamedRange(this WorksheetBase thisWorkSheet, Excel.Range range) 
        => thisWorkSheet.Controls.OfType<NamedRange>().FirstOrDefault(
            nr =>
            nr.RefersToRange.MergeArea.AddressLocal == range.AddressLocal ||
            nr.RefersToRange.AddressLocal == range.AddressLocal);

RangeName can be whatever other helpers you want for returning different criteria.