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()