How to find a named cell range - VSTO

Question

I created a series of lines using C # and VSTO. I basically downloaded a couple of lines of data and gave each cell a NamedRange. My question is: how do I, knowing the starting line and the index of the ending line, cross each cell and get its NamedRange. I tried Excel. Range range = (Excel.Range) m_worksheet.Cells [x, y]; which gets the range fine, but then when I do the range .Name.ToString (); I get "System .__ COM ...." instead of a name. Can anyone help?

thanks

Answer 1

Here is a sample code ( from here ) on how you can iterate over a 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 eg =Sheet1!$A$1 or =#REF!#REF! if refers to nothing string linkName = name.Name; //gives the name of the link eg 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 2

I know that this suction cup is old, but I just needed this answer, so now that I have it, I will share: When you create named ranges, you want to handle their Change event, in this handler you will need the following code:

 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 two ranges and returns null if it does not find it.

Answer 3

I used the cast:

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

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

Answer 4

I tried this way: it works if the cell is part of the first sheet, but the moment I click on the cell on another sheet and use this function, I get an exception from HRESULT: error 0x800A03EC

My code is this:

 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; } } 

Answer 5

You need a loop through a collection of names to find namedrange

Answer 6

It 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 the link from here and replaced refereTo, as this gave the actual cell addresses.

Answer 7

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