How to use Workbooks.Item in the xlwings API way

The Item member of the Workbooks object in Excel’s object model is a property used to access a specific workbook within the Workbooks collection. In xlwings, this functionality is typically accessed through the books property of the App object, which represents the collection of open workbooks. The Item property allows you to retrieve a workbook by its index number (position in the collection) or by its name (as a string). This is essential for programmatically manipulating specific workbooks when multiple workbooks are open, enabling you to set a workbook as the active object, read data, or perform other operations.

Syntax in xlwings:
While xlwings does not explicitly expose an Item method, the books collection behaves similarly. You can access a workbook using indexing or key-based lookup.

• By index (1-based, like Excel VBA): app.books[index]
• By name (workbook filename): app.books[name]

Parameters:

• index: An integer representing the position of the workbook in the books collection. The index starts at 1 for the first workbook opened or referenced.
• name: A string that matches the full name (including extension) of the workbook, such as “Data.xlsx”. If the workbook is saved, you can use the base name without the path if it’s unique among open workbooks.

Examples:

1. Access by Index:
   Suppose you have two workbooks open: “Report.xlsx” (opened first) and “Analysis.xlsx” (opened second). To reference the first workbook:

import xlwings as xw
app = xw.apps.active # Get the active Excel application
first_workbook = app.books[0] # Index 0 in xlwings corresponds to VBA's Item(1)
print(first_workbook.name) # Output: Report.xlsx

Note: xlwings uses 0-based indexing for collections in Python, unlike VBA’s 1-based Item. So app.books[0] is equivalent to Workbooks.Item(1) in VBA.

2. Access by Name:
   To directly access a workbook named “Financials.xlsx”:

import xlwings as xw
app = xw.apps.active
target_workbook = app.books['Financials.xlsx']
target_workbook.activate() # Make it the active workbook

If multiple workbooks have similar names, ensure you use the full filename. This method is case-insensitive on Windows but case-sensitive on macOS.

3. Iterating Through Workbooks:
   You can loop through all open workbooks using the books collection, which internally utilizes the Item property:

import xlwings as xw
app = xw.apps.active
for wb in app.books:
    print(f"Workbook: {wb.name}, Sheets: {[sheet.name for sheet in wb.sheets]}")

This iterates over each workbook and prints its name along with sheet names, demonstrating how Item underpins collection access.

4. Error Handling:
   When accessing by name, if the workbook isn’t open, xlwings raises a KeyError. You can handle this gracefully:

import xlwings as xw
app = xw.apps.active
try:
    wb = app.books['NonExistent.xlsx']
except KeyError:
    print("Workbook not found. Please check if it's open.")