Ranges with the scope of a specific sheet

Aspose.Cells Product Family
1

Hi,


I am trying to get all the ranges from an Excel document.
One of the ranges has the scope of the whole workbook, the other one of a specific worksheet.

When I call the objWorkbook.Worksheets.GetNamedRanges() it returns one range (the whole workbook scoped one), but not the specific one.
So I tried to call objWorkbook.Worksheets(n).Cells.Ranges.Count, but for both the worksheets it return 0.

Am I quering the incorrect collection to get the ranges for a specific worksheet?

I have include my test excel as an example, and this is the simple code to query if the range name existst:
   Dim objWorkBook As Workbook = Nothing

objWorkBook = New Workbook(IO.Path.Combine(InputPath, strInputFileName))

Dim objWorksheet As Worksheet = objWorkBook.Worksheets(strWorksheetName)

    <span style="color:blue;">If</span> objWorksheet <span style="color:blue;">Is</span> <span style="color:blue;">Nothing</span> <span style="color:blue;">Then</span>
        <span style="color:blue;">Throw</span> <span style="color:blue;">New</span> <span style="color:#2b91af;">Exception</span>(<span style="color:blue;">String</span>.Format(<span style="color:#a31515;">"Worksheet with name {0} could not be found"</span>, strWorksheetName))
    <span style="color:blue;">End</span> <span style="color:blue;">If</span>

    <span style="color:blue;">Dim</span> objRanges <span style="color:blue;">As</span> <span style="color:#2b91af;">RangeCollection</span> = objWorksheet.Cells.Ranges()

    <span style="color:blue;">For</span> <span style="color:blue;">Each</span> objRange <span style="color:blue;">As</span> <span style="color:#2b91af;">Range</span> <span style="color:blue;">In</span> objRanges
        <span style="color:blue;">If</span> objRange.Name = strRangeName <span style="color:blue;">Then</span>
            <span style="color:blue;">Return</span> <span style="color:blue;">True</span>
        <span style="color:blue;">End</span> <span style="color:blue;">If</span>
    <span style="color:blue;">Next</span>

    <span style="color:blue;">Return</span> <span style="color:blue;">False</span></pre></div><div><div><br></div><div>Kind regards,</div><div><br></div><div>Sjoerd van Loon</div></div>
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.2.3


Please use the Workbook.Worksheets.Names property to retrieve all the named ranges in your workbook having workbook or sheet scope.

Please see the following code and its output

VB.NET


Dim filePath As String = “F:\Shak-Data-RW\Downloads\CellRanges.xlsx”


Dim workbook As Workbook = New Workbook(filePath)


Dim names As NameCollection = workbook.Worksheets.Names


Debug.WriteLine("Ranges Count: " & names.Count)



Output:
Ranges Count: 2
3

Hi,


And here is the simplest sample code to retrieve all the named ranges in the workbook using Names and Name objects for your reference:
Sample code:

Dim workbook1 As New Workbook(“e:\test2\cellranges.xlsx”)
For Each range As Name In workbook1.Worksheets.Names
Debug.WriteLine("------------------------------------")
Debug.WriteLine(“Refer To: " & range.RefersTo)
Debug.WriteLine(“Text: " & range.Text)
Debug.WriteLine(”------------------------------------”)
Next range
4

Hi,


Thank you very much I will use this code.

But what is the purpose behind the Cells.Ranges collection then?

Kind regards,

Sjoerd van Loon
5

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Sometimes, dynamic ranges are created and added using workbook.Worksheets[0].Cells.AddRange() method, these ranges are nameless, mostly their purpose is to set the style of the ranges of cell, Ranges collection hold such ranges.