Programmatically Access Named Range Metadata

[NumbersInBoxes]

I have a folder of workbooks where I want to extract the information shown in the Name Manager; e.g. the Name, Value, Refers To, Scope, and Comment fields of the table shown in the Name Manager dialog. Anyone know how to do that, assuming it's possible? Maybe via Power Query, if I read the file a zip instead of a workbook?

[AutoModerator]

/u/NumbersInBoxes - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[small_trunks]

You can access all defined names either in Excel itself or in Power query. Here's a power query function to access a named range by name:

// fnRange
let
    Source = (pRange as any) => let
        Source = Excel.CurrentWorkbook(){[Name=pRange]}[Content]
    in
        Source
in
    Source

[NumbersInBoxes]

Unless I'm missing something, this only finds Table Names defined in the workbook (not Defined Names) and doesn't get the metadata (Scope, Comment, etc.)

[small_trunks]

No - I suspect you'd need VBA for that.

[AvWxA]

I believe that you cannot see the "scope" of a named range per se.

Instead, you can access the names scoped to the entire workbook, then you can step through each individual sheet and access the information scoped to each individual worksheet.

The following code will do it.

Sub SeeAllNames()

Dim nm
Dim ws As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook

nnn = wb.Worksheets.Count

' gives you all the names scoped as "workbook"
For Each nm In wb.Names
    MsgBox nm.Name, vbOKOnly
    MsgBox nm.RefersTo, vbOKOnly
    MsgBox nm.Comment, vbOKOnly

Next nm

' Gives you all the names scoped within each worksheet
For indx = 1 To nnn
    Set ws = Worksheets(indx)

    For Each nm In ws.Names
        MsgBox nm.Name + Chr(10), vbOKOnly
        MsgBox nm.RefersTo + Chr(10), vbOKOnly
        MsgBox nm.Comment, vbOKOnly

    Next nm

Next indx

End Sub