Archive

How to use Application.CommandBars in the xlwings API way

The CommandBars member of the Application object in Excel’s object model represents the collection of all command bars, which include toolbars and menus, in the application. In modern Excel, command bars are largely superseded by the Ribbon interface, but they remain accessible for compatibility and custom UI development. Using xlwings, you can interact with CommandBars to customize or retrieve information about these UI elements programmatically.

The xlwings API provides a way to access the CommandBars collection through the Application object. The syntax for accessing it is straightforward: app.api.CommandBars. Here, app is an instance of the xlwings App class, which represents the Excel application. The .api property exposes the underlying COM object, allowing direct use of Excel’s VBA object model members. The CommandBars object itself is a collection, and you can reference specific command bars by name or index. For example, app.api.CommandBars("Standard") refers to the Standard toolbar. Key methods and properties include:

• Count: Returns the number of command bars.
• Item(index): Retrieves a specific CommandBar object by index or name.
• Add(Name, Position, MenuBar, Temporary): Creates a new custom command bar. Parameters: Name (string, the bar’s name), Position (integer, e.g., 1 for top, 2 for left), MenuBar (boolean, whether it’s a menu bar), Temporary (boolean, deleted on Excel exit).

To use these, you can call methods directly on app.api.CommandBars. For instance, to add a custom toolbar, you might specify Position as 1 (msoBarTop) from the MsoBarPosition enumeration. Note that xlwings does not have built-in constants for these enumerations; you may need to define them or use their integer values based on Microsoft documentation.

Here is a code example demonstrating the use of CommandBars with xlwings. This script lists all command bars and creates a custom one, adding a button to run a simple macro.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active

# Access the CommandBars collection
command_bars = app.api.CommandBars

# Print the count and names of all command bars
print(f"Total command bars: {command_bars.Count}")
for i in range(1, command_bars.Count + 1):
    cb = command_bars.Item(i)
    print(f" {i}: {cb.Name}")

# Define constants for MsoBarPosition (from Microsoft documentation)
msoBarTop = 1
msoBarLeft = 2
msoBarRight = 3
msoBarBottom = 4
msoBarFloating = 5
msoBarPopup = 6

# Add a custom command bar (toolbar)
custom_bar = command_bars.Add(Name="MyCustomBar", Position=msoBarTop, MenuBar=False, Temporary=True)
print(f"Created custom bar: {custom_bar.Name}")

# Add a button to the custom bar (using CommandBarControls)
# Note: This requires further setup with OnAction to link to a macro
button = custom_bar.Controls.Add(Type=1) # Type 1 is msoControlButton
button.Caption = "Run Macro"
button.TooltipText = "Click to execute a macro"

# Make the custom bar visible
custom_bar.Visible = True

# Clean up: Delete the custom bar (optional, since Temporary=True will remove it on exit)
# custom_bar.Delete()

How to use Application.COMAddIns in the xlwings API way

The Application.COMAddIns property in Excel’s object model provides access to the collection of currently installed COM add-ins. This is particularly useful for developers who need to programmatically check, manage, or interact with these add-ins from within Python using the xlwings library. COM add-ins extend Excel’s functionality, and accessing them via xlwings allows for automation tasks such as verifying if a specific add-in is loaded, enabling or disabling add-ins, or retrieving details about them.

Functionality:
The COMAddIns collection enables you to:

• Count the number of installed COM add-ins.
• Iterate through each COM add-in to get its ProgID (Programmatic Identifier), description, and connection state (whether it is loaded or not).
• Activate or deactivate an add-in programmatically.
• This is essential for ensuring that dependent add-ins are available before executing macros or functions that rely on them, improving script robustness.

Syntax in xlwings:
In xlwings, you access this property through the api property of the App or Application object. The typical call format is:

app = xw.App() # or xw.apps.active for an existing instance
com_addins = app.api.COMAddIns

Once you have the collection, you can use its methods and properties. Key members include:

• Count: Returns the number of COM add-ins (integer).
• Item(index): Retrieves a specific COMAddIn object, where index can be an integer (1-based) or the add-in’s ProgID (string).
• Update(): Updates the list of COM add-ins from the registry.

For a COMAddIn object, important properties are:

• ProgId: The programmatic identifier (string).
• Description: A descriptive name (string).
• Connect: A boolean indicating if the add-in is currently loaded (True/False). Setting this property enables or disables the add-in.

Example Usage:
Below is a practical xlwings code example that demonstrates how to list all COM add-ins and toggle one add-in’s state:

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active

# Access the COMAddIns collection
com_addins = app.api.COMAddIns

# Print the number of add-ins
print(f"Total COM add-ins: {com_addins.Count}")

# Iterate through each add-in and display details
for i in range(1, com_addins.Count + 1):
    addin = com_addins.Item(i)
    print(f"Add-in {i}:")
    print(f" ProgID: {addin.ProgId}")
    print(f" Description: {addin.Description}")
    print(f" Loaded: {addin.Connect}")

# Example: Toggle the state of a specific add-in by ProgID
target_progid = "Example.AddIn" # Replace with an actual ProgID
try:
    specific_addin = com_addins.Item(target_progid)
    current_state = specific_addin.Connect
    specific_addin.Connect = not current_state # Toggle the state
    print(f"Toggled add-in '{target_progid}' from {current_state} to {specific_addin.Connect}")
except Exception as e:
    print(f"Add-in '{target_progid}' not found or error: {e}")

# Optional: Update the collection
com_addins.Update()

How to use Application.Columns in the xlwings API way

The Application.Columns property in the xlwings API provides a powerful way to reference and manipulate entire columns within an Excel application context. It returns a Range object that represents all the columns in the active sheet, or more specifically, it can be used to refer to columns in a general sense, though its direct use is often through the active workbook’s sheets. In xlwings, this is typically accessed via the app object, which represents the Excel Application.

Functionality:
The primary function is to obtain a Range object representing all columns on a worksheet. This is useful for applying formatting, setting column widths, or performing operations across every column. It serves as a starting point for more specific column selections, such as Columns(1) for the first column or Columns("A:C") for a range of columns.

Syntax:
In xlwings, the syntax is:
app.api.Columns
or, more commonly when working with a specific sheet:
sheet.api.Columns

This accesses the underlying Excel VBA Columns property via the api object. The property itself can take an optional index argument to specify which column(s) to return.

• Index (Optional): Can be a column number (integer) or a column letter (string). If omitted, it returns a collection of all columns on the worksheet.
• Example: Columns(1) or Columns("A") returns the first column.
• Example: Columns("A:C") returns columns A through C.

Examples:

1. Set the width of all columns:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
sheet = wb.sheets[0]

# Set width of every column to 15
sheet.api.Columns.ColumnWidth = 15

2. Format the first column (A) with bold font:

sheet.api.Columns(1).Font.Bold = True
# Alternatively using column letter:
sheet.api.Columns("A").Font.Bold = True

3. Hide columns B through D:

sheet.api.Columns("B:D").Hidden = True

4. AutoFit a range of columns:

# AutoFit columns A to E
sheet.api.Columns("A:E").AutoFit()

5. Apply a border to all columns:

from xlwings.constants import LineStyle, BorderWeight
all_columns = sheet.api.Columns
all_columns.Borders.LineStyle = LineStyle.xlContinuous
all_columns.Borders.Weight = BorderWeight.xlThin

Notes:

• Using app.api.Columns directly (without a sheet reference) typically targets the active sheet. It’s more reliable to explicitly reference a sheet object.
• The Columns property is part of the Worksheet object in Excel’s object model. In xlwings, you access it via sheet.api.Columns.
• This property is read-write; you can both retrieve and set properties on the returned Range object.

How to use Application.ClusterConnector in the xlwings API way

The ClusterConnector member of the Application object in Excel’s object model is a specialized interface for managing connections to Power Pivot data models in a clustered environment, such as SQL Server Analysis Services (SSAS) tabular models. In xlwings, this provides programmatic control over how Excel interacts with these external data sources, enabling automation of data refresh and connection management within a workbook. This is particularly useful in enterprise scenarios where data models are hosted on scalable, high-availability servers.

Syntax in xlwings:
The property is accessed through the Application object. In xlwings, the Application is typically represented by the app object when you instantiate Excel.

app.api.ActiveWorkbook.ClusterConnector

Note: ClusterConnector is a property that returns a WorkbookConnection object when the active workbook is connected to a Power Pivot model in a cluster. It is not a method. This property is read-only in the context of xlwings via the Excel API.

Key Functionality and Usage:
The primary purpose is to retrieve the connection string and details of the clustered Power Pivot connection. You can inspect properties like OLEDBConnection.Server and OLEDBConnection.Connection to understand the data source. This allows for verification or logging of connection parameters. It’s important to note that directly modifying the ClusterConnector via xlwings is limited; its main use is for informational purposes or to trigger a refresh of the connected model.

Code Examples:

1. Retrieving Cluster Connection Details:
   This example checks if the active workbook has a cluster connector and prints its server name and connection string.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active

# Ensure a workbook is active
if app.books.active is not None:
    wb = app.books.active
    try:
        # Access the ClusterConnector property
        cluster_conn = wb.api.ClusterConnector

        # Get the OLEDB connection details
        oledb_conn = cluster_conn.OLEDBConnection
        print(f"Server: {oledb_conn.Server}")
        print(f"Connection String: {oledb_conn.Connection}")

        # Check if it's a Power Pivot connection
        if cluster_conn.Type == 5: # xlConnectionTypeOLEDB for Power Pivot
        print("This is a Power Pivot cluster connection.")
    except AttributeError:
        print("No active Power Pivot cluster connection found in this workbook.")

2. Refreshing Data via Cluster Connection:
   This example refreshes all data connections, including the Power Pivot model connected through the cluster.

import xlwings as xw

app = xw.apps.active
wb = app.books.active

# Refresh all data in the workbook, which includes the cluster-connected model
wb.api.RefreshAll()

# Alternatively, refresh a specific connection if known
# wb.connections["YourConnectionName"].Refresh()

Important Notes:

• The ClusterConnector property is only available if the workbook contains a Power Pivot data model connected to an SSAS cluster. Otherwise, accessing it may raise an AttributeError.
• In xlwings, you interact with this through the underlying Excel API (.api attribute), so a solid understanding of the Excel object model is beneficial.
• For automation, common tasks involve refreshing data, but direct manipulation of cluster settings (like changing the server) is typically done through Excel’s UI or server-side configuration, not via this property in xlwings.

How to use Application.ClipboardFormats in the xlwings API way

The Application.ClipboardFormats property in Excel VBA returns an array that lists the data formats currently available on the Clipboard. This is useful for programmatically determining what type of data (e.g., text, bitmap, HTML) has been copied, allowing your xlwings script to handle pasting operations intelligently or to verify clipboard content before proceeding. In xlwings, you access this property through the Application object, which represents the Excel application instance.

Functionality:
The primary function is to inspect the clipboard. It does not alter the clipboard’s contents. By retrieving the array of format IDs, you can check for the presence of specific formats (like xlClipboardFormatText or xlClipboardFormatBitmap) to decide the appropriate course of action in your automation script, such as conditional pasting or data validation.

Syntax in xlwings:

import xlwings as xw

app = xw.apps.active # Or xw.App() for a new instance
formats_array = app.api.ClipboardFormats

• app: This is your xlwings App object.
• .api: This provides direct access to the underlying Excel VBA object model.
• ClipboardFormats: This is the property being called. It takes no arguments.
  The property returns a 1-based array (Variant) containing the integer IDs of the available formats. If the clipboard is empty, it may return None or an empty variant. The specific integer IDs correspond to Excel’s XlClipboardFormat enumeration. Common values include:
• 1: xlClipboardFormatText
• 2: xlClipboardFormatBitmap
• 8: xlClipboardFormatHTML
• -4142: xlClipboardFormatLink (DDE link)

Code Examples:

1. Basic Retrieval and Display:

import xlwings as xw
import sys

app = xw.apps.active
formats = app.api.ClipboardFormats

if formats is not None:
    # Convert the returned COM array to a Python list for easy handling
    # On Windows, the returned object is often a `tuple` when accessed via pywin32.
    format_list = list(formats)
    print(f"Available Clipboard Formats (IDs): {format_list}")
else:
    print("Clipboard is empty or formats cannot be retrieved.")

2. Checking for a Specific Format Before Pasting:
   This example checks if text is available on the clipboard before pasting into a specific cell.

import xlwings as xw

app = xw.apps.active
wb = app.books.active
sht = wb.sheets[0]

formats = app.api.ClipboardFormats

# Check if Text format (ID 1) is present
if formats is not None and 1 in formats:
    sht.range("A1").select() # Select the target cell
    app.api.Selection.PasteSpecial(Paste=-4163) # xlPasteValues = -4163
    # Alternatively, for a more xlwings-native approach after checking:
    # text_data = pyperclip.paste() # Using pyperclip module
    # sht.range("A1").value = text_data
else:
    print("No text format found on clipboard. Pasting aborted.")

3. Logging Available Formats for Debugging:

import xlwings as xw
import logging

logging.basicConfig(level=logging.INFO)
app = xw.apps.active

formats = app.api.ClipboardFormats
if formats:
    logging.info("Clipboard Snapshot:")
    for fmt_id in formats:
        # Map some known IDs for better readability
        format_names = {1: "Text", 2: "Bitmap", 8: "HTML", -4142: "DDE Link"}
        name = format_names.get(fmt_id, f"Unknown ID ({fmt_id})")
    logging.info(f" - {name}")

How to use Application.Charts in the xlwings API way

The Charts member of the Application object in Excel’s object model represents the collection of all chart sheets in a workbook. In xlwings, this is accessed via the api property, which provides direct access to the underlying Excel object model. This allows for advanced chart management, such as adding new chart sheets, modifying existing ones, or iterating through all charts in the application. Using Charts is particularly useful when you need to work with chart sheets specifically, as opposed to embedded charts within worksheets.

Functionality:
The Charts collection enables you to create, access, and manipulate chart sheets. Chart sheets are standalone sheets that contain only a chart, separate from worksheet data. You can add new chart sheets, reference existing ones by name or index, and perform operations like copying, moving, or deleting them. This is essential for automating report generation or dashboard creation where charts need to be organized independently.

Syntax:
In xlwings, you typically access Charts through the workbook or application context. The general syntax is:

• app.api.Charts: Returns the Charts collection for the entire Excel application, including all open workbooks.
• wb.api.Charts: Returns the Charts collection for a specific workbook (where wb is a workbook object).

Key methods and properties include:

• Add([Before], [After], [Count]): Adds new chart sheets. Parameters are optional: Before and After specify the sheet position (as a sheet object), and Count sets the number of sheets to add (default is 1).
• Item(Index): Returns a single Chart object by index (integer) or name (string).
• Count: Property that returns the number of chart sheets in the collection.

For parameters like Before and After, you can use sheet references, such as wb.sheets['Sheet1'].api, to position the new chart sheet relative to existing sheets.

Examples:
Here are xlwings API code examples demonstrating the use of the Charts member:

1. Adding a new chart sheet to a workbook:

import xlwings as xw

# Connect to an existing workbook or create a new one
wb = xw.Book('example.xlsx')
app = xw.apps.active

# Add a new chart sheet named "SalesChart" at the end
new_chart = wb.api.Charts.Add()
new_chart.Name = "SalesChart"
print(f"Added chart sheet: {new_chart.Name}")

2. Accessing and iterating through all chart sheets in the application:

import xlwings as xw

app = xw.apps.active

# Count the total chart sheets across all open workbooks
total_charts = app.api.Charts.Count
print(f"Total chart sheets in application: {total_charts}")

# Iterate through each chart sheet and print its name
for chart in app.api.Charts:
print(chart.Name)

3. Creating a chart sheet with specific positioning:

import xlwings as xw

wb = xw.Book('data.xlsx')
# Add a chart sheet before the first worksheet
first_sheet = wb.sheets[0].api
new_chart = wb.api.Charts.Add(Before=first_sheet)
new_chart.Name = "AnalysisChart"

# You can then use the Chart object to set data sources or formats
# For example, set a chart type (assuming data is prepared)
new_chart.ChartType = 51 # 51 corresponds to xlLineMarkers in Excel constants

4. Deleting a chart sheet by name:

import xlwings as xw

wb = xw.Book('report.xlsx')
# Delete a chart sheet named "OldChart"
try:
    wb.api.Charts("OldChart").Delete()
    print("Deleted chart sheet: OldChart")
except Exception as e:
    print(f"Error: {e}")