Blog

How to use Application.PrintCommunication in the xlwings API way

The Application.PrintCommunication property in Excel is a Boolean value that controls whether Excel sends print settings to the printer driver before printing a document. This communication is essential for ensuring that page layout, scaling, and other printer-specific settings are correctly applied. When set to True (the default), Excel communicates these settings during the print process. Setting it to False can improve performance in certain scenarios, such as when programmatically generating many reports where print settings are static, as it prevents Excel from repeatedly querying the printer driver. However, disabling it may lead to incorrect print output if the document relies on dynamic printer feedback for proper formatting.

In the xlwings API, this property is accessed through the Application object. The syntax for getting or setting its value is straightforward, as it maps directly to the underlying Excel object model.

Syntax in xlwings:

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

• app: An xlwings App object representing the Excel application instance.
• .api: Provides direct access to the underlying Excel object model (pywin32 on Windows, appscript on Mac).
• PrintCommunication: The property name. It accepts a Boolean value: True to enable print communication (default), False to disable it.

Important Considerations:

• This property is primarily useful for advanced automation where print performance is critical. For most standard tasks, it should remain True.
• When set to False, ensure that all print settings (like PageSetup properties) are explicitly defined in your code to avoid layout issues.
• The property is application-wide, affecting all workbooks within that Excel instance.

Code Example:
The following xlwings script demonstrates how to disable PrintCommunication before batch printing multiple worksheets, then re-enable it. This can speed up operations when printer settings are consistent.

import xlwings as xw

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

# Disable print communication for performance
app.api.PrintCommunication = False

try:
    # Access the active workbook
    wb = app.books.active

    # Set common print settings (example: set all sheets to landscape)
    for sheet in wb.sheets:
        sheet.api.PageSetup.Orientation = 2 # xlLandscape

        # Print all worksheets (adjust printer name or settings as needed)
        for sheet in wb.sheets:
            sheet.api.PrintOut(Copies=1, Collate=True)

finally:
    # Re-enable print communication to ensure normal Excel behavior
    app.api.PrintCommunication = True
    print("Print communication restored to default (True).")

How to use Application.PreviousSelections in the xlwings API way

The PreviousSelections property of the Application object in Excel is a powerful feature for tracking and managing the last four cells or ranges that were selected by the user or set via VBA. In xlwings, this functionality is accessible through the api property, which provides direct access to the underlying Excel object model. This property is read-only and returns an array of Range objects, making it useful for scenarios where you need to revert to previous selections, analyze user navigation patterns, or create custom undo features for specific actions within a workbook.

Functionality
The primary function of PreviousSelections is to store a history of the most recent selections in the Excel application. Each time a new cell or range is selected, the list updates, with the oldest entry being removed when the history exceeds four items. This is particularly valuable in complex macros or automated processes where you might need to reference or return to a prior location after performing intermediate operations.

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

import xlwings as xw
app = xw.apps.active
previous_selections = app.api.PreviousSelections

Here, app.api bridges to Excel’s COM interface. PreviousSelections returns a one-based array (index starting at 1) of Range objects. You can iterate through this array or access individual items by index. Note that if there are fewer than four previous selections, the array will contain only the available items, and attempting to access an index beyond the count may result in an error.

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

1. Listing All Previous Selections:
   This code prints the address of each stored range. It checks the count of items to avoid errors.

import xlwings as xw
app = xw.apps.active
selections = app.api.PreviousSelections
for i in range(1, selections.Count + 1):
    range_obj = selections(i)
    print(f"Selection {i}: {range_obj.Address}")

2. Accessing the Most Recent Previous Selection:
   To get the immediate previous selection (the second item in the list, as the first is the current selection), you can index directly. Note that indexing starts at 1.

import xlwings as xw
app = xw.apps.active
# Get the most recent previous selection (index 2 if current is index 1)
recent_selection = app.api.PreviousSelections(2)
if recent_selection:
    print(f"Recent previous selection: {recent_selection.Address}")

3. Using PreviousSelections in a Macro-Like Function:
   This example saves the current selection, performs an operation, and then returns to the saved selection using the history.

import xlwings as xw
def example_previous_selections():
app = xw.apps.active
# Assume user has selected a range before running this
original_range = app.api.PreviousSelections(1) # Current selection
# Perform some operation, e.g., select another range
app.range("A10").select()
# Now, revert to the original selection using PreviousSelections
# Since the selection changed, original_range is now in index 2
reverted_range = app.api.PreviousSelections(2)
reverted_range.select()
print(f"Reverted to: {reverted_range.Address}")
example_previous_selections()

How to use Application.PivotTableSelection in the xlwings API way

The PivotTableSelection member of the Application object in Excel’s object model is a property that returns a Range object representing the current selection within a PivotTable report. This is particularly useful when automating Excel with xlwings, as it allows you to programmatically identify and interact with the specific cells, fields, or data items that a user has selected in an active PivotTable. This property is read-only and only returns a valid range if the selection is within a PivotTable; otherwise, it may return None or cause an error if accessed when no PivotTable is active. In xlwings, you can access this property to perform tasks such as analyzing selected data, applying formatting, or extracting values based on user interaction within PivotTables.

Syntax in xlwings:
The property is accessed through the xlwings App object, which corresponds to the Excel Application object. The typical call format is:

selection_range = xlwings.apps.active.api.PivotTableSelection

Here, xlwings.apps.active gets the active Excel application instance, and .api provides direct access to the underlying Excel object model. The .PivotTableSelection property returns a Range object from the Excel API. If no PivotTable is selected or active, this may return None or raise an error, so error handling is recommended.

Parameters:
This property does not take any parameters. However, its behavior depends on the current Excel selection context. Key considerations include:

• Active Selection: Must be within a PivotTable report. If a regular worksheet range is selected, the property may not return a meaningful value.
• Return Type: Returns an Excel Range object, which in xlwings can be used with properties like .address, .value, or .formula to get details.
• Error Handling: Always check if the returned value is not None to avoid runtime errors.

Example Usage in xlwings:
Below are code examples demonstrating how to use PivotTableSelection with xlwings for common automation tasks.

1. Getting the Address of the Current PivotTable Selection:
   This example retrieves the address of the selected range within a PivotTable and prints it to the console. It includes basic error handling to check if a valid selection exists.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active
try:
    # Access the PivotTableSelection property
    pivot_selection = app.api.PivotTableSelection
    if pivot_selection is not None:
        address = pivot_selection.Address
        print(f"Selected PivotTable range: {address}")
    else:
        print("No PivotTable is currently selected.")
except Exception as e:
    print(f"Error accessing PivotTable selection: {e}")

2. Extracting Values from the Selected PivotTable Range:
   This example reads the values from the selected PivotTable range and processes them, such as calculating a sum or performing data analysis.

import xlwings as xw

app = xw.apps.active
try:
    pivot_selection = app.api.PivotTableSelection
    if pivot_selection is not None:
        # Get values as a Python list of lists
        values = pivot_selection.Value
        if values:
            total = sum(sum(filter(None, row)) for row in values if isinstance(row, (list, tuple)))
            print(f"Sum of selected PivotTable values: {total}")
        else:
            print("No data in the selected range.")
    else:
        print("Selection is not within a PivotTable.")
except Exception as e:
    print(f"Error: {e}")

3. Applying Formatting to the Selected PivotTable Area:
   Here, the code applies formatting (e.g., bold font and background color) to the selected range in the PivotTable to highlight user-selected data.

import xlwings as xw

app = xw.apps.active
try:
    pivot_selection = app.api.PivotTableSelection
    if pivot_selection is not None:
        # Apply formatting via the Excel API
        pivot_selection.Font.Bold = True
        pivot_selection.Interior.Color = 65535 # Yellow color
        print("Formatting applied to the selected PivotTable range.")
    else:
        print("Cannot apply formatting; no PivotTable selected.")
except Exception as e:
    print(f"Formatting error: {e}")

How to use Application.PathSeparator in the xlwings API way

The PathSeparator property of the Application object in Excel returns a string that represents the character used as the path separator in file paths for the current operating system. This property is particularly useful when writing cross-platform Excel automation scripts, as the path separator differs between Windows (which uses a backslash \) and macOS (which uses a colon :). By using PathSeparator, developers can dynamically construct file paths that are compatible with the system where the Excel application is running, enhancing code portability and reducing errors related to path handling.

In xlwings, the Application object is accessed through the app property of a Book object or directly via xw.apps. The PathSeparator property is read-only and can be retrieved as a string. The syntax for accessing it in xlwings is straightforward, as it mirrors the Excel object model but within Python’s context.

Syntax in xlwings:

application_object.path_separator

• application_object: This is an instance of the Application object in xlwings, typically obtained from xw.apps (e.g., xw.apps.active to reference the currently active Excel instance) or from the app property of a workbook (e.g., wb.app where wb is a Book object).
• path_separator: This property returns a string representing the path separator character. No parameters are required, as it is a property, not a method.

Code Examples:

1. Basic Retrieval of Path Separator:
   This example demonstrates how to get the path separator from the active Excel application using xlwings. It prints the separator, which helps in understanding the current system’s path format.

import xlwings as xw

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

# Retrieve the path separator
separator = app.path_separator
print(f"The path separator is: '{separator}'")

On Windows, this might output: The path separator is: '\', while on macOS, it could output: The path separator is: ':'.

2. Dynamic Path Construction:
   Here, PathSeparator is used to build a file path dynamically, ensuring compatibility across different operating systems. This is useful when automating tasks that involve saving or opening files in Excel.

import xlwings as xw

# Access the Excel application
app = xw.apps.active

# Define folder and file names
folder = "Documents"
subfolder = "Reports"
filename = "data.xlsx"

# Construct the path using the path separator
path = folder + app.path_separator + subfolder + app.path_separator + filename
print(f"Constructed path: {path}")

On Windows, the output might be: Constructed path: Documents\Reports\data.xlsx, and on macOS: Constructed path: Documents:Reports:data.xlsx.

3. Handling Paths in a Cross-Platform Script:
   This example shows a practical scenario where PathSeparator is used to check and manipulate file paths within an Excel automation script, making it robust for deployment on multiple platforms.

import xlwings as xw

# Get the active Excel app
app = xw.apps.active

# Simulate a file path (e.g., from a user input or configuration)
raw_path = "C:UsersJohnDocuments:file.txt" # Note: This uses a mix of separators for illustration

# Normalize the path by replacing incorrect separators with the system's correct one
# For simplicity, assume we want to convert forward slashes or colons/backslashes as needed
# This is a basic example; in real scenarios, use os.path for more complex operations
normalized_path = raw_path.replace(":", app.path_separator).replace("\\", app.path_separator).replace("/", app.path_separator)
print(f"Normalized path: {normalized_path}")

# Use the path in Excel, e.g., to open a workbook
try:
    wb = app.books.open(normalized_path)
    print("Workbook opened successfully.")
except Exception as e:
    print(f"Error opening workbook: {e}")

How to use Application.Path in the xlwings API way

The Application.Path property in xlwings provides a straightforward way to retrieve the installation directory path of the Microsoft Excel application. This can be particularly useful for scenarios where you need to locate Excel’s executable or related files programmatically, such as for launching Excel independently, configuring add-ins, or ensuring compatibility checks within your automation scripts.

Functionality:
The Path property returns a string that represents the complete folder path where the Excel application (EXCEL.EXE) is installed. This is the path to the root directory of the Office installation, not the path of the currently active workbook. It is a read-only property, meaning you can only retrieve its value and cannot set it to change Excel’s installation location.

Syntax in xlwings:
The property is accessed through the xlwings.App object, which represents the Excel application instance. The syntax is simple, as it does not require any parameters.

app_instance.api.Path

• app_instance: This is your xlwings App object, typically created with xw.App() (for a new instance) or xw.apps collection (to connect to a running instance).
• .api: This is the gateway to the underlying Excel Object Model (COM). It allows you to access native Excel properties and methods that are not directly wrapped by xlwings’ high-level API.
• .Path: The specific property being called from the Excel Application object.

Code Examples:

1. Getting the Path from a New Excel Instance:
   This example starts a new, visible instance of Excel and prints its installation path.

import xlwings as xw

# Launch a new Excel application
app = xw.App(visible=True)

# Access the Path property via the .api attribute
excel_install_path = app.api.Path
print(f"Excel is installed at: {excel_install_path}")

# Close the Excel application
app.quit()

2. Getting the Path from a Running Instance:
   If Excel is already open, you can connect to the active application and retrieve the path.

import xlwings as xw

# Connect to the first running instance of Excel
# (Ensure Excel is open before running this)
app = xw.apps.active

# Retrieve and display the installation path
path = app.api.Path
print(f"Connected Excel instance path: {path}")

3. Practical Use Case – Constructing a Path to an Add-in:
   You can combine the Path with other directory information to build full file paths. For instance, to construct the typical path for an Excel Add-in file.

import os
import xlwings as xw

app = xw.App(visible=False)
excel_root = app.api.Path

# Build a path to a common Add-ins directory
# The structure might vary based on Office version and installation type
addins_path = os.path.join(excel_root, "Library", "Analysis", "ANALYS32.XLL")
print(f"Potential Analysis ToolPak path: {addins_path}")

# Check if a specific file exists at that location
if os.path.exists(addins_path):
    print("The Analysis ToolPak add-in file was found.")
else:
    print("File not found at the constructed path.")

app.quit()