How to use Application.Names in the xlwings API way

The Names member of the Application object in Excel’s object model is a powerful collection that represents all the defined names within a workbook or application scope. In xlwings, this is accessed through the app.names property. Defined names are essentially named ranges or constants that make formulas more readable and dynamic. They can refer to a single cell, a range of cells, a constant value, or even a formula. Using the Names collection via xlwings allows you to programmatically create, modify, retrieve, and delete these names, which is crucial for building robust, maintainable Excel-based automation and data models.

The primary syntax in xlwings for interacting with this collection is through the app.names property, which returns a Names collection object. You can also access it via a specific workbook: wb.names. Key methods and properties include:

• add(name, refers_to): Creates a new defined name. name is a string for the name (cannot contain spaces and must begin with a letter or underscore). refers_to is a string defining the reference, using standard Excel notation (e.g., "=Sheet1!$A$1:$D$10" or "=5" for a constant).
• item(index_or_name): Returns a specific Name object, either by its string name or its numerical index in the collection.
• count: Returns the number of defined names in the collection.
• On a Name object, key properties are name (to get or set the name text) and refers_to (to get or set the reference formula string). The delete() method removes the name.

Here are practical xlwings code examples demonstrating the use of the Application.Names member:

import xlwings as xw

# Connect to the active Excel instance and its active workbook
app = xw.apps.active
wb = app.books.active

# Example 1: Adding a new defined name for a range
# This creates a name "DataRange" referring to cells A1:D20 on Sheet1
wb.names.add(name="DataRange", refers_to="=Sheet1!$A$1:$D$20")

# Example 2: Adding a named constant
# This creates a name "TaxRate" with a constant value of 0.075
app.names.add(name="TaxRate", refers_to="=0.075")

# Example 3: Retrieving and inspecting a defined name
# Get a specific name object and print its details
try:
    data_name = wb.names["DataRange"]
    print(f"Name: {data_name.name}")
    print(f"Refers to: {data_name.refers_to}")
except KeyError:
    print("Name not found.")

# Example 4: Iterating through all defined names in the workbook
print(f"\nTotal names in workbook: {wb.names.count}")
for name_obj in wb.names:
    print(f" - {name_obj.name}: {name_obj.refers_to}")

# Example 5: Modifying an existing name's reference
# Change the "DataRange" to refer to a dynamic range using the OFFSET function
data_name = wb.names["DataRange"]
data_name.refers_to = "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)"

# Example 6: Deleting a defined name
app.names["TaxRate"].delete()