Blog

How to use Application.OnWindow in the xlwings API way

In Excel’s object model, the Application.OnWindow property is a very useful member that allows developers to assign a macro or a procedure to run whenever any workbook window is activated (i.e., brought to the front) within the Excel application. This enables automation of tasks that should respond to window switching, such as updating a dashboard, refreshing data, or adjusting UI elements based on the active workbook.

In xlwings, which provides a Pythonic way to interact with Excel via its COM API, you can access this property through the Application object. The OnWindow property is a read/write string that accepts the name of a macro (as stored in Excel) to be executed. It is important to note that the assigned macro must be available in a currently open workbook, typically within a standard module.

Syntax and Parameters:
In xlwings, you can set or get the OnWindow property using the following approach:

import xlwings as xw

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

# Set the OnWindow property to a macro name
app.api.OnWindow = "MacroName"

# Get the current OnWindow property value
current_macro = app.api.OnWindow

• app.api.OnWindow: This accesses the underlying COM Application.OnWindow property. The api attribute in xlwings provides direct access to the raw Excel object model.
• Value: The property expects a string that is the name of a macro (e.g., "MyWindowHandler"). To clear the assignment, set it to an empty string ("").

Key Points:

• The macro specified must be written in VBA and reside in a module of an open workbook. It cannot be a Python function directly; xlwings can bridge this by calling Python from VBA, but the OnWindow property itself only accepts VBA macro names.
• The event triggers whenever any workbook window is activated, including switching between different windows of the same workbook.
• This property is application-wide, meaning it affects all workbooks open in that Excel instance.

Example Usage:
Suppose you have a VBA macro named UpdateStatusBar in a workbook that updates the status bar with the active window’s name. You can assign it via xlwings as follows:

import xlwings as xw

# Start or connect to Excel
app = xw.App(visible=True)

# Open a workbook containing the macro (e.g., 'Book1.xlsm')
wb = app.books.open('Book1.xlsm')

# Set the OnWindow property to trigger the macro
app.api.OnWindow = "UpdateStatusBar"

# Now, whenever you switch windows, the macro will run
# For demonstration, activate another window
app.books.open('Book2.xlsx').activate()

# To check the current assignment
print(f"OnWindow macro is set to: {app.api.OnWindow}")

# To remove the assignment
app.api.OnWindow = ""

# Close the workbooks and quit
wb.close()
app.quit()

How to use Application.OLEDBErrors in the xlwings API way

The OLEDBErrors member of the Application object in Excel’s object model represents a collection of OLEDBError objects. These objects provide detailed information about errors that occur during an OLE DB query operation, such as when refreshing data connections linked to external databases (e.g., SQL Server, Access). This is particularly useful for debugging and handling data connection issues programmatically, allowing developers to identify specific error numbers, descriptions, and the responsible application or provider.

In xlwings, you can access this collection via the api property of the main App or Book object, which exposes the underlying Excel VBA object model. The syntax for referencing the OLEDBErrors collection is:

xlwings.App.api.OLEDBErrors

or, if working with a specific workbook:

xlwings.Book.api.Parent.OLEDBErrors

The OLEDBErrors collection has several key properties and methods, but note that it is typically read-only and used for inspection. The most commonly used property is Item(index), which returns a single OLEDBError object. Each OLEDBError object has properties like:

• ErrorString: A descriptive text of the error.
• Native: The native error code from the OLE DB provider.
• Number: The error number.
• SqlState: The SQL state code.
• ApplicationName: The name of the application that generated the error.

To retrieve error details, you would first check the Count property of the OLEDBErrors collection to see if any errors exist, then iterate through them.

Here is a practical xlwings code example that demonstrates how to use the OLEDBErrors collection. This example assumes you have an Excel workbook with an existing OLE DB data connection (e.g., a query table linked to a database), and an error might occur during a refresh operation:

import xlwings as xw

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

# Assume we have a workbook with a data connection
wb = app.books.active

# Try to refresh all data connections in the workbook
try:
    wb.api.RefreshAll()
except Exception as e:
    print(f"Refresh failed with general error: {e}")

# Check for OLE DB errors after refresh
ole_errors = app.api.OLEDBErrors

if ole_errors.Count > 0:
    print(f"Number of OLE DB errors: {ole_errors.Count}")
    for i in range(1, ole_errors.Count + 1):
        error = ole_errors.Item(i)
        print(f"Error {i}:")
        print(f" Description: {error.ErrorString}")
        print(f" Error Number: {error.Number}")
        print(f" Native Error Code: {error.Native}")
        print(f" SQL State: {error.SqlState}")
        print(f" Application: {error.ApplicationName}")
else:
    print("No OLE DB errors detected.")

How to use Application.ODBCTimeout in the xlwings API way

The ODBCTimeout property of the Application object in Excel is a setting that controls the timeout duration, in seconds, for ODBC (Open Database Connectivity) queries. When you execute a query that retrieves data from an external database via ODBC, this property determines how long Excel will wait for the query to complete before it times out and potentially returns an error. This is particularly useful in environments where database queries might be slow due to network latency, large datasets, or server load, allowing you to adjust the wait time to suit your specific needs.

In the xlwings API, you can access this property through the Application object. The property is both readable and writable, meaning you can retrieve the current timeout value and set it to a new one as needed. The syntax for using it in xlwings is straightforward, as it maps directly to the underlying Excel object model. The value is an integer representing the number of seconds, and it can be set to any positive number, though practical limits depend on your system and requirements.

Syntax in xlwings:

• To get the current ODBCTimeout value: app.api.ODBCTimeout
• To set a new ODBCTimeout value: app.api.ODBCTimeout = seconds

Here, app refers to the xlwings App instance connected to Excel, and seconds is an integer specifying the timeout duration. For example, setting it to 0 means no timeout (wait indefinitely), while a value like 60 sets a one-minute timeout. It’s important to note that this property applies globally to the Excel application session, affecting all ODBC queries run during that session.

Code Examples:
Below are practical examples demonstrating how to use the ODBCTimeout property with xlwings in Python. These examples assume you have Excel and xlwings installed, and they show common scenarios like checking the current timeout, adjusting it for long-running queries, and resetting it.

import xlwings as xw

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

# Example 1: Retrieve the current ODBCTimeout setting
current_timeout = app.api.ODBCTimeout
print(f"Current ODBC Timeout: {current_timeout} seconds")

# Example 2: Set a new timeout value, e.g., to 120 seconds for a slow database query
app.api.ODBCTimeout = 120
print("ODBC Timeout updated to 120 seconds.")

# Example 3: Use in a context where you run an ODBC query, then reset to default
# First, increase timeout for a complex query
app.api.ODBCTimeout = 300 # 5 minutes
# Here, you would typically execute your ODBC query via Excel methods or external connections
# For demonstration, we'll just print a message
print("Running a long ODBC query with extended timeout...")
# After query, you might reset to a shorter timeout or the original value
app.api.ODBCTimeout = current_timeout # Reset to previous value
print(f"Reset ODBC Timeout back to {app.api.ODBCTimeout} seconds.")

# Example 4: Set to 0 for no timeout (use with caution to avoid hanging)
app.api.ODBCTimeout = 0
print("ODBC Timeout set to 0 (no timeout).")

How to use Application.ODBCErrors in the xlwings API way

The Application.ODBCErrors property in the Excel object model returns a collection of ODBCError objects that represent errors generated by the most recent ODBC (Open Database Connectivity) query operation. This is particularly useful for debugging and error handling when working with external databases via ODBC connections in Excel, such as when using Microsoft Query or retrieving data through SQL queries. In xlwings, this property can be accessed to programmatically inspect and respond to these errors, enabling robust data integration workflows.

In xlwings, the Application object is accessed through the app instance, typically when connecting to an existing Excel application or creating a new one. The ODBCErrors property is a read-only collection that provides details about any ODBC-related issues encountered during data retrieval. Each error in the collection includes properties like ErrorString (a description of the error) and SqlState (the SQL state code), which can be used for diagnostic purposes. Note that this collection is only populated after an ODBC operation fails; if no errors occur, it remains empty.

The syntax for accessing ODBCErrors in xlwings is straightforward. After setting up an xlwings connection to Excel, you can reference it as follows:

import xlwings as xw

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

# Access the ODBCErrors collection
odbc_errors = app.api.ODBCErrors

Here, app.api is used to access the underlying Excel object model, and ODBCErrors is called as a property. This returns a COM object representing the collection, which can be iterated over to examine individual errors. The collection supports a Count property to check the number of errors, and you can access specific errors by index (e.g., odbc_errors.Item(1)). Key parameters or attributes for each ODBCError object include:

• ErrorString: A string describing the error.
• SqlState: A five-character SQL state code indicating the error type.
• NativeError: The native error code from the ODBC data source.
  These can be retrieved in Python by calling the respective properties on each error item.

For example, consider a scenario where an ODBC query fails due to a database connection issue. The following xlwings code demonstrates how to capture and display the errors:

import xlwings as xw

# Start or connect to Excel
app = xw.apps.active

# Assume an ODBC query has been executed and failed
# Access the ODBCErrors collection
errors = app.api.ODBCErrors

# Check if any errors occurred
if errors.Count > 0:
    print(f"Number of ODBC errors: {errors.Count}")
    for i in range(1, errors.Count + 1):
        error = errors.Item(i)
        print(f"Error {i}:")
        print(f" Description: {error.ErrorString}")
        print(f" SQL State: {error.SqlState}")
        print(f" Native Error Code: {error.NativeError}")
else:
    print("No ODBC errors detected.")

How to use Application.NewWorkbook in the xlwings API way

The Application.NewWorkbook property in xlwings provides a powerful way to create a new Excel workbook programmatically. It returns a Workbook object representing the newly created workbook, allowing for immediate manipulation of its contents, sheets, and properties. This functionality is essential for automating report generation, data processing workflows, or any task requiring the dynamic creation of Excel files without manual intervention.

Functionality
The primary function of Application.NewWorkbook is to generate a fresh, blank workbook in Excel. This new workbook becomes the active workbook, and you can start adding data, formatting, or charts right away. It is particularly useful in scenarios where you need to produce multiple output files from a single data source or when building templates on the fly. Unlike simply opening an existing file, this method ensures you begin with a clean slate, adhering to default Excel settings unless otherwise modified.

Syntax and Parameters
In xlwings, you access this property through the app object, which represents the Excel Application. The basic syntax is:

new_wb = app.books.add()

While the VBA object model uses Application.NewWorkbook, xlwings employs the books.add() method as the direct equivalent. The add() method can accept several optional parameters to customize the new workbook:

• Template: A string specifying the path to an Excel template file (.xltx, .xltm). If provided, the new workbook is based on this template instead of the default blank workbook.
• Before: A Sheet object. The new workbook is placed before this specified sheet (relevant when adding within a specific workbook context, though typically used with app.books.add() for a new file).
• After: A Sheet object. The new workbook is placed after this specified sheet.

For most common use cases, calling app.books.add() without arguments is sufficient. The method returns a Book object (xlwings’ term for a Workbook), which you can assign to a variable for further operations.

Code Examples

1. Creating a Simple New Workbook and Adding Data:

import xlwings as xw

# Start a new Excel instance (visible=False for background operation)
app = xw.App(visible=True)

# Create a new workbook
new_workbook = app.books.add()

# Access the first sheet and write data
sheet = new_workbook.sheets[0]
sheet.range('A1').value = 'Product'
sheet.range('B1').value = 'Sales'
sheet.range('A2').value = ['Widget A', 'Widget B', 'Widget C']
sheet.range('B2').value = [1500, 2100, 1850]

# Save the workbook
new_workbook.save(r'C:\Reports\NewReport.xlsx')
# new_workbook.close()
# app.quit()

2. Creating a Workbook from a Template:

import xlwings as xw

app = xw.App(visible=False)

# Specify the path to your template
template_path = r'C:\Templates\CompanyReport.xltx'

# Create a new workbook based on the template
new_wb = app.books.add(template_path)

# The new workbook already contains the template's formatting and sheets.
# You can populate predefined cells.
new_wb.sheets['Data'].range('C5').value = 'Q4-2023'
new_wb.sheets['Summary'].range('B10').value = 95000

# Save it with a new name
new_wb.save(r'C:\Reports\Q4_Report_Final.xlsx')
app.quit()

3. Creating Multiple Workbooks in a Loop:

import xlwings as xw

app = xw.App(visible=False)

departments = ['Sales', 'Marketing', 'Engineering', 'HR']

for dept in departments:
    # Create a new workbook for each department
    wb = app.books.add()
    wb.sheets[0].name = dept
    wb.sheets[0].range('A1').value = f'Department: {dept}'
    # ... add more department-specific data ...
    wb.save(fr'C:\DepartmentReports\{dept}_Data.xlsx')
    wb.close() # Close the workbook after saving to free memory

app.quit()

How to use Application.NetworkTemplatesPath in the xlwings API way

The Application.NetworkTemplatesPath property is a member of the Excel Object Model that returns a String representing the full network path where Microsoft Excel stores templates that are available to all users on a network. This path is typically set through Excel’s options or via group policy in an enterprise environment. In the context of xlwings, this property provides a convenient way to programmatically determine the central location for shared workbook and worksheet templates, enabling scripts to dynamically locate and utilize these resources for report generation, data standardization, and template-driven automation. Accessing this path via xlwings allows for robust, location-agnostic code that adapts to the specific network configuration of the deployment environment.

Functionality
The primary function is to retrieve the read-only network templates directory path. It is useful for operations such as:

• Opening a network template to create a new workbook.
• Saving a custom template to the shared network location for team-wide access.
• Listing available templates in the directory for user selection in a custom dialog.

Syntax in xlwings
The property is accessed through the xlwings App object, which represents the Excel application.

import xlwings as xw

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

# Access the NetworkTemplatesPath property
network_path = app.api.NetworkTemplatesPath

• app: An xlwings App object.
• .api: This property provides direct access to the underlying Excel Application object’s API (the COM object).
• .NetworkTemplatesPath: The specific property call. It takes no parameters.

The return value is a Python string (str) containing the full UNC (Universal Naming Convention) path, e.g., "\\fileserver\companydata\ExcelTemplates". If no network path is configured, it may return an empty string ("").

Code Examples

1. Opening a Workbook from the Network Templates Path:
   This example checks if the path is configured and opens a specific template file from it.

import xlwings as xw
import os

app = xw.apps.active
base_path = app.api.NetworkTemplatesPath

if base_path:
    template_file = "Monthly_Report.xltx"
    full_path = os.path.join(base_path, template_file)

    if os.path.exists(full_path):
        # Opens the template, creating a new workbook based on it
        new_wb = app.books.open(full_path)
        print(f"Opened template from: {full_path}")
        # ... perform operations on new_wb ...
    else:
        print(f"Template file not found at {full_path}")
else:
    print("Network Templates Path is not configured.")

2. Saving a Custom Template to the Network Location:
   This example saves the active workbook as a template (.xltx) to the shared network directory.

import xlwings as xw
import os

app = xw.apps.active
wb = app.books.active
network_path = app.api.NetworkTemplatesPath

if network_path:
    # Ensure the directory exists (Excel usually manages this)
    if not os.path.isdir(network_path):
        os.makedirs(network_path)

    template_name = "Data_Analysis_Template.xltx"
    save_path = os.path.join(network_path, template_name)

    # Save the active workbook as a template
    # Note: The `FileFormat` parameter for .xltx is 54 (xlOpenXMLTemplate).
    # We use the .api to access the SaveAs method with specific parameters.
    wb.api.SaveAs(Filename=save_path, FileFormat=54)
    print(f"Template saved successfully to: {save_path}")
else:
    print("Cannot save template. Network Templates Path is not set.")

3. Listing Available Templates:
   This script retrieves and prints a list of all Excel template files (.xltx, .xltm, .xlt) in the network directory.

import xlwings as xw
import os

app = xw.apps.active
network_path = app.api.NetworkTemplatesPath

if network_path and os.path.isdir(network_path):
    template_extensions = ('.xltx', '.xltm', '.xlt')
    all_files = os.listdir(network_path)
    template_files = [f for f in all_files if     f.lower().endswith(template_extensions)]

    print(f"Templates found in '{network_path}':")
    for template in template_files:
        print(f" - {template}")
else:
    print("Network Templates Path is either not configured or not accessible.")