Archive

How to use Application.CommandUnderlines in the xlwings API way

The Application.CommandUnderlines property in Excel VBA controls the underline style used for menu command access keys (the underlined letter that, when pressed with the Alt key, activates a command). In the xlwings Python library, which provides a programmatic interface to Excel’s object model, you can access and manipulate this property to adjust the user interface behavior of the Excel application instance. This can be useful for ensuring consistency in application appearance or for automating UI configuration tasks in scripts that interact with Excel via xlwings.

Functionality
The CommandUnderlines property determines whether access key underlines in Excel menus and dialog boxes are always visible, visible only when the Alt key is pressed, or follow the system setting. This is a remnant of older UI conventions but can still be relevant for accessibility or specific user preference scenarios when automating Excel. In xlwings, you can both read the current setting and change it programmatically.

Syntax
In xlwings, you access this property through the app object, which represents the Excel Application. The property is exposed as a simple attribute.

app.api.CommandUnderlines

This property accepts and returns an integer value corresponding to the XlCommandUnderlines enumeration. The primary values are:

Value	Constant (VBA)	Description
0	xlCommandUnderlinesAutomatic	Underlines appear based on the system setting.
1	xlCommandUnderlinesOff	Underlines are never shown.
2	xlCommandUnderlinesOn	Underlines are always shown.

Code Examples
Here are practical examples using xlwings to work with the CommandUnderlines property.

1. Reading the Current Setting:
   This code retrieves the current underline setting and prints a descriptive message.

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active

# Get the current CommandUnderlines setting
current_setting = app.api.CommandUnderlines

# Map the integer value to a description
setting_map = {
0: "Automatic (follows system)",
1: "Off (never shown)",
2: "On (always shown)"
}
description = setting_map.get(current_setting, "Unknown setting")
print(f"Current CommandUnderlines setting: {current_setting} ({description})")

2. Changing the Setting:
   This script changes the setting to always show underlines.

import xlwings as xw

app = xw.apps.active

# Set CommandUnderlines to always show (xlCommandUnderlinesOn)
app.api.CommandUnderlines = 2 # You can also use the constant 2 directly

print("Command underlines are now set to be always visible.")

3. Toggling the Setting Based on Current State:
   A more advanced example that toggles the setting between “On” and “Off”.

import xlwings as xw

app = xw.apps.active

current = app.api.CommandUnderlines
if current == 2: # If currently On
    new_setting = 1 # Turn Off
    print("Toggling command underlines OFF.")
else:
    new_setting = 2 # Otherwise, turn On
    print("Toggling command underlines ON.")

app.api.CommandUnderlines = new_setting

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}")

How to use Application.ChartDataPointTrack in the xlwings API way

The ChartDataPointTrack member of the Application object in the Excel object model is a Boolean property that controls whether data points in charts are tracked when the underlying data changes. When this property is set to True, Excel automatically updates data labels and other data point-related elements to reflect changes in the source data. This is particularly useful in dynamic dashboards or reports where the chart data is frequently updated, as it ensures visual elements remain synchronized without manual intervention. In xlwings, this property can be accessed and modified through the Application object, allowing Python scripts to manage this tracking behavior programmatically.

In xlwings, the syntax for accessing and setting the ChartDataPointTrack property is straightforward. Since it belongs to the Application object, you reference it via the xlwings.App instance. The property is a Boolean, accepting True or False values. Here’s the basic syntax:

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active # Or xw.App() for a new instance

# Get the current value of ChartDataPointTrack
current_setting = app.api.ChartDataPointTrack
print(f"Current setting: {current_setting}")

# Set ChartDataPointTrack to True to enable tracking
app.api.ChartDataPointTrack = True

# Set it to False to disable tracking
app.api.ChartDataPointTrack = False

In this code, app.api provides direct access to the underlying Excel object model, allowing you to use the ChartDataPointTrack property as defined in Excel’s VBA documentation. There are no additional parameters for this property; it’s a simple read/write Boolean. When enabled, it affects all charts in the workbook that are linked to dynamic data sources, ensuring data points update automatically. This can be especially beneficial when combined with other xlwings features for data manipulation, such as updating cell values from Python, as changes will propagate to charts seamlessly.

Here’s a practical example demonstrating the use of ChartDataPointTrack with xlwings. Suppose you have an Excel workbook with a chart that visualizes sales data, and you’re updating the data from Python. By enabling ChartDataPointTrack, you ensure the chart’s data points adjust automatically:

import xlwings as xw
import pandas as pd

# Start or connect to Excel
app = xw.App(visible=True) # Make Excel visible for demonstration
wb = app.books.open('sales_report.xlsx') # Open a workbook with a chart
sheet = wb.sheets['Data']

# Enable ChartDataPointTrack for automatic updates
app.api.ChartDataPointTrack = True
print("Chart data point tracking enabled.")

# Simulate updating the underlying data with new sales figures
new_data = pd.DataFrame({
'Month': ['Jan', 'Feb', 'Mar'],
'Sales': [15000, 18000, 22000]
})
sheet.range('A1').value = new_data # Overwrite the existing data range

# The chart linked to this data range will now update its data points automatically
# For instance, if data labels were showing, they’d reflect the new Sales values

# Optional: Disable tracking after updates if needed
app.api.ChartDataPointTrack = False
print("Tracking disabled after updates.")

# Save and close
wb.save()
app.quit()

How to use Application.Cells in the xlwings API way

The Application object’s Cells member in Excel’s object model is an essential property for accessing and manipulating individual cells or ranges through their row and column indices. In xlwings, this functionality is primarily accessed via the api property, which exposes the underlying Excel object model, allowing for precise control over cell references.

Functionality:
The Cells property returns a Range object representing a single cell, specified by its row and column numbers. This is particularly useful for programmatically referencing cells without relying on A1-style notation, enabling dynamic cell access in loops or calculations based on numerical indices.

Syntax:
In xlwings, you access the Cells property through the Application object’s api. The basic syntax is:

app.api.Cells(RowIndex, ColumnIndex)

• RowIndex (required): An integer specifying the row number of the cell. For example, 1 refers to the first row.
• ColumnIndex (required): An integer specifying the column number of the cell. For example, 1 refers to column A, 2 to column B, and so on. Alternatively, you can use a column letter string, but this is less common in xlwings when using the Cells property directly.

This property is read-write, allowing you to both retrieve and set cell values or properties.

Examples:
Here are practical xlwings API code instances demonstrating the use of the Cells member:

1. Accessing a Single Cell Value:

import xlwings as xw
app = xw.App(visible=False) # Start Excel in the background
workbook = app.books.add() # Create a new workbook
sheet = workbook.sheets.active

# Set value in cell B3 (row 3, column 2) using Cells
sheet.api.Cells(3, 2).Value = "Hello, World!"

# Retrieve the value from cell B3
cell_value = sheet.api.Cells(3, 2).Value
print(cell_value) # Output: Hello, World!

app.quit()

2. Looping Through a Range of Cells:

import xlwings as xw
app = xw.App(visible=False)
workbook = app.books.add()
sheet = workbook.sheets.active

# Populate a 5x5 grid with numbers using Cells in nested loops
for row in range(1, 6):
for col in range(1, 6):
    sheet.api.Cells(row, col).Value = row * col

# Read and print the values from the grid
for row in range(1, 6):
    row_values = [sheet.api.Cells(row, col).Value for col in range(1, 6)]
    print(row_values)

app.quit()

3. Combining Cells with Other Range Properties:

import xlwings as xw
app = xw.App(visible=False)
workbook = app.books.open("example.xlsx") # Open an existing workbook
sheet = workbook.sheets[0]

# Use Cells to define a range from A1 to C3 by specifying start and end cells
start_cell = sheet.api.Cells(1, 1) # A1
end_cell = sheet.api.Cells(3, 3) # C3
range_obj = sheet.api.Range(start_cell, end_cell)

# Apply formatting to the range
range_obj.Interior.Color = 0x00FF00 # Green background
range_obj.Font.Bold = True

workbook.save()
app.quit()

4. Dynamic Cell Reference Based on Variables:

import xlwings as xw
app = xw.App(visible=False)
workbook = app.books.add()
sheet = workbook.sheets.active

# Use variables for row and column indices
target_row = 10
target_column = 5 # Column E
sheet.api.Cells(target_row, target_column).Value = "Dynamic Entry"

# Access adjacent cells using offsets from a base cell
base_cell = sheet.api.Cells(target_row, target_column)
base_cell.Offset(0, 1).Value = "Next Column" # Cell F10
base_cell.Offset(1, 0).Value = "Next Row" # Cell E11

app.quit()

How to use Application.CellDragAndDrop in the xlwings API way

Application.CellDragAndDrop in xlwings enables or disables the ability to drag and drop cells within Excel. This property is particularly useful when automating Excel through xlwings, as it allows developers to control whether users can interactively move cell contents by dragging—a feature that might interfere with automated processes or require restriction in certain applications. By setting this property, you can ensure the Excel environment behaves predictably during script execution.

Syntax and Parameters in xlwings
In xlwings, you access this property via the Application object. The syntax is straightforward:

app = xw.apps.active # Get the active Excel application
app.api.CellDragAndDrop = value

Here, app.api provides direct access to Excel’s underlying COM object model. The CellDragAndDrop property accepts a Boolean value:

• True (default in Excel): Enables cell drag-and-drop functionality.
• False: Disables cell drag-and-drop, preventing users from moving cells by dragging.

This property is read/write, meaning you can both retrieve its current state and modify it. Note that changes apply to the entire Excel instance and persist until altered again or Excel is restarted. In xlwings, using .api is essential because CellDragAndDrop is a native Excel VBA property not directly wrapped by xlwings’ high-level API.

Code Examples
Below are practical xlwings examples demonstrating how to use CellDragAndDrop:

1. Disabling drag-and-drop to prevent user interference during automation:

import xlwings as xw

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

# Disable cell drag-and-drop
app.api.CellDragAndDrop = False
print("Cell drag-and-drop disabled.")

# Perform automated tasks, e.g., data manipulation
wb = app.books.active
sheet = wb.sheets[0]
sheet.range("A1").value = "Data processing in progress..."

# Re-enable drag-and-drop after completion
app.api.CellDragAndDrop = True
print("Cell drag-and-drop re-enabled.")

2. Checking the current state and toggling the setting:

import xlwings as xw

app = xw.apps.active

# Get the current setting
current_setting = app.api.CellDragAndDrop
print(f"Current CellDragAndDrop setting: {current_setting}")

# Toggle the setting
app.api.CellDragAndDrop = not current_setting
print(f"Toggled to: {app.api.CellDragAndDrop}")

3. Using in a context manager to temporarily disable drag-and-drop:

import xlwings as xw
from contextlib import contextmanager

@contextmanager
def disable_cell_drag_and_drop(app):
"""Temporarily disable cell drag-and-drop."""
original_setting = app.api.CellDragAndDrop
app.api.CellDragAndDrop = False
try:
    yield
finally:
    app.api.CellDragAndDrop = original_setting

# Usage
app = xw.apps.active
with disable_cell_drag_and_drop(app):
# Perform operations without drag-and-drop interference
app.books.active.sheets[0].range("A1:A10").value = [[i] for i in range(10)]
print("Data written with drag-and-drop disabled.")