Blog

How to use Application.AddCustomList in the xlwings API way

The AddCustomList member of the Application object in Excel is a method that allows you to define a custom list for sorting and auto-filling data. Custom lists are particularly useful for creating personalized sorting orders, such as days of the week, months, or any user-defined sequence, which can then be applied across worksheets to ensure consistent data organization. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, enabling precise control over Excel’s features from Python.

Functionality:
The primary function of AddCustomList is to add a new custom list to Excel’s memory. Once added, this list can be used in sorting operations or for auto-fill actions, where dragging a cell’s fill handle will populate cells based on the defined sequence. This is beneficial for standardizing data entry and maintaining order in datasets that follow non-alphabetical or non-numeric sequences.

Syntax in xlwings:
The xlwings API call follows the pattern:

app.api.AddCustomList(ListArray, ByRow)

• ListArray: This parameter specifies the items to be included in the custom list. It can be provided as a Python list or tuple containing strings or numbers. For example, ['Low', 'Medium', 'High'] or ('Q1', 'Q2', 'Q3', 'Q4'). The list must be one-dimensional.
• ByRow: This is a Boolean parameter that indicates whether the list is arranged by rows. In most cases, setting ByRow to False is appropriate, as custom lists are typically column-oriented. If set to True, the list is interpreted as a row-based array, but this is less common. The default behavior in Excel VBA is False, and it is generally recommended to use False in xlwings unless specific row-based data is provided.

Example Usage:
Below is an xlwings code example that demonstrates how to add a custom list and then use it for sorting data in an Excel worksheet. This example assumes an existing Excel workbook is open via xlwings.

import xlwings as xw

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

# Define a custom list for priority levels
custom_list = ['Low', 'Medium', 'High']

# Add the custom list using the Application object's AddCustomList method
app.api.AddCustomList(ListArray=custom_list, ByRow=False)

# Now, use the custom list to sort data in a specific worksheet
wb = xw.books.active
ws = wb.sheets['Sheet1']

# Assume column A contains priority data to be sorted based on the custom list
# Set the sort range (e.g., A1:A10)
sort_range = ws.range('A1:A10')

# Apply sorting with the custom order
sort_range.api.Sort(
Key1=ws.range('A1').api,
Order1=1, # Ascending order
CustomOrder=custom_list[0], # Use the first item of the list to reference the custom list
DataOption1=0
)

# Note: In Excel, the custom list is stored globally, so it can be reused across workbooks during the session.

How to use Application.ActivateMicrosoftApp in the xlwings API way

The ActivateMicrosoftApp method in the Excel object model is accessible via the Application object in xlwings. This method serves a specific purpose: it activates a separate Microsoft application window, bringing it to the foreground. This is particularly useful when automating workflows that involve switching between Excel and other Microsoft Office programs like Word or PowerPoint, allowing for seamless integration and control from within an Excel VBA macro or, in this context, an xlwings-powered Python script.

Functionality
The primary function of ActivateMicrosoftApp is to launch or switch to another Microsoft application. It does not create new documents within that application but activates the application window itself. If the requested application is not already running, the method will typically start it. This enables automated processes to prepare data in Excel and then directly present it in another Office program without manual intervention.

Syntax in xlwings
The xlwings API provides a direct mapping to this method through the Application object. The syntax is:

app.api.ActivateMicrosoftApp(Index)

Here, app refers to the xlwings App instance (which corresponds to the Excel Application object). The .api property exposes the underlying pywin32 object, allowing access to the native VBA method.

Parameters
The method requires a single argument, Index, which is a Long integer specifying the application to activate. The standard values are:

Note: The availability and behavior might depend on the specific Office version installed. Indexes like 5 (Schedule+) are largely obsolete.

Code Examples
Below are practical xlwings code snippets demonstrating the use of ActivateMicrosoftApp.

1. Activating Microsoft Word:
   This script opens Excel, writes a value to a cell, and then switches to Microsoft Word.

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.App(visible=True)
wb = app.books.active
wb.sheets[0].range('A1').value = "Data for Word"

# Activate Microsoft Word (Index = 1)
app.api.ActivateMicrosoftApp(1)

2. Switching to PowerPoint from an Existing Workbook:
   This example assumes Excel is already open and controlled by xlwings. It activates PowerPoint.

import xlwings as xw

# Connect to the currently running Excel
app = xw.apps.active
# Bring PowerPoint to the foreground
app.api.ActivateMicrosoftApp(2)

3. Checking Application Activation with Error Handling:
   A more robust example includes basic error handling, acknowledging that the target application might fail to start.

import xlwings as xw
import time

app = xw.App(visible=True)
try:
    # Attempt to activate Microsoft Access
    app.api.ActivateMicrosoftApp(4)
    print("Microsoft Access activation attempted.")
    # A brief pause can be helpful for the window switch to complete
    time.sleep(1)
except Exception as e:
    print(f"An error occurred: {e}")
finally:
    # Perform cleanup or other tasks
    pass

How to use Application.FileValidation in the xlwings API way

The FileValidation property of the Application object in Excel is a feature designed to manage file validation settings for the application. This property allows developers to control how Excel handles files that originate from potentially unsafe locations, such as those downloaded from the internet or received via email, which may contain macros or other executable content. By using the FileValidation property, you can programmatically adjust Excel’s behavior to either enable or disable validation checks on these files, enhancing security by preventing the automatic execution of potentially harmful code. In xlwings, which provides a Pythonic interface to Excel’s COM automation, accessing this property enables automation of security settings directly from Python scripts, integrating Excel file handling into broader data processing workflows.

Syntax in xlwings:
In xlwings, the Application object is typically accessed through the app object when connecting to an Excel instance. The FileValidation property can be retrieved or set using the following format:

app.api.FileValidation

This property returns or accepts an integer value corresponding to the file validation mode. The values are defined in the Excel object model as follows:

• 0: msoFileValidationDefault – Uses the default file validation behavior.
• 1: msoFileValidationSkip – Skips file validation for the current session.
• 2: msoFileValidationOn – Turns on file validation for the current session.

Note that in xlwings, the api attribute provides direct access to the underlying Excel COM object, allowing you to use properties and methods as documented in the Excel VBA object model. The FileValidation property is read-write, meaning you can both get its current value and set it to change Excel’s behavior.

Code Examples:
Here are practical examples demonstrating how to use the FileValidation property with xlwings:

1. Retrieving the Current File Validation Setting:
   This example connects to an active Excel instance and prints the current file validation mode.

import xlwings as xw

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

# Get the current FileValidation value
validation_mode = app.api.FileValidation
print(f"Current FileValidation mode: {validation_mode}")

2. Setting the File Validation to Skip Validation:
   This example sets the file validation to skip mode, which might be useful when processing trusted files in a controlled environment, and then restores it to the default.

import xlwings as xw

app = xw.apps.active

# Save the current mode for later restoration
original_mode = app.api.FileValidation

# Set to skip validation
app.api.FileValidation = 1 # msoFileValidationSkip
print("File validation set to skip mode.")

# Perform tasks with files (e.g., open a workbook)
# ...

# Restore the original mode
app.api.FileValidation = original_mode
print("File validation restored to original mode.")

3. Enabling File Validation for Enhanced Security:
   This example ensures that file validation is turned on, which is recommended for general use to maintain security.

import xlwings as xw

app = xw.apps.active

# Enable file validation
app.api.FileValidation = 2 # msoFileValidationOn
print("File validation is now enabled.")