Archive

How to use Application.WindowsForPens in the xlwings API way

The Application.WindowsForPens property in the Excel object model is a legacy property primarily used to indicate whether the system is configured for pen-based input, such as with a stylus or tablet. In the context of xlwings, a powerful Python library for automating Excel, this property can be accessed to check the pen input settings of the Excel application. While its practical use in modern automation scripts is limited, it can be relevant for applications that need to adapt their interface or behavior based on the input device type.

Functionality:
This read-only property returns a Boolean value (True or False). A return value of True signifies that the Excel application is running on a system that is set up for pen input (e.g., Windows is configured to use a tablet or touch screen with pen support). A value of False indicates the system is not configured for such input. It can be used to conditionally enable or disable certain features in a macro or script that are optimized for pen interaction.

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

app.api.WindowsForPens

Here, app is your xlwings App instance. The .api attribute provides direct access to the underlying Excel object model, allowing you to call the native WindowsForPens property. This property does not take any parameters.

Code Example:
The following xlwings code demonstrates how to check the WindowsForPens property and print the result. This example assumes you have an Excel instance running.

import xlwings as xw

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

# Access the WindowsForPens property via the .api attribute
is_pen_enabled = app.api.WindowsForPens

# Output the result
if is_pen_enabled:
    print("The system is configured for pen input.")
else:
    print("The system is not configured for pen input.")

# Alternatively, you can directly print the Boolean value
print(f"WindowsForPens value: {is_pen_enabled}")

How to use Application.Windows in the xlwings API way

The Application.Windows property in Excel’s object model provides a collection of all open workbook windows. In xlwings, this property is accessible through the app object, which represents the Excel application instance. It is particularly useful for programmatically managing and interacting with multiple workbook windows, such as iterating through them to perform actions like arranging, resizing, or closing windows based on specific conditions. This property is read-only and returns a Windows collection object, enabling developers to handle window-level operations efficiently within their automation scripts.

Syntax in xlwings:
app.api.Windows
Here, app is an instance of the xlwings App class, which connects to the Excel application. The .api attribute provides direct access to the underlying Excel object model, allowing you to use the Windows property. The returned collection can be indexed or iterated over, with each item representing a Window object corresponding to an open workbook window. For example, app.api.Windows[0] refers to the first window in the collection, typically the most recently activated window. Note that the order of windows in this collection may vary based on user interactions, so it’s advisable to reference windows by their Caption property (the window title) for more reliable access.

Example Usage:
Below is a practical xlwings code example that demonstrates how to use the Application.Windows property to list all open workbook windows and perform a simple action, such as arranging them in a tiled layout. This example assumes Excel is already running with multiple workbooks open.

import xlwings as xw

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

# Access the Windows collection via the .api attribute
windows = app.api.Windows

# Print the Caption (title) of each open window
print("Open workbook windows:")
for window in windows:
    print(f" - {window.Caption}")

# Arrange all windows in a tiled layout (Excel constant xlTiled = 1)
# This organizes windows side-by-side without overlapping
windows.Arrange(Style=1) # Style 1 corresponds to xlTiled

# Optionally, you can close a specific window by its Caption
# For instance, close a window titled "SalesData.xlsx"
for window in windows:
    if window.Caption == "SalesData.xlsx":
    window.Close()
    print("Closed SalesData.xlsx window.")
    break

# Note: The Arrange method affects only visible windows. Hidden or minimized windows may not be rearranged.

How to use Application.Width in the xlwings API way

The Width property of the Application object in the Excel object model controls the overall width of the Excel application window. In xlwings, this property is accessible through the api property, which provides direct access to the underlying Excel object model. This allows you to programmatically adjust the width of the Excel window, which can be useful for creating a consistent user interface, optimizing screen space during automated tasks, or ensuring that the application window fits specific display requirements.

Syntax in xlwings:

app.api.Width

• Get: The property is read-write, so you can retrieve the current width by simply accessing it.
• Set: Assign a new numeric value (in points) to change the width. The value must be a positive number.

The width is measured in points, where one point equals 1/72 of an inch. The maximum and minimum values depend on the user’s screen resolution and system settings, but Excel typically enforces practical limits to keep the window within the visible desktop area.

Example:
Here are practical examples of using the Width property with xlwings:

1. Getting the Current Application Window Width:

import xlwings as xw
app = xw.App(visible=True) # Start Excel application
current_width = app.api.Width
print(f"The current Excel window width is {current_width} points.")
app.quit() # Close the application

This code snippet starts Excel, reads the window width, prints it, and then closes Excel.

2. Setting the Application Window Width:

import xlwings as xw
app = xw.App(visible=True)
app.api.Width = 800 # Set the width to 800 points
print("Excel window width has been set to 800 points.")
# Keep the application open for observation
input("Press Enter to close Excel...")
app.quit()

Here, the width is explicitly set to 800 points, which can help standardize the window size for presentations or automated reports.

3. Adjusting Width Based on Screen Resolution:

import xlwings as xw
import tkinter as tk
app = xw.App(visible=True)
# Use tkinter to get screen width in pixels
root = tk.Tk()
screen_width_pixels = root.winfo_screenwidth()
root.destroy()
# Convert pixels to points (assuming 96 DPI: 1 pixel = 0.75 points)
width_in_points = screen_width_pixels * 0.75
app.api.Width = width_in_points # Set to half of screen width
print(f"Excel window width set to {width_in_points:.2f} points (half of screen width).")
app.quit()

This example demonstrates dynamic width adjustment by calculating half of the screen width in points, ensuring the Excel window adapts to different monitors.

4. Combining with Height for Full Window Control:

import xlwings as xw
app = xw.App(visible=True)
app.api.Width = 600
app.api.Height = 400 # Set height as well
print("Excel window resized to 600 points wide and 400 points tall.")
app.quit()

How to use Application.Watches in the xlwings API way

The Watches member of the Excel Application object in xlwings provides a programmatic way to manage and interact with the Watch Window feature in Excel. The Watch Window is a debugging and monitoring tool that allows users to track the values of specific cells or formulas across different worksheets and workbooks, updating in real-time as changes occur. Through the Watches collection in xlwings, developers can add, delete, or modify watches dynamically, enabling automation of data validation, error checking, or performance monitoring in complex Excel models.

In xlwings, the Watches collection is accessed via the Application object. The syntax for referencing it is straightforward: app.api.Watches, where app is an instance of the xlwings App class representing the Excel application. This returns a COM object that mirrors the VBA Watches collection, allowing access to its methods and properties. Key methods include Add, which creates a new watch, and Delete, which removes an existing one. The Add method requires parameters such as the source (a Range object) and optional arguments like the sheet name or workbook, which can be specified using xlwings range objects or Excel range addresses as strings. For example, to add a watch for cell A1 on the active sheet, you would use app.api.Watches.Add(app.range('A1').api). Properties like Count can be used to iterate through existing watches, and each watch item in the collection has properties such as Formula (the cell reference or formula being watched) and Value (the current value).

Here is a code example demonstrating the use of the Watches member in xlwings:

import xlwings as xw

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

# Add a watch for cell B5 on the first sheet of the active workbook
sheet = app.books.active.sheets[0]
watch_range = sheet.range('B5')
app.api.Watches.Add(watch_range.api)

# Check the number of watches currently in the Watch Window
watch_count = app.api.Watches.Count
print(f"Number of watches: {watch_count}")

# List all watches and their details
for i in range(1, watch_count + 1):
    watch = app.api.Watches.Item(i)
    print(f"Watch {i}: Formula = {watch.Formula}, Value = {watch.Value}")

# Delete a specific watch by index (e.g., the first watch)
if watch_count > 0:
    app.api.Watches.Item(1).Delete()

# Alternatively, delete all watches
app.api.Watches.Delete()

How to use Application.WarnOnFunctionNameConflict in the xlwings API way

The WarnOnFunctionNameConflict property of the Excel Application object is a setting that controls whether Excel displays a warning message when a user-defined function (UDF) in an add-in has the same name as a built-in Excel function. This is particularly relevant when working with custom functions created via VBA or other add-ins, as name conflicts can cause confusion or unexpected behavior. In xlwings, you can access and modify this property to manage how Excel handles such conflicts, ensuring a smoother integration of custom functionality.

Functionality:
When set to True, Excel will show a warning dialog if a function name conflict is detected. This alert informs the user that a custom function may override or be confused with a built-in one, allowing them to decide how to proceed. When set to False, no warning is issued, which can be useful in controlled environments where conflicts are intentional or managed. This property helps maintain clarity and prevent errors in spreadsheet calculations.

Syntax in xlwings:
In xlwings, you interact with this property through the app object, which represents the Excel application. The property is accessed as follows:

import xlwings as xw

app = xw.apps.active # Or xw.App() for a new instance
# Get the current value
current_setting = app.api.WarnOnFunctionNameConflict
# Set the value
app.api.WarnOnFunctionNameConflict = True # or False

The app.api provides direct access to the underlying Excel object model. The WarnOnFunctionNameConflict property is a Boolean value:

• True: Enables warnings for function name conflicts.
• False: Disables warnings.

Example Usage:
Suppose you are developing an add-in with custom functions and want to ensure users are alerted to potential conflicts. You can use xlwings to enable warnings dynamically. Below is a code example that checks the current setting, changes it to enable warnings, and then restores the original state after performing tasks.

import xlwings as xw

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

# Store the original setting
original_setting = app.api.WarnOnFunctionNameConflict
print(f"Original WarnOnFunctionNameConflict setting: {original_setting}")

# Enable warnings for function name conflicts
app.api.WarnOnFunctionNameConflict = True
print("Warnings enabled for function name conflicts.")

# Perform tasks that might involve custom functions, e.g., running a macro or adding an add-in
# For demonstration, we just wait a moment
import time
time.sleep(2)

# Restore the original setting
app.api.WarnOnFunctionNameConflict = original_setting
print(f"Restored WarnOnFunctionNameConflict to: {app.api.WarnOnFunctionNameConflict}")

How to use Application.Visible in the xlwings API way

The Application object’s Visible property is a fundamental control in Excel automation that determines whether the Excel application window is displayed to the user. In xlwings, this property allows you to run scripts in the background without the Excel interface being shown, which is useful for automated report generation, data processing, or server-side tasks where a user interface is unnecessary. Conversely, you can make the application visible to monitor the automation process or for interactive debugging.

Syntax and Parameters

In xlwings, you access the Visible property through the App object, which represents the Excel application. The property is a Boolean value.

import xlwings as xw

# To get the current visibility state
is_visible = xw.apps.active.api.Visible

# To set the visibility state
xw.apps.active.api.Visible = True # Makes Excel visible
xw.apps.active.api.Visible = False # Hides Excel

Alternatively, when starting a new instance:

app = xw.App(visible=False) # Start Excel in the background
app = xw.App(visible=True) # Start Excel with the window visible

• Member Access: The property is accessed via the .api attribute, which provides direct access to the underlying Excel object model (through pywin32 on Windows or appscript on macOS).
• Value: A Boolean (True or False).
• True: The Excel application window is visible.
• False: The Excel application window is hidden. The application continues to run and can be controlled programmatically.

Code Examples

1. Running a Script Silently in the Background:
   This example opens a workbook, performs a calculation, saves the result, and closes Excel without ever showing the window to the user.

import xlwings as xw

# Start Excel invisibly
app = xw.App(visible=False)
# Open a workbook
wb = app.books.open('source_data.xlsx')
sheet = wb.sheets[0]

# Perform operations (e.g., add a formula)
sheet.range('C10').value = '=SUM(A1:A100)'
# Calculate to ensure formula results are updated
wb.app.calculate()

# Save the result to a new file
wb.save('processed_report.xlsx')

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

2. Toggling Visibility for Monitoring:
   This script hides Excel during a long computation to free system resources, then makes it visible to show the final result before saving.

import xlwings as xw
import time

app = xw.App(visible=True) # Start visible
wb = app.books.add()

print("Starting heavy calculation...")
app.api.Visible = False # Hide Excel

# Simulate a long process
sheet = wb.sheets[0]
for i in range(1, 10001):
    sheet.range(f'A{i}').value = i
    # Perform a complex calculation
    sheet.range('B1').formula = '=SUMPRODUCT(A:A, A:A)'
    wb.app.calculate()
    time.sleep(2) # Simulate processing time

app.api.Visible = True # Show Excel again
print("Calculation complete. Review the sheet.")

# Keep Excel open for review, then save and close
# wb.save('final_output.xlsx')
# app.quit()

3. Checking Current Visibility Status:
   A simple utility to check if the Excel window is currently shown.

import xlwings as xw

# Connect to the active instance (or start one)
if xw.apps.count > 0:
    app = xw.apps.active
    if app.api.Visible:
       print("Excel application window is visible.")
    else:
        print("Excel is running in the background (hidden).")
else:
    print("No active Excel instance found.")

How to use Application.Version in the xlwings API way

The Application object’s Version member in Excel’s object model is a read-only property that returns the version number of the Excel application instance as a string. When automating Excel with xlwings, this property is invaluable for implementing version-specific logic, ensuring compatibility, or logging the environment in which a script runs. Since xlwings acts as a bridge between Python and Excel’s COM (Component Object Model) or AppleScript (on macOS) interfaces, it provides direct access to this property through its API.

Functionality
The primary function of the Version property is to retrieve the exact version number of Microsoft Excel. This information typically follows a format like “16.0” for Excel 2016 or 365, or “15.0” for Excel 2013, allowing scripts to adapt their behavior based on the host application’s capabilities. It is particularly useful for debugging, conditional feature usage (e.g., leveraging functions introduced in newer versions), or generating reports that include the software environment details.

Syntax in xlwings
In xlwings, you access the Application object via the app property of a Book (workbook) object or directly through an App instance. The Version property is then called as an attribute. The syntax is straightforward, as it does not accept any parameters.

# When you have an existing workbook object (book)
version_from_book = book.app.version

# When you have an App instance (app)
version_from_app = app.version

Both approaches return a string representing the Excel version. The property is accessed directly without parentheses, as it is not a method.

Code Examples
Below are practical examples demonstrating how to use the Version property in xlwings scripts.

Example 1: Retrieving and Printing the Excel Version
This basic example opens Excel (if not already running), creates a new workbook, and prints the version to the console. It ensures the Excel application is properly instantiated.

import xlwings as xw

# Start Excel and create a new workbook
app = xw.App(visible=False) # Set visible=True to see the Excel window
wb = app.books.add()

# Get the Excel version
excel_version = app.version
print(f"Excel version: {excel_version}")

# Save, close, and quit (cleanup)
wb.save('example_version.xlsx')
wb.close()
app.quit()

Example 2: Conditional Logic Based on Version
This example shows how to implement version-specific behavior. It checks if the Excel version is 16.0 or higher (typically Excel 2016/365) to decide whether to use a newer function or a fallback method. This is crucial for maintaining compatibility across different user installations.

import xlwings as xw
import re

app = xw.App(visible=False)
wb = app.books.add()

version_str = app.version
# Extract the major version number (e.g., 16 from "16.0")
major_version = int(re.search(r'^(\d+)', version_str).group(1))

if major_version >= 16:
    print("Using features available in Excel 2016 and later.")
    # Here you could call newer Excel functions via xlwings, e.g., XLOOKUP if supported
else:
    print("Using legacy compatibility mode for older Excel versions.")
    # Implement alternative logic for older versions

app.quit()

Example 3: Logging Environment Information for a Report
In this scenario, the script logs the Excel version along with other system details into a worksheet. This is useful for audit trails or technical documentation generated by the script itself.

import xlwings as xw
from datetime import datetime

app = xw.App(visible=False)
wb = app.books.add()
sheet = wb.sheets[0]

# Write environment info to cell A1
info_text = f"Report generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n"
info_text += f"Excel version: {app.version}\n"
info_text += f"xlwings version: {xw.__version__}"

sheet.range('A1').value = info_text
sheet.range('A1').rows.autofit() # Adjust row height for readability

wb.save('environment_report.xlsx')
app.quit()

How to use Application.VBE in the xlwings API way

The Application.VBE property in Excel’s object model provides a reference to the Visual Basic for Applications (VBA) development environment. This is a powerful and advanced feature, primarily used for programmatically interacting with the VBA project, such as adding modules, reading code, or managing references. In xlwings, this property is accessed through the api property, which exposes the underlying pywin32 COM object, allowing you to call the raw Excel VBA object model methods.

Functionality:
The VBE property returns the root object of the VBA Extensibility library (the VBIDE.VBE object). It enables automation of the VBA Integrated Development Environment (IDE) from an external script. Common use cases include:

• Dynamically adding standard or class modules to a workbook.
• Inserting or modifying VBA macro code.
• Inspecting existing VBA project components.
• Enabling programmatic access to the VBA project (which often requires setting the “Trust access to the VBA project object model” in Excel’s Trust Center settings).

Syntax in xlwings:

vbe_object = xw.apps[app_key].api.VBE
# or for the active Excel instance
vbe_object = xw.apps.active.api.VBE

• xw.apps[app_key] or xw.apps.active: This gets the specific or active xlwings App object, representing an Excel instance.
• .api: This is the crucial bridge to the pywin32/COM object, providing access to the native Excel Application object.
• .VBE: This is the property call that returns the VBIDE.VBE object.

Important Notes:

1. Security Setting: To use the VBE property successfully, Excel must have the “Trust access to the VBA project object model” checkbox enabled. This is found under File > Options > Trust Center > Trust Center Settings > Macro Settings.
2. Library Reference: Your Python environment needs the win32com library (provided by pywin32). xlwings handles this dependency.
3. VBIDE Constants: When using methods of the returned VBIDE.VBE object, you may need constants like vbext_ct_StdModule. These are available in the win32com.client.constants module after ensuring the VBIDE type library is referenced. A simpler approach is to use their known integer values (e.g., 1 for a standard module).

Code Example:
The following example demonstrates how to access the VBE object, check if the VBA project is accessible, and add a new standard module to the active workbook containing a simple macro.

import xlwings as xw

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

# Access the VBE object via the .api property
vbe = app.api.VBE

# Get the active workbook's VBA project
# The 'VBProject' property of a Workbook is accessed via its .api
active_wb_vbproject = app.books.active.api.VBProject

# Check if we have access (this will raise an error if trust settings are off)
print(f"VBE Version: {vbe.Version}")

# Add a new standard module to the active workbook's VBA project
# Constant vbext_ct_StdModule = 1
new_module = active_wb_vbproject.VBComponents.Add(1) # 1 represents a standard module
new_module.Name = "MyNewModule"

# Insert code into the new module
code_string = """
Sub HelloFromXlwings()
MsgBox "This module was added programmatically via xlwings!"
End Sub
"""
new_module.CodeModule.AddFromString(code_string)

print(f"Module '{new_module.Name}' added successfully.")

How to use Application.Value in the xlwings API way

The Value member of the Application object in the Excel object model is a property that can be used to get or set the value of the active cell or a specified range through the xlwings API. In xlwings, this is typically accessed via the app object, which represents the Excel application instance. The primary function of the Application.Value property in xlwings is to interact with cell data programmatically, allowing for dynamic data entry, retrieval, and manipulation directly from Python. It serves as a bridge between Python scripts and Excel worksheets, enabling automation of data processing tasks without manual intervention.

In xlwings, the syntax for accessing the Value property of the Application object is not directly used in the same way as in VBA. Instead, xlwings provides a more Pythonic approach through the app object and its associated methods. To get or set values, you typically work with Range objects. However, you can access the active cell’s value via the application context. The general syntax is:

• To get the value: app.active_cell.value
• To set the value: app.active_cell.value = new_value

Here, app is an instance of the xlwings App class representing the Excel application. The active_cell refers to the currently selected cell in the active workbook. This property can return or accept various data types, such as numbers, strings, dates, or even arrays, depending on the context. For setting values, you can assign a single value or a list of lists to represent a 2D array for a range.

For example, to retrieve the value from the active cell in Excel using xlwings, you can use the following code snippet:

import xlwings as xw

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

# Get the value of the active cell
current_value = app.active_cell.value
print(f"The active cell value is: {current_value}")

# Set a new value to the active cell
app.active_cell.value = "Hello from xlwings"

In this example, app.active_cell.value is used to both read and write data. This demonstrates how the Value property facilitates basic data interaction. For more complex scenarios, such as working with specific ranges, you can use app.range('A1:B2').value to get or set multiple values at once. The Value property in this context automatically handles data conversion between Excel and Python types, making it seamless for data analysis tasks.

Another practical use case is when automating data entry from a Python list into an Excel sheet. For instance:

import xlwings as xw

# Start or connect to Excel
app = xw.App(visible=True) # Make Excel visible
wb = app.books.add() # Add a new workbook
ws = wb.sheets[0] # Access the first worksheet

# Define a Python list of data
data = [[1, "Apple", 2.5], [2, "Banana", 1.8], [3, "Cherry", 3.2]]

# Write the data to a range starting at cell A1
ws.range('A1').value = data

# Read back the data to verify
retrieved_data = ws.range('A1:C3').value
print(f"Retrieved data: {retrieved_data}")

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

How to use Application.UseSystemSeparators in the xlwings API way

The Application.UseSystemSeparators property in Excel is a Boolean value that controls whether Excel uses the system’s decimal and thousands separators for number formatting, or the separators specified in the Windows regional settings for the Excel application itself. When set to True (the default), Excel will use the separators defined by the operating system’s regional settings (e.g., a period for decimal and a comma for thousands in the US locale). When set to False, Excel will use the alternative separators, which are typically a comma for decimal and a period for thousands, as might be used in some European locales. This property is crucial for ensuring data is displayed and interpreted correctly in international environments, especially when workbooks are shared across different regional systems.

In the xlwings API, this property is accessed through the Application object. The syntax for getting or setting the property is straightforward:

import xlwings as xw

app = xw.apps.active # or xw.App() for a new instance

# Get the current value
current_setting = app.api.UseSystemSeparators

# Set the value
app.api.UseSystemSeparators = False # Use alternative separators

Here, app.api provides direct access to the underlying Excel Application object from the COM interface. The UseSystemSeparators property is a read/write Boolean. No parameters are required for getting or setting it. To determine the system’s current separators, you can check the Application.DecimalSeparator and Application.ThousandsSeparator properties, which are influenced by this setting.

Example use cases include preparing a workbook for users in a locale with different formatting norms or ensuring consistent number parsing in automated scripts. Below is a practical xlwings code example that demonstrates toggling this property and observing the effect on cell formatting:

import xlwings as xw

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

# Display initial state
print(f"Initial UseSystemSeparators: {app.api.UseSystemSeparators}")
print(f"Decimal Separator: {app.api.DecimalSeparator}")
print(f"Thousands Separator: {app.api.ThousandsSeparator}")

# Change to alternative separators
app.api.UseSystemSeparators = False
print(f"\nAfter setting to False:")
print(f"Decimal Separator: {app.api.DecimalSeparator}")
print(f"Thousands Separator: {app.api.ThousandsSeparator}")

# Write a sample number to a cell to see formatting
wb = app.books.active
ws = wb.sheets[0]
ws.range('A1').value = 12345.67
ws.range('A1').number_format = '#,##0.00'

# The display in Excel will reflect the current separators.
# For example, with UseSystemSeparators=False, it might show as "12.345,67" depending on system settings.

# Revert to system separators
app.api.UseSystemSeparators = True
print(f"\nReverted to True:")
print(f"Decimal Separator: {app.api.DecimalSeparator}")
print(f"Thousands Separator: {app.api.ThousandsSeparator}")

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