How to use Range.CreateNames in the xlwings API way

The CreateNames method in the Excel object model is a powerful feature for automating the creation of named ranges from a selection. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This method is particularly useful when you have a range where the top row, left column, bottom row, or right column contains labels that you want to use as names for adjacent cells or ranges. By using CreateNames, you can programmatically define these names, enhancing workbook organization and making formulas more readable and maintainable.

Functionality
The CreateNames method generates named ranges based on the labels located at the edges of a specified range. It examines the selected range and uses the labels in the top row, left column, bottom row, or right column to create names for the contiguous cells in the opposite direction. For example, if you have labels in the top row, it will create names for the columns below each label. This is especially beneficial in scenarios like financial models or data tables where you need to assign descriptive names to data blocks dynamically.

Syntax
In xlwings, you invoke CreateNames via the api property of a Range object. The method signature is:

range.api.CreateNames(Top, Left, Bottom, Right)

• Top (Optional, Boolean): If True, uses labels in the top row to create names. Default is False.
• Left (Optional, Boolean): If True, uses labels in the left column to create names. Default is False.
• Bottom (Optional, Boolean): If True, uses labels in the bottom row to create names. Default is False.
• Right (Optional, Boolean): If True, uses labels in the right column to create names. Default is False.

All parameters are optional, and if none are specified, Excel will guess the label positions based on the range’s layout. Typically, you set one of these to True depending on where your labels are located. Note that the method corresponds to the “Create from Selection” feature in Excel’s Formulas tab.

Examples
Here are practical xlwings API code instances demonstrating the use of CreateNames:

1. Creating names from top row labels: Suppose you have a range where the first row contains headers like “Revenue”, “Expenses”, and you want to name the data below each header.

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Define the range including headers and data (e.g., A1:C10)
target_range = sheet.range('A1:C10')

# Use CreateNames with Top=True to generate names based on top row
target_range.api.CreateNames(Top=True)

# This will create named ranges like "Revenue" for A2:A10, "Expenses" for B2:B10, etc.

2. Creating names from left column labels: If your labels are in the first column and you want to name the cells to the right.

import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['Data']

# Range with labels in column A and data in columns B onward (e.g., A1:D5)
target_range = sheet.range('A1:D5')

# Use CreateNames with Left=True
target_range.api.CreateNames(Left=True)

# This creates names like "Jan" for B1:D1 if A2 contains "Jan", and so on.

3. Combining multiple directions: In complex tables, you might have labels on both top and left. You can call CreateNames multiple times or use it with caution, as overlapping names may cause conflicts. Here’s an example for top and left:

import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Assume a table with top row headers and left column labels
target_range = sheet.range('A1:E6')

# First, create names from top row
target_range.api.CreateNames(Top=True)
# Then, create names from left column (if needed, but note potential overwrites)
target_range.api.CreateNames(Left=True)

# It's advisable to manage ranges separately to avoid errors.

4. Using without parameters for automatic detection: If the range has a clear layout, you can let Excel decide.

import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['Report']

# Select a range where labels are intuitively placed (e.g., A1:B10 with headers in row 1)
target_range = sheet.range('A1:B10')
target_range.api.CreateNames() # Excel guesses label positions

# This is useful for quick automation but may require verification in complex cases.