How to access entire named range count in a workbook?
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.