Reading Named Ranges in Excel using C#

Couldnt find any good examples of reading Named Ranges from a workbook, but eventually figured it out. Here some of the code.

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


The following references are included:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Diagnostics;
using System.Windows.Forms;
using System.IO;


7 Responses to “Reading Named Ranges in Excel using C#”

  1. Nishant Says:

    This is a wonderful piece of work. Works great. But just one issue I am facing is that, this code seems to throw an exception when the named range refers to another file. Is there any workaround for this?

    Many thanks!!!

    • Brian Bishoip Says:

      At which point does it throw the exception if you step through in debug mode?

      • Nishant Says:

        address = name.RefersToRange.Cells.get_Address(true, true, Excel.XlReferenceStyle.xlA1, missing, missing);

        This is where I get the exception.
        The Stack trace is as follows:
        at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
        at Microsoft.Office.Interop.Excel.Name.get_RefersToRange()
        at NamedRangeExcelTest.Program.Main(String[] args) in C:\Misc\Learn\Microsoft.NET\NamedRangeExcelTest\NamedRangeExcelTest\Program.cs:line 60


  2. Brian Bishoip Says:

    Im presuming its actually trying to open this file to get the Name Range details. Maybe catch the exception, and use a different function to open the external file and retrieve its details then. Otherwise, catch the exception, and process whatever details you have access to.

  3. Rock and Roll Says:

    foreach (Excel.Name name in m_workbook.Names)
    will takes more time in executing.

    I duno what will be the best way to handle that “issue”.

  4. ไอพีโบนัส Says:

    Quality articles is the crucial to be a focus for the users
    to pay a quick visit the web site, that’s what this web
    page is providing.

  5. How to find the named range of a cell - VSTO - QuestionFocus Says:

    […] is the sample code (take from here) how you can iterate through named range in […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: