How to use Range.AutoComplete in the xlwings API way

The AutoComplete member of the Range object in Excel is a property that enables or disables the automatic completion feature for a specific cell. This feature, when active, suggests possible entries based on previously entered data in the same column, helping to ensure consistency and speed up data entry. It is particularly useful in scenarios where users are inputting repetitive data, such as product names, categories, or employee IDs, as it reduces typing errors and minimizes the need for manual lookups. In xlwings, the AutoComplete property is accessed through the api property of a Range object, providing direct interaction with the underlying Excel object model.

The syntax for using AutoComplete in xlwings is straightforward. It is a read-write property that accepts a Boolean value. The xlwings API call format is range.api.AutoComplete = value, where range is an xlwings Range object representing a single cell (since AutoComplete applies per cell), and value is either True to enable automatic completion or False to disable it. For example, to check the current setting, you can retrieve it with current_setting = range.api.AutoComplete. It is important to note that this property is specific to Excel’s application-level settings for a cell and does not require additional parameters.

Below are practical xlwings API code examples demonstrating the use of the AutoComplete property. First, ensure xlwings is installed and an Excel workbook is open or created via xlwings.

import xlwings as xw

# Connect to an existing workbook or create a new one
wb = xw.Book() # Opens a new workbook
sheet = wb.sheets['Sheet1']

# Example 1: Enable AutoComplete for a specific cell (e.g., A1)
range_a1 = sheet.range('A1')
range_a1.api.AutoComplete = True
print(f"AutoComplete enabled for A1: {range_a1.api.AutoComplete}")

# Example 2: Disable AutoComplete for another cell (e.g., B1)
range_b1 = sheet.range('B1')
range_b1.api.AutoComplete = False
print(f"AutoComplete enabled for B1: {range_b1.api.AutoComplete}")

# Example 3: Check the AutoComplete status for multiple cells
for cell in ['A1', 'B1', 'C1']:
    cell_range = sheet.range(cell)
    status = "Enabled" if cell_range.api.AutoComplete else "Disabled"
    print(f"AutoComplete for {cell}: {status}")

# Example 4: Toggle AutoComplete based on a condition (e.g., enable only if cell is empty)
target_range = sheet.range('D1')
if target_range.value is None:
    target_range.api.AutoComplete = True
    print("AutoComplete toggled to True for empty cell D1")
else:
    target_range.api.AutoComplete = False
    print("AutoComplete toggled to False for non-empty cell D1")

# Save and close the workbook (optional)
wb.save('example_autocomplete.xlsx')
wb.close()