How to use Worksheet.ConsolidationSources in the xlwings API way

The ConsolidationSources property of the Worksheet object in Excel returns an array of strings that represent the source data ranges for a consolidation on the specified worksheet. This is particularly useful when you have a worksheet that consolidates data from multiple other worksheets or ranges, and you need to programmatically inspect or manage these sources. In xlwings, you can access this property to retrieve this information, enabling automation in scenarios where consolidation settings need to be reviewed or dynamically adjusted.

Functionality:
This property allows you to get the references (as text strings) of all the ranges that are included in a consolidation on the worksheet. It is a read-only property. Each string in the returned array typically follows Excel’s reference style, such as "[WorkbookName]SheetName!Range". This can be used for auditing, logging, or further processing where the consolidation structure is relevant.

Syntax in xlwings:
In xlwings, you access this property via the api property of a Worksheet object, which exposes the underlying Excel object model. The syntax is:

sources = worksheet.api.ConsolidationSources

• worksheet: This is your xlwings Worksheet object.
• .api: This provides access to the native Excel VBA object model.
• .ConsolidationSources: This property returns a Variant array (in Python, it’s typically converted to a tuple or list of strings). If there is no consolidation on the worksheet, it returns None or an empty array.

The property does not take any parameters. The returned array contains each source as a string. The strings represent the references in the format used by Excel (e.g., R1C1 or A1 style, depending on the application’s reference style setting).

Example:
Suppose you have an Excel workbook named “ConsolidatedData.xlsx” with a worksheet named “Summary”. This “Summary” sheet consolidates data from two other sheets: “Data1” and “Data2”, with ranges “A1:C10” on each. You can use xlwings to retrieve these consolidation sources.

import xlwings as xw

# Open the workbook and select the worksheet
wb = xw.Book("ConsolidatedData.xlsx")
summary_sheet = wb.sheets["Summary"]

# Get the consolidation sources
sources = summary_sheet.api.ConsolidationSources

# Check if there are any sources and print them
if sources:
    print("Consolidation Sources:")
    for source in sources:
        print(f" - {source}")
else:
    print("No consolidation sources found on this worksheet.")

# Close the workbook if needed
wb.close()