Articles in this section
Category / Section

How to access entire named range count in a workbook?

1 min read

The named ranges in a workbook is separated into two types based on their values. If value is a valid range, then it is known as known named range. If values is invalid range (i.e., #REF), then it is known as unknown named range. We have maintained two properties to return the count of named ranges with and without unknown named ranges.

 

Property

Description

INames.Count

Returns the count of the known named range.

WorkbookNamesCollection.Count

Returns the count of the named range including unknown named range.

 

To access the entire named range count, the INames object must be type-casted to WorkbookNamesCollection. Please refer the below code example to access the entire named range count in workbook.

C#

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
listView1.Items.Clear();
if (0 == textBox1.Text.Length || !File.Exists(textBox1.Text))
return;
 
IWorkbook Workbook = application.Workbooks.OpenReadOnly(textBox1.Text);
 
if (null != Workbook)
{
WorkbookNamesCollection Names = Workbook.Names as WorkbookNamesCollection;
for (int i = 0; i < Names.Count; i++)
{
NameRangeData Data = new NameRangeData(Workbook.Names[i]);
listView1.Items.Add(Data.ListItem);
}
}
MessageBox.Show("Named ranges count is " + listView1.Items.Count);
Workbook.Version = ExcelVersion.Excel2013;
Workbook.SaveAs(@"../../Output/Output.xlsx");
Workbook.Close();
excelEngine.Dispose();

VB

Dim excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
listView1.Items.Clear()
If 0 = textBox1.Text.Length Or Not File.Exists(textBox1.Text) Then
Return
End If
 
Dim Workbook As IWorkbook = application.Workbooks.OpenReadOnly(textBox1.Text)
 
If Nothing <> Workbook Then
Dim Names As WorkbookNamesCollection =  Workbook.Names as WorkbookNamesCollection 
Dim i As Integer
For i = 0 To Names.Count - 1 Step i + 1
Dim Data As NameRangeData = New NameRangeData(Workbook.Names(i))
listView1.Items.Add(Data.ListItem)
Next
End If
MessageBox.Show("Named ranges count is " + listView1.Items.Count)
Workbook.Version = ExcelVersion.Excel2013
Workbook.SaveAs("../../Output/Output.xlsx")
Workbook.Close()
excelEngine.Dispose()

 

The sample illustrating this behavior can be downloaded here.

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied