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;
July 26, 2011 at 3:26 pm |
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!!!
July 26, 2011 at 3:38 pm |
At which point does it throw the exception if you step through in debug mode?
July 26, 2011 at 4:33 pm
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
Thanks
July 26, 2011 at 4:36 pm |
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.
December 22, 2011 at 10:38 pm |
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”.
December 23, 2013 at 5:22 pm |
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.
December 9, 2017 at 2:18 pm |
[…] is the sample code (take from here) how you can iterate through named range in […]
April 13, 2019 at 2:56 am |
Corporate proxy solicitation Regulations governing
Reading Named Ranges in Excel using C# | Bishop On Development