How to use Worksheet.OLEObjects in the xlwings API way

The OLEObjects member of the Worksheet object in Excel represents a collection of all OLE objects (such as embedded documents, ActiveX controls, or other insertable objects) on a specific worksheet. In xlwings, this collection is accessible via the api property, which provides direct access to the underlying Excel object model. This allows for programmatic control over embedded objects, including enumeration, modification, and interaction with OLE controls within a workbook.

Functionality:
The primary use of the OLEObjects collection in xlwings is to manage embedded OLE objects. You can count the objects, retrieve a specific object by its index or name, modify properties (e.g., size, placement), or even invoke methods associated with ActiveX controls. This is particularly useful for automating dashboards or forms that contain interactive elements like buttons, list boxes, or embedded charts from other applications.

Syntax:
Accessing the OLEObjects collection in xlwings follows the pattern: worksheet.api.OLEObjects. This returns a collection object. To reference a specific OLE object, you can use:

• worksheet.api.OLEObjects(Index) where Index is the object’s numeric position (1-based) or its name as a string.
• worksheet.api.OLEObjects.Item(Index) which is functionally equivalent.

Common properties and methods of an OLE object (returned from the collection) include:

• Name: Gets or sets the object’s name.
• Left, Top, Width, Height: Control the object’s position and dimensions in points.
• Object: Provides access to the underlying OLE object’s native interface (e.g., for an ActiveX control, you can access its specific properties).
• Delete(): Removes the object from the worksheet.

Code Examples:

1. List all OLE objects on a worksheet:

import xlwings as xw
wb = xw.Book('workbook.xlsx')
ws = wb.sheets['Sheet1']
ole_objects = ws.api.OLEObjects
count = ole_objects.Count
print(f"Number of OLE objects: {count}")
for i in range(1, count + 1):
    obj = ole_objects(i)
    print(f"Object {i}: Name='{obj.Name}', Type='{obj.progID}'")

2. Resize and reposition an OLE object by name:

# Assume an embedded Word document object named "DocObject1"
obj = ws.api.OLEObjects("DocObject1")
obj.Left = 100 # Points from left edge
obj.Top = 50 # Points from top
obj.Width = 200
obj.Height = 150

3. Interact with an ActiveX command button:

# Access the button, then its specific properties via the Object property
button = ws.api.OLEObjects("CommandButton1")
# Change the button caption
button.Object.Caption = "Click Me"
# The Object property exposes the ActiveX control's native interface

4. Delete all OLE objects on a sheet:

ole_objects = ws.api.OLEObjects
for i in range(ole_objects.Count, 0, -1): # Iterate backwards when deleting
    ole_objects(i).Delete()