Blog

How to use Application.RecordRelative in the xlwings API way

The Application.RecordRelative property in Excel’s object model is a Boolean property that indicates whether the next macro recording will use relative references. In simpler terms, when RecordRelative is set to True, any actions you record (like selecting cells) will be stored relative to the initially selected cell. When set to False (the default), recordings use absolute references, meaning actions are tied to specific cell addresses (e.g., Range("A1")). This property is primarily useful when you are programmatically controlling the macro recorder via VBA or, in the context of automation, when you need to check or set the recorder’s state. However, it’s important to note that xlwings, as a Python library, does not have a direct, dedicated wrapper for every single property like RecordRelative. Instead, you access it through the generic api property, which exposes the underlying COM object (Excel’s Application object).

Syntax and Parameters in xlwings:
The xlwings syntax to get or set this property is:

app.api.RecordRelative

This is a read/write property. It accepts and returns a Boolean value.

• Get: current_state = app.api.RecordRelative retrieves the current setting (True for relative, False for absolute).
• Set: app.api.RecordRelative = True sets the macro recorder to use relative references for the next recording.

Important Considerations:

1. The RecordRelative property only affects the next macro recording session started via the Excel UI (e.g., Developer Tab > Record Macro) or via Application.StartRecorder. It does not affect existing macros or code.
2. This is a very low-level, recorder-specific property. Most xlwings scripts perform actions directly without involving the macro recorder, so its utility in typical xlwings automation is limited. It might be used in scenarios where you are building a tool that needs to programmatically prepare Excel’s environment for user-driven macro recording.

Code Examples:

Example 1: Checking the Current RecordRelative Setting

import xlwings as xw

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

# Get the current RecordRelative state
recording_mode = app.api.RecordRelative
print(f"Next macro will record with relative references: {recording_mode}")
# Output might be: Next macro will record with relative references: False

Example 2: Setting RecordRelative to True

import xlwings as xw

app = xw.apps.active

# Set the recorder to use relative references for the next macro
app.api.RecordRelative = True
print("Macro recorder is now set to relative reference mode.")
# If a user now starts recording a macro via the Excel UI, their cell selections will be recorded relatively.

Example 3: Toggling the Setting Based on a Condition

import xlwings as xw

app = xw.apps.active

# Toggle the current state
current_state = app.api.RecordRelative
app.api.RecordRelative = not current_state
print(f"Toggled RecordRelative from {current_state} to {app.api.RecordRelative}")

How to use Application.RecentFiles in the xlwings API way

The RecentFiles property of the Application object in Excel is a powerful feature accessible through the xlwings library, enabling Python scripts to interact with the list of most recently opened workbooks. This property returns a RecentFiles collection, which contains RecentFile objects representing each file in Excel’s recent documents list. It is particularly useful for automating tasks that involve recently used files, such as logging, batch processing, or creating dynamic dashboards that reference the latest data sources. By leveraging xlwings, developers can programmatically access and manipulate this list without manual intervention, enhancing workflow efficiency in data analysis and visualization projects.

Functionality:
The primary function of the RecentFiles property is to provide read-only access to the collection of recently opened files in Excel. Each item in the collection corresponds to a file that appears in Excel’s “Recent” list, typically found under the “File” tab. Through xlwings, you can retrieve details such as file paths, names, and the order of recency, allowing for automated operations like opening, analyzing, or tracking usage patterns of these files. Note that this property does not allow direct modification of the list (e.g., adding or removing files programmatically), as it reflects Excel’s internal state based on user actions.

Syntax:
In xlwings, the RecentFiles property is accessed via the Application object. The basic syntax is as follows:

import xlwings as xw
app = xw.apps.active # or xw.App() for a new instance
recent_files = app.api.RecentFiles

Here, app.api.RecentFiles returns the Excel VBA RecentFiles collection object. To interact with individual files, you can iterate over the collection or access items by index (starting from 1). Key methods and properties include:

• Count: Returns the number of recent files (e.g., recent_files.Count).
• Item(index): Retrieves a specific RecentFile object by its position in the list, where the most recent file is at index 1.
• Name: Property of a RecentFile object that provides the full file path and name.
• Path: Property that returns the directory path of the file.

Parameters for Item(index):

• index: An integer specifying the position in the recent files list. Values range from 1 to Count, with 1 being the most recently opened file. If the index is out of range, an error will occur.

Example:
Below is a practical xlwings code example that demonstrates how to use the RecentFiles property to list and open the most recent workbook. This script assumes Excel is already running with an active instance.

import xlwings as xw

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

# Access the RecentFiles collection
recent_files = app.api.RecentFiles

# Check if there are any recent files
if recent_files.Count > 0:
    print("Recent Files List:")
    for i in range(1, recent_files.Count + 1):
        recent_file = recent_files.Item(i)
        file_name = recent_file.Name
        print(f"{i}: {file_name}")

# Open the most recent file (index 1) in a new workbook
most_recent_path = recent_files.Item(1).Name
wb = app.books.open(most_recent_path)
print(f"Opened: {most_recent_path}")

# Perform data analysis: e.g., read data from the first worksheet
sheet = wb.sheets[0]
data_range = sheet.range("A1").expand()
print(f"Data range size: {data_range.shape}")
else:
print("No recent files available.")

How to use Application.Ready in the xlwings API way

The Ready member of the Application object in Excel is a property that indicates whether Excel has completed any pending calculations, data refreshes, or operations, and is ready to accept user input or further automation commands. In the context of automation via xlwings, this property is particularly useful when you need to ensure that Excel is in a stable, idle state before proceeding with subsequent operations, such as reading calculated values, saving workbooks, or executing macros. This can help prevent errors or race conditions in scripts that interact with a live Excel instance.

In xlwings, you access the Ready property through the app object, which represents the Excel application. The property is read-only and returns a Boolean value: True if Excel is ready, and False otherwise. The syntax for accessing it is straightforward:

app.api.Ready

Here, app is your xlwings App instance, and .api provides direct access to the underlying Excel object model, including the Application object and its members. The Ready property does not take any parameters. It’s a simple check that you can use in conditional statements or loops to pause execution until Excel is ready.

A common use case is to wait for Excel to finish calculating after changing cell values or formulas, especially in workbooks with complex calculations or external data connections. Instead of using arbitrary time delays (e.g., time.sleep()), which can be inefficient or unreliable, polling the Ready property ensures that your script proceeds only when Excel is truly idle. However, note that in some scenarios, such as when Excel is displaying a modal dialog (like a message box), the Ready property might return False indefinitely, so it’s best used in controlled environments where such dialogs are avoided.

Below is a code example that demonstrates how to use the Ready property in xlwings. This script opens an Excel workbook, performs an operation that triggers calculations, and waits for Excel to be ready before reading a result:

import xlwings as xw

# Start or connect to an Excel application
app = xw.App(visible=True) # Set visible=False for background operation

# Open a workbook (replace with your file path)
wb = app.books.open('example.xlsx')
sheet = wb.sheets['Sheet1']

# Change a cell value that triggers calculations, e.g., a formula dependency
sheet.range('A1').value = 100

# Check if Excel is ready; poll in a loop if necessary
while not app.api.Ready:
    # You can add a short sleep to avoid excessive CPU usage, but keep it minimal
import time
    time.sleep(0.1) # Sleep for 100 milliseconds between checks

# Once ready, read a calculated value from another cell
result = sheet.range('B1').value
print(f"Calculated result: {result}")

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

How to use Application.Range in the xlwings API way

The Application object’s Range member in Excel’s object model is a fundamental interface for accessing and manipulating cells and ranges within a workbook. In xlwings, this is primarily accessed through the app (or xw.apps) object, which represents the Excel application instance. The Range member is not directly called as a method on app in xlwings; instead, it is used via the books, sheets, and range properties to target specific cells. The core functionality revolves around reading, writing, and formatting cell data, as well as performing operations like resizing or selecting ranges.

Syntax and Parameters:
The typical xlwings pattern to get a range starts from the application, through a specific workbook and sheet. The direct equivalent to VBA’s Application.Range is not a single call but a chain:

import xlwings as xw
app = xw.apps.active # Or xw.App() for a new instance
range_obj = app.books['Book1'].sheets['Sheet1'].range('A1:B10')

Alternatively, using the shorter, more common xlwings syntax that implicitly uses the active app:

range_obj = xw.Range('A1:B10') # Uses active sheet in active workbook

The range() method/function accepts arguments to define the range:

• cell1 (str or tuple): The starting cell address (e.g., 'A1') or a tuple of row and column numbers (e.g., (1, 1) for A1).
• cell2 (str or tuple, optional): The ending cell address for defining a rectangular range (e.g., 'B10'). If omitted, a single-cell range is created.

The returned object is an xlwings Range object, which has numerous properties and methods like value, formula, color, autofit(), etc.

Example Usage:
Here are practical examples using xlwings to interact with ranges via the application context:

1. Writing data to a range:

import xlwings as xw
app = xw.App(visible=True) # Start Excel app
wb = app.books.add() # Add a new workbook
ws = wb.sheets[0]
# Write a 2D list to range A1:C3
ws.range('A1').value = [[1, 'Apple', 2.5], [2, 'Banana', 1.8], [3, 'Cherry', 3.2]]

2. Reading data from a range:

data = ws.range('A1:C3').value # Returns a list of lists
print(data) # Output: [[1, 'Apple', 2.5], [2, 'Banana', 1.8], [3, 'Cherry', 3.2]]

3. Using range operations:

# Autofit column widths for range A:C
ws.range('A:C').columns.autofit()
# Add a formula in cell D1
ws.range('D1').formula = '=SUM(C1:C3)'
# Get the address of the used range
used_range = ws.used_range.address
print(used_range) # e.g., '$A$1:$D$3'

4. Dynamic range via app selection:

app = xw.apps.active
# Get the range currently selected in Excel
selected_range = app.selection
if isinstance(selected_range, xw.Range):
    selected_range.value = 'Updated' # Write to all selected cells

How to use Application.QuickAnalysis in the xlwings API way

The QuickAnalysis property of the Application object in Excel is a powerful feature that provides a user interface for quick data analysis, including options for formatting, charts, totals, tables, and sparklines. In xlwings, this functionality is exposed through the api property, which allows direct access to the underlying Excel object model. Using the QuickAnalysis property programmatically via xlwings enables developers to trigger this feature on a selected range of cells, enhancing productivity by automating common data analysis tasks. This is particularly useful in scenarios where you want to guide users through interactive data exploration without manual intervention.

Functionality:
The QuickAnalysis property returns a QuickAnalysis object, which represents the quick analysis options available for a specified range. In the Excel interface, this appears as a small icon at the bottom-right corner of a selected range, offering contextual tools for data visualization and summarization. Through xlwings, you can programmatically invoke this feature to display the quick analysis menu or apply specific analysis options, such as conditional formatting or chart creation, based on the data in the range.

Syntax:
In xlwings, the QuickAnalysis property is accessed via the api property of an Application object. The general syntax is:

quick_analysis_obj = xw.apps[0].api.QuickAnalysis

However, note that the QuickAnalysis property is typically used in conjunction with a Range object to specify the target cells. The full usage involves:

• Accessing the Application object through xlwings.
• Using the QuickAnalysis property to get the QuickAnalysis object.
• Applying methods like Show to display the analysis options for a range.

The Show method is key here, with the syntax:

range.api.QuickAnalysis.Show(Location)

Where:

• range: This is the xlwings Range object representing the cells you want to analyze.
• Location: An optional parameter that specifies where the quick analysis menu should appear. It can take values from the XlQuickAnalysisMode enumeration, such as xlQuickAnalysisModeAll (default) to show all options.

Common XlQuickAnalysisMode values include:

• xlQuickAnalysisModeAll (0): Displays all available analysis options.
• xlQuickAnalysisModeFormulas (1): Shows only formula-related options.
• xlQuickAnalysisModeCharts (2): Displays chart options.
• xlQuickAnalysisModeTotals (3): Shows total calculation options.
• xlQuickAnalysisModeTables (4): Displays table formatting options.
• xlQuickAnalysisModeSparklines (5): Shows sparkline options.

Example:
Here is a practical xlwings code example that demonstrates using the QuickAnalysis property to trigger the quick analysis menu for a selected range. This example assumes you have an Excel workbook open with some data.

import xlwings as xw

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

# Open a workbook or use the active one
wb = app.books.active

# Select a range of data, e.g., A1:D10 on the first sheet
data_range = wb.sheets[0].range('A1:D10')

# Display the quick analysis menu for the selected range
# Using the default location (all options)
data_range.api.QuickAnalysis.Show(xlQuickAnalysisModeAll)

# Alternatively, you can specify a specific mode, like charts only
# First, ensure the constant is defined (xlQuickAnalysisModeCharts = 2)
data_range.api.QuickAnalysis.Show(2)

# To apply a specific analysis option programmatically, you might use other methods
# For instance, to apply a specific chart type, you could use:
# data_range.api.QuickAnalysis.ApplyChartType(ChartType)
# Note: The ApplyChartType method requires further parameters and may vary based on Excel version.

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