Some name ranges are not showing

Aspose.Cells Product Family
1

Hey.

I am not able to get the all name ranges that are in the excel file.
I am attaching the sample project and the screen shots that differentiate between Aspose and Excel.

You can refer the attached project with sample file as well as the screen shot of excel and Aspose.

NameRange.zip (5.3 MB)

2

@harshCIPL22

Thanks for the sample project, template file and screenshot.

I evaluated your issue a bit using your template file and sample code. I found your so called missing named range is actually a table in Excel worksheet, so you cannot get it in Name collection. Rather you may use objWorkbook.Worksheets.GetNamedRangesAndTables to get both named ranges and tables in one go. Please change your sample code segment accordingly, see the update code segment that work find as I tested:
e.g
Sample code:

........
  If Not IsNothing(objWorkbook) Then
                Me.Cursor = Cursors.WaitCursor

                For Each objName As Range In objWorkbook.Worksheets.GetNamedRangesAndTables
                    arrData(0) = objName.Name
                    arrData(1) = "True"
                    arrData(2) = objName.RefersTo
                    arrData(3) = ""
                    mdtData.Rows.Add(arrData)
                Next
                mdtData.AcceptChanges()

            End If
            watch.Stop()
.......

Hope, this helps a bit.

3

Thanks for the response.
I’ll try and let you know about it.

4

Hey,

I tried your solution which you provided me earlier to use “GetNamedRangesAndTables”.
Actually I am not looking for the Table Ranges , I am looking for the Name Ranges.
The Sheet is having totally 8 name Range and 1 Table Range. From Aspose I showing just 7 Name Range.
If I Use the “GetNamedRangesAndTables”. It gives me the same 7 Name Range and 1 Table Range.
The 1 Name Range is missing that is actually a hidden one.

You can clearly understand with the screen shot that I have attached.

Missing Name Range.png (31.3 KB)

5

@harshCIPL22,

Thanks for the screenshot.

I tested your scenario/ case by opening your file into MS Excel (2007/2013) and found it has total 8 named ranges (7 common named ranges and 1 table range), see the screenshot attached for your reference:
sc_shot1.png (76.9 KB)

Where is the hidden named range, how to show the hidden named range (without using vba codes) in MS Excel manually?

6

Thanks for your description.

Actually I tried to get it without VBA and there is not such a way except the VBA.

7

@harshCIPL22,

I am afraid, if something cannot be done in MS Excel manually (without using VBA codes) or there is no MS Excel options/feature to accomplish the task, Aspose.Cells might not implement it either.