Blog

How to use Application.ProtectedViewWindows in the xlwings API way

The ProtectedViewWindows member of the Application object in Excel’s object model provides access to a collection of ProtectedViewWindow objects. Each ProtectedViewWindow represents a workbook that has been opened in Protected View, a security feature that opens potentially unsafe files (like those from the internet or email attachments) in a restricted mode to prevent harmful content from affecting your system. Through xlwings, you can interact with this collection to inspect, manage, or close workbooks opened in this mode, which is useful for automating security checks or handling multiple protected files programmatically.

Functionality
The primary function is to access and manage workbooks in Protected View. You can iterate through all open Protected View windows, retrieve specific windows by index, count them, or close them. This allows for automation scripts that monitor or clean up Protected View sessions, especially in environments where files are frequently downloaded and need processing.

Syntax in xlwings
In xlwings, you access the ProtectedViewWindows collection via the Application object. The typical syntax is:

import xlwings as xw
app = xw.apps.active # Or xw.App() for a specific instance
protected_windows = app.api.ProtectedViewWindows

Here, app.api provides the underlying Excel COM object, exposing the ProtectedViewWindows property. This returns a collection object that supports standard VBA-style methods and properties, such as Count and Item.

Key Properties and Methods

• Count: Returns the number of open Protected View windows (read-only integer).
• Item(index): Returns a single ProtectedViewWindow object by its index number (1-based) or by name.
• Open(filename): Opens a file in Protected View (not directly via the collection in xlwings; typically, you’d use app.api.Workbooks.Open with security flags).

For the ProtectedViewWindow objects themselves, common members include:

• SourceName: The full path of the source file (string).
• Close(): Closes the Protected View window without saving.
• Activate(): Activates the window.
• Workbook: Returns the workbook object within the Protected View (read-only).

Example Usage
Below is a code example demonstrating how to use the ProtectedViewWindows member in xlwings to list and close all Protected View windows:

import xlwings as xw

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

# Access the ProtectedViewWindows collection
protected_windows = app.api.ProtectedViewWindows

# Check if any Protected View windows are open
if protected_windows.Count > 0:
    print(f"Number of Protected View windows: {protected_windows.Count}")

    # Iterate through each window and display its source file
    for i in range(1, protected_windows.Count + 1):
        window = protected_windows.Item(i)
        print(f"Window {i}: Source = {window.SourceName}")

    # Optionally close the window (uncomment to use)
    # window.Close()
else:
    print("No Protected View windows are currently open.")

# To open a file in Protected View (using Workbook.Open with security settings)
# This requires setting the correct parameters; note that xlwings doesn't have a direct method for this.
# In practice, you might use: app.api.Workbooks.Open("C:\\path\\to\\file.xlsx", UpdateLinks=0, ReadOnly=True)
# But for true Protected View, ensure Excel's security settings trigger it automatically for unsafe sources.

How to use Application.PromptForSummaryInfo in the xlwings API way

The Application.PromptForSummaryInfo member in Excel’s object model is a method that displays the “Properties” dialog box, allowing users to view or edit the summary information and statistics of the active workbook. This dialog box includes details such as the title, subject, author, manager, company, category, keywords, comments, and hyperlink base. In xlwings, this functionality can be accessed to programmatically trigger this dialog, which is useful for automating document property management or prompting users to input metadata before saving or distributing a workbook.

Syntax in xlwings:
The method is called via the Application object. The xlwings API syntax is:

app.api.PromptForSummaryInfo

Here, app refers to the xlwings Application object. This method does not take any parameters and does not return a value. It simply opens the dialog box modally, meaning code execution pauses until the user closes the dialog. The method corresponds to the VBA Application.PromptForSummaryInfo method.

Parameters:
The method has no parameters. In VBA, it is called without arguments, and the same applies in xlwings through the .api attribute, which exposes the underlying Excel object model.

Example Usage:
Below is a practical xlwings code example that starts an Excel instance, opens a workbook, and then displays the “Properties” dialog box to allow the user to edit summary information. This can be integrated into scripts for data preparation workflows where document metadata is required.

import xlwings as xw

# Start a new Excel application (visible to see the dialog)
app = xw.App(visible=True)

# Open an existing workbook or create a new one
wb = app.books.open('example.xlsx') # Replace with your file path

# Display the PromptForSummaryInfo dialog
app.api.PromptForSummaryInfo

# The code will pause here while the user interacts with the dialog.
# After closing the dialog, you can continue with other operations, e.g., save the workbook.
wb.save()
print("Workbook properties have been updated.")

# Close the workbook and quit Excel
wb.close()
app.quit()

How to use Application.ProductCode in the xlwings API way

The ProductCode property of the Application object in Excel’s object model is a read-only property that returns a globally unique identifier (GUID) for the installed Microsoft Excel product. This GUID is a string that uniquely identifies the specific version and edition of Excel, such as whether it is a retail, volume-licensed, or OEM version. This property is particularly useful for developers and system administrators who need to programmatically identify or verify the Excel installation on a machine, for example, in software deployment, licensing checks, or compatibility validations within automated scripts or applications.

In xlwings, the Application object is accessed through the app property of a Book object or directly when starting an application. The ProductCode property can be called as an attribute on the app object. The syntax is straightforward, as it does not take any parameters:

product_code = app.api.ProductCode

Here, app refers to the xlwings App instance, and .api is used to access the underlying Excel Application object from the COM interface. The property returns a string representing the ProductCode GUID. Note that this property is specific to the Excel application instance, so it will reflect the version of Excel that xlwings is connected to.

For example, to retrieve the ProductCode of an active Excel instance using xlwings, you can use the following code. This example assumes Excel is already running or will be started by xlwings:

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active if xw.apps.active else xw.App(visible=True)

# Get the ProductCode property
product_code = app.api.ProductCode
print(f"Excel ProductCode: {product_code}")

# Optionally, close the app if it was started for this purpose
if not xw.apps.active:
    app.quit()

In this code, xw.apps.active is used to attach to an existing Excel application; if none exists, a new one is created with xw.App(visible=True). The app.api.ProductCode call retrieves the GUID, which is then printed. The GUID typically looks something like {90160000-0011-0000-1000-0000000FF1CE} for an Office 2016 version, but it varies by installation. This output can be used to identify the Excel product programmatically.

Another practical use case is to check the ProductCode in a script that requires a specific Excel version. For instance, you might want to ensure compatibility before proceeding with automation tasks:

import xlwings as xw

app = xw.apps.active if xw.apps.active else xw.App(visible=False)
expected_product_code = "{90160000-0011-0000-1000-0000000FF1CE}" # Example for Office 2016

if app.api.ProductCode == expected_product_code:
    print("Compatible Excel version detected. Proceeding with automation.")
    # Add your automation code here
else:
    print(f"Incompatible Excel version. ProductCode: {app.api.ProductCode}")

app.quit()

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