CODEWITHSUNDEEP
excelvba
user11889334
user11889334

Reputation: 149

Specific named range does not exist in .Names

I am looping through both the workbook and worksheet .Names collection of a workbook that I have opened remotely. For some reason, some of the named ranges in that workbook are not included in the .Names collection. If I open the workbook and go to the name manager, I can see them in there. But they don't come through when I loop through the collection:

Dim app As New Excel.Application
Dim book As Excel.Workbook, wbReport As Excel.Workbook
Set book = app.Workbooks.Open(filePath)
app.visible = False

For Each nr In book.Names
    Debug.Print nr.Name
Next nr
For Each ws In book.Worksheets
    Debug.Print ws.Name
    For Each wsnr In ws.Names
        Debug.Print wsnr.Name
    Next
Next ws

Please note that the file path is definitely correct and I do see some of the named ranges but not others.

Any ideas?

Upvotes: 2

Views: 643

Answers (1)

MGP
MGP

Reputation: 2551

As mentioned in the comments, Worksheet.Names will only return a collection of the named ranges. If there are additionally named tables in the worksheet, then one has to loop through the Worksheet.ListObject's as well.

Upvotes: 2

Related Questions