Blog

How to use Application.Parent in the xlwings API way

In the xlwings library, the Application object’s Parent property is a fundamental attribute that provides a reference to the object that contains the current Application object. According to the Excel object model, the Application object is typically the top-level object, meaning its Parent property usually returns the application itself or, in certain contexts, another containing object. In xlwings, this property is accessed via the parent attribute of the App instance, allowing users to navigate and manipulate the hierarchical structure of Excel objects, which is essential for advanced automation and integration tasks.

Functionality:
The Parent property is primarily used to retrieve the parent object of the current Application instance. This can be useful in scenarios where you need to verify the context of the Excel application, such as when working with multiple instances or embedded objects. For example, if an Application object is embedded within another application (like a Microsoft Office suite component), the Parent property helps identify that container. In most standalone Excel sessions, the Parent of the Application object is the Application itself, reflecting its top-level status. This property is read-only and is often leveraged in debugging or dynamic object traversal.

Syntax:
In xlwings, the Parent property is accessed through the parent attribute of an App object. The general syntax is:
app.parent
Here, app is an instance of the xlwings App class representing the Excel application. This attribute returns an App object that represents the parent. No parameters are required for this property. It is a straightforward attribute call, and since it is read-only, you cannot set it to a new value directly.

Example:
Consider a scenario where you launch an Excel application using xlwings and want to check its parent object. The following code demonstrates how to use the Parent property:

import xlwings as xw

# Launch or connect to an Excel application
app = xw.App(visible=True)

# Access the Parent property
parent_app = app.parent

# Display information about the parent
print(f"Type of parent: {type(parent_app)}")
print(f"Parent is the same as the original app? {parent_app is app}")

# In a typical standalone Excel, this will show that the parent is the application itself
# You can also check properties like the parent's process ID
if hasattr(parent_app, 'pid'):
    print(f"Parent process ID: {parent_app.pid}")

# Close the application
app.quit()

In this example, app.parent returns an App object that, in a standard Excel session, refers to the same application instance. The output will likely indicate that the parent is identical to the original app, confirming the top-level nature. This can be validated using identity comparison (is operator). Note that in embedded contexts, such as when Excel is hosted within another program, the parent might differ, but xlwings typically handles standalone applications.

Another practical use case is when iterating through multiple Excel instances to manage them programmatically. For instance, you can loop through all open Excel applications and examine their parent relationships to ensure correct handling:

import xlwings as xw

# Get all running Excel instances
apps = xw.apps

for app in apps:
parent = app.parent
print(f"App PID: {app.pid}, Parent PID: {parent.pid if hasattr(parent, 'pid') else 'N/A'}")
# Perform actions based on parent context, such as closing orphaned instances

How to use Application.OrganizationName in the xlwings API way

The Application.OrganizationName property in xlwings provides a read-only string that returns the registered organization name associated with the installation of Microsoft Excel. This property is useful for retrieving system-level information, often for logging, auditing, or customizing application behavior based on the organizational context. It reflects the organization name entered during the initial setup or through the system registry, and it is consistent across the Excel application instance.

In xlwings, you access this property through the Application object. The syntax is straightforward as it does not require any parameters. Since it is a property, you simply reference it to get its value.

Syntax:

app.organization_name

• app: This is an instance of the xlwings App class, representing the Excel application. Typically, you obtain it by creating a new app instance with xw.App() or by connecting to an existing one.
• organization_name: This is the property that returns the organization name as a string. Note that in xlwings, property names generally use snake_case (e.g., organization_name) rather than the CamelCase used in the Excel object model (e.g., OrganizationName).

Example Usage:

Here is a basic example demonstrating how to retrieve the organization name using xlwings:

import xlwings as xw

# Start a new instance of Excel (visible or hidden)
app = xw.App(visible=False) # Set visible=True to see the Excel window

# Access the OrganizationName property
org_name = app.organization_name

# Print the result
print(f"The registered organization name is: {org_name}")

# Close the Excel application
app.quit()

In this example, the app.organization_name property is called to fetch the organization name, which is then printed to the console. The application is started in a non-visible mode to run in the background, which is efficient for automated scripts. If the organization name is not set or cannot be retrieved, the property may return an empty string.

Another common scenario is to use this property within a larger automation script, perhaps to conditionally execute certain operations based on the organization. For instance:

import xlwings as xw

# Connect to an existing Excel instance
app = xw.App(visible=True)

# Get the organization name
current_org = app.organization_name

# Check if the organization matches a specific value
if current_org == "Contoso Ltd.":
    print("Proceeding with Contoso-specific formatting.")
    # Add custom formatting or data processing here
else:
    print(f"Organization '{current_org}' detected. Running standard procedures.")

# Save and close the active workbook if needed
wb = app.books.active
wb.save()
app.quit()

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