How to use Worksheet.CodeName in the xlwings API way

The CodeName property of a Worksheet object in Excel is a powerful feature that allows developers to assign a unique, programmatic identifier to a sheet. Unlike the Name property, which is the visible tab name a user can change, the CodeName is intended to remain static through the life of the workbook. This makes it an ideal reference in VBA macros and, by extension, in xlwings scripts, as it provides a stable way to target a specific sheet even if a user renames its tab. In xlwings, you access this property directly through the api object, which grants raw access to the underlying Excel object model.

The syntax for accessing the CodeName property in xlwings is straightforward. Since it is a read-only property in the context of xlwings (typically set in the Excel VBA IDE), you primarily retrieve its value. The call format is:

sheet.api.CodeName

Here, sheet is an xlwings Sheet object. The property returns a string representing the sheet’s programmatic name. It’s important to note that while you can read the CodeName via xlwings, changing it programmatically is not directly supported through the standard xlwings API; it is generally set in the Visual Basic for Applications editor (by changing the (Name) property in the Properties window for the sheet) before or during development.

Consider a workbook where you have a data input sheet. In the VBA IDE, you set its CodeName to shDataInput. Even if a user later changes the tab name from “Data” to “Monthly Data”, your xlwings code can still reliably find it. Here is a practical example:

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active

# Method 1: Get a sheet by its CodeName by iterating
target_code_name = "shDataInput"
target_sheet = None
for sheet in wb.sheets:
    if sheet.api.CodeName == target_code_name:
        target_sheet = sheet
        break

if target_sheet:
    print(f"Found sheet with CodeName: {target_sheet.api.CodeName}")
    # Now you can work with the sheet reliably
    target_sheet.range("A1").value = "Updated via CodeName"
else:
    print("Sheet not found.")

# Method 2: A more direct approach using the `api` collection (requires knowing the index/name in the VBA project)
# This is less common but demonstrates the direct COM access.
try:
    # The VBA workbook object has a `Worksheets` collection accessible via `api`
    vba_sheet = wb.api.Worksheets(target_code_name) # This uses the *CodeName* in    the VBA collection
    xw_sheet = xw.Sheet(vba_sheet)
    print(f"Direct access successful. Sheet name (tab): {xw_sheet.name}")
except Exception as e:
    print(f"Direct access failed: {e}")