How to get all named ranges in Excel worksheet with xlwings?

Question

I'm using xlwings to read named ranges' values, however, to get the list of the name ranges I manually do it in Excel by the "use formulas" function to paste them into another worksheet which i then copy into my Python program.

I have not found a method in the documentation to do this? Is there a way to do this dynamically?

Answer 1

Use the names collection, see https://docs.xlwings.org/en/stable/api.html#names

import xlwings as xw

book = xw.Book(...)
for name in book.names:
    print(name.name, name.refers_to_range)

Name objects can be tied to the workbook or the sheet. If they have the sheet scope, you'd have to replace book.names with mysheet.names where mysheet is your sheet object.