Blog

How to use Application.OperatingSystem in the xlwings API way

The Application.OperatingSystem property in Excel’s object model is a read-only property that returns the name and version number of the current operating system as a string. This information can be useful for writing cross-platform compatible scripts or for logging and diagnostic purposes within your automation tasks. When using the xlwings library in Python, you can access this property to retrieve the OS details of the machine where Excel is running.

In xlwings, the Application object is typically accessed through the app object when you have an instance of Excel running. The syntax to call the OperatingSystem property is straightforward, as it does not require any parameters. The property returns a string that usually includes the OS name and version, such as “Windows (32-bit) NT 10.00” for a 32-bit Windows 10 system or “Mac OS X 10.15.7” for a macOS Catalina system. Note that the exact format of the string may vary depending on the Excel version and operating system, but it generally provides key details to identify the environment.

To use the OperatingSystem property in xlwings, you first need to ensure that Excel is running and connected via xlwings. Here is a basic example of how to retrieve and print the OS information:

import xlwings as xw

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

# Access the OperatingSystem property
os_info = app.api.OperatingSystem

# Print the result
print(f"Operating System: {os_info}")

In this code, app.api is used to access the underlying Excel object model, allowing you to call the OperatingSystem property directly. The property returns a string that you can store in a variable or use in conditional logic. For instance, you might want to check the OS to adjust file paths or features in your script. Here’s another example that demonstrates conditional handling based on the OS:

import xlwings as xw

# Start or connect to Excel
app = xw.App(visible=False) # Create a new instance, or use xw.apps.active for an existing one

# Get the operating system string
os_string = app.api.OperatingSystem

# Check for specific OS conditions
if "Windows" in os_string:
    print("Running on Windows. Adjusting file paths for Windows compatibility.")
    # Add Windows-specific code here, e.g., using backslashes in paths
elif "Mac" in os_string:
    print("Running on macOS. Adjusting file paths for macOS compatibility.")
    # Add macOS-specific code here, e.g., using forward slashes in paths
else:
    print(f"Unknown operating system: {os_string}")

# Close the Excel instance if it was created in this script
app.quit()

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