Blog

How to use Application.Help in the xlwings API way

In Excel’s object model, the Application.Help method is a powerful tool for launching Excel’s built-in help system directly from your code. While xlwings, as a Python library, does not have a direct, one-to-one wrapper for every single Excel VBA method, it provides full access to the underlying Excel Application object through its api property. This allows you to call native Excel methods, including Help, from your Python scripts. This functionality is particularly useful for creating user-friendly macros or applications that can provide context-sensitive assistance.

Functionality:
The primary function of Application.Help is to open the Excel Help pane and display a specific help topic. You can use it to show general help or to jump to a topic identified by a Help Context ID. This can guide users to official documentation about a function, feature, or error message directly from within your automated workflow.

Syntax (via xlwings api):
The call is made through the xlwings App object’s api property, which exposes the native Excel Application COM object.

app.api.Help(HelpFile, HelpContextID)

• app: Your xlwings App instance (e.g., app = xw.App() or xw.apps.active).
• .api: The gateway to the native Excel object model.
• .Help(...): The actual VBA method call.

Parameters:

Parameter	Data Type	Description	How to Determine Values
HelpFile	String	Optional. The name of the Help file you want to display. If omitted, Excel’s default help file is used.	Typically, you leave this blank to use Excel’s main help. For add-ins, you would specify their custom .chm or .hlp file name.
HelpContextID	Long	Optional. The context ID number for the specific help topic. If provided, Help opens directly to that topic. If omitted, the main Help contents page is shown.	These IDs are defined by the Help file author (Microsoft or add-in developer). They are often listed in the VBA Object Browser or add-in documentation.

Code Examples:

1. Opening General Excel Help:
   This is the simplest use case, launching the main Excel Help window.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active
# Open the default Excel Help
app.api.Help()

2. Opening Help for a Specific Topic (using a known Context ID):
   This example assumes you know the Context ID for the “VLOOKUP” function help topic (a hypothetical ID for demonstration).

import xlwings as xw

app = xw.App() # Starts a new Excel instance
# Open Help directly to the topic for Context ID 10017
app.api.Help(HelpContextID=10017)
# Note: The actual Context ID for VLOOKUP differs. You need the correct ID from Microsoft's documentation.

3. Integrating into a Macro for User Assistance:
   You can bind this to a button in your xlwings-powered tool to create a “Help” button.

import xlwings as xw
from xlwings import Book

def show_function_help():
"""Assumes the active cell contains a function name and fetches its help."""
    wb = xw.books.active
    sheet = wb.sheets.active

    # Get the formula from the active cell
    current_cell = sheet.range('A1') # Example: Get function name from A1
    func_name = current_cell.value

    # A simple mapping (In reality, you'd need a full map of function names to Context IDs)
    help_id_map = {"VLOOKUP": 10017, "SUMIF": 10042}

    app = xw.apps.active
    context_id = help_id_map.get(func_name)

if context_id:
    app.api.Help(HelpContextID=context_id)
else:
    app.api.Help() # Open general help if no specific ID is found

# This function can be called from an xlwings Ribbon button or a shape macro.

How to use Application.Goto in the xlwings API way

The Goto method of the Application object in Excel is a powerful feature for navigating to a specific range, reference, or named location within a workbook. It is particularly useful for quickly moving the active cell or selection to a predefined area, which can enhance user interaction and automate navigation tasks in scripts. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control similar to VBA.

Functionality:
The primary function of Goto is to shift the active cell or selection to a specified target. This target can be a Range object, a cell reference as a string, or a defined name (e.g., a named range). It can also scroll the window to make the target visible if it is not currently in view. This is especially beneficial in large worksheets where manual scrolling is inefficient, as it streamlines navigation during automated processes.

Syntax:
In xlwings, the Goto method is called via the Application object. The syntax is:

app.api.Goto(Reference, Scroll)

• Reference: This parameter is required and specifies the destination. It can be:
• A Range object (e.g., app.range('A1') or app.range('Sheet1!B5')).
• A string representing a cell address (e.g., 'Sheet2!C10').
• A string representing a defined name (e.g., 'MyNamedRange').
• Scroll: This optional parameter is a boolean value (True or False). If set to True, Excel will scroll the window to bring the target into view. The default value is False, meaning no scrolling occurs unless necessary.

Code Examples:

1. Navigate to a specific cell using a string reference:

import xlwings as xw
app = xw.apps.active # Get the active Excel application
app.api.Goto(Reference='Sheet1!D20', Scroll=True)

This moves the active cell to D20 on Sheet1 and scrolls the window to ensure it is visible.

2. Navigate to a named range:

import xlwings as xw
app = xw.apps.active
app.api.Goto(Reference='SalesData', Scroll=False)

Assuming ‘SalesData’ is a defined name in the workbook, this selects that range without scrolling.

3. Navigate using a Range object from xlwings:

import xlwings as xw
app = xw.apps.active
target_range = app.books['Workbook1.xlsx'].sheets['Data'].range('F15')
app.api.Goto(Reference=target_range.api, Scroll=True)

Here, the api property of the xlwings Range object is passed as the Reference, ensuring compatibility with the Excel object model.

4. Navigate to a range in another workbook:

import xlwings as xw
app = xw.apps.active
app.api.Goto(Reference="[Budget.xlsx]Annual!A1", Scroll=True)

How to use Application.GetSaveAsFilename in the xlwings API way

The GetSaveAsFilename method of the Application object in Excel is a powerful tool for prompting users to specify a filename and location for saving a file, without actually performing the save operation. This is particularly useful in scenarios where you need to obtain a user-defined file path for further processing, such as exporting data, creating reports, or setting a save destination in a macro. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model.

Functionality:
The primary function of GetSaveAsFilename is to display the standard “Save As” dialog box. It returns the full path selected by the user as a string. If the user cancels the dialog, it returns False. This allows your script to conditionally proceed based on user input, ensuring flexibility and user control over file operations.

Syntax in xlwings:
The method is called via the Excel Application object. The basic xlwings API syntax is:

file_path = xw.apps[app_key].api.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

Parameters:

• InitialFilename (Optional, Variant): Suggests a default filename. If omitted, the current workbook’s name is used.
• FileFilter (Optional, Variant): A string specifying file type filters. It consists of pairs: a description and the file extension, separated by commas, with pairs delimited by semicolons. For example, "Excel Files (*.xlsx), *.xlsx, Text Files (*.txt), *.txt".
• FilterIndex (Optional, Variant): The index number (1-based) of the default file filter to use from FileFilter. If omitted, the first filter is used.
• Title (Optional, Variant): The title text displayed in the dialog box. If omitted, the default title is shown.
• ButtonText (Optional, Variant): On Macintosh only, the text for the save button.

Code Examples:

1. Basic Usage: Prompt the user for a filename with a default suggestion.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active
# Get a save filename, suggesting "Report.xlsx"
suggested_path = app.api.GetSaveAsFilename(InitialFilename="Report.xlsx")
if suggested_path != False:
    print(f"User selected: {suggested_path}")
else:
    print("Save dialog was cancelled.")

2. With File Filters: Allow the user to choose between Excel and CSV formats.

import xlwings as xw

app = xw.apps.active
# Define filters for Excel and CSV files
file_filters = "Excel Workbook (*.xlsx), *.xlsx, CSV Files (*.csv), *.csv"
selected_path = app.api.GetSaveAsFilename(FileFilter=file_filters, FilterIndex=2, Title="Export Data")
if selected_path:
    # Process the path (e.g., save data using pandas or other libraries)
    print(f"File will be saved to: {selected_path}")

3. Integration with Data Export: Combine with pandas to save a DataFrame based on user input.

import xlwings as xw
import pandas as pd

# Sample DataFrame
data = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
app = xw.apps.active
path = app.api.GetSaveAsFilename(InitialFilename="DataExport.csv",
FileFilter="CSV Files (*.csv), *.csv",
Title="Save CSV File")
if path and isinstance(path, str):
    # Ensure the file has the correct extension if not provided by user
    if not path.endswith('.csv'):
        path += '.csv'
        data.to_csv(path, index=False)
        print(f"Data saved to {path}")

How to use Application.GetPhonetic in the xlwings API way

The GetPhonetic member of the Excel Application object is a method that retrieves the Japanese phonetic (furigana) text for a specified string or cell. This is particularly useful when working with Japanese data, as it allows you to programmatically access the phonetic guides often used to indicate the pronunciation of Kanji characters. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model.

The syntax for calling GetPhonetic in xlwings is as follows:

app.api.GetPhonetic(Text)

Where:

• app is an instance of the xlwings App class, representing the Excel application.
• Text (optional): This parameter specifies the text for which to retrieve the phonetic information. It can be a string or a reference to a cell. If omitted, the method returns the phonetic text for the last processed text.

The Text parameter accepts different types of inputs, which determine its behavior:

Input Type	Description
String	A literal text string (e.g., "東京"). The method returns the phonetic text for that string.
Range Reference	A reference to a cell (e.g., app.range('A1')). The method returns the phonetic text for the cell’s value.
Omitted	If the parameter is not provided, Excel uses the last text that was processed for phonetics.

It’s important to note that the GetPhonetic method is primarily designed for Japanese text and may not return meaningful results for other languages. Additionally, the phonetic information must be present in the Excel file; it is often added through features like “Phonetic Guide” in Excel’s UI.

Here are two xlwings code examples demonstrating the use of GetPhonetic:

Example 1: Retrieving phonetic text from a string

import xlwings as xw

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

# Get phonetic text for the Japanese string "東京" (Tokyo)
phonetic_text = app.api.GetPhonetic("東京")
print(phonetic_text) # Output might be "トウキョウ" depending on Excel's settings

Example 2: Retrieving phonetic text from a cell

import xlwings as xw

# Start a new workbook
wb = xw.Book()
sheet = wb.sheets[0]

# Write a Japanese word with phonetic guide to cell A1 (assume phonetic is added via Excel)
sheet.range('A1').value = "東京"

# Retrieve the phonetic text from cell A1
phonetic_text = wb.app.api.GetPhonetic(sheet.range('A1'))
print(phonetic_text) # Output will be the phonetic text associated with the cell's content

How to use Application.GetOpenFilename in the xlwings API way

The Application.GetOpenFilename method in Excel’s object model allows users to display the standard “Open” dialog box, enabling file selection without actually opening any files. This is particularly useful for scenarios where you need to retrieve a file path for further processing, such as importing data, logging, or batch operations. In xlwings, this functionality is accessed through the api property of the App object, providing a direct bridge to Excel’s VBA methods.

Syntax in xlwings:

file_path = xw.apps.active.api.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

• FileFilter: A string specifying the file filtering criteria. For example, "Excel Files (*.xlsx), *.xlsx" restricts selection to .xlsx files. Multiple filters can be separated by commas.
• FilterIndex: An integer indicating the default filter index to use (e.g., 1 for the first filter). If omitted, the first filter is used.
• Title: A string for the dialog box’s title bar. If omitted, the default title “Open” is displayed.
• ButtonText: Reserved for Macintosh; typically ignored on Windows.
• MultiSelect: If set to True, allows multiple file selections, returning an array of file paths; default is False.

Example Usage:
Here is a practical example that prompts the user to select one or more Excel files and prints their paths. This script uses xlwings to interact with an active Excel instance.

import xlwings as xw

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

# Set up file filter for Excel files
file_filter = "Excel Files (*.xlsx), *.xlsx, All Files (*.*), *.*"

# Display the Open dialog with a custom title
selected_files = app.api.GetOpenFilename(FileFilter=file_filter,
FilterIndex=1,
Title="Select Excel Files for Processing",
MultiSelect=True)

# Process the result
if selected_files:
    if isinstance(selected_files, str): # Single file selected
        print(f"Selected file: {selected_files}")
    else: # Multiple files selected (returns a tuple)
        for file in selected_files:
            print(f"Selected file: {file}")
else:
    print("No file was selected.")

Key Points:

• When MultiSelect=True, the method returns a tuple of strings if multiple files are chosen; otherwise, it returns a single string.
• If the user cancels the dialog, the method returns False.
• This method does not open the file; it only retrieves the path(s), giving full control over subsequent actions like reading with pandas or xlwings.

How to use Application.GetCustomListNum in the xlwings API way

The GetCustomListNum member of the Application object in Excel is a method used to retrieve the index number of a custom list that has been defined in the Excel application. Custom lists are often utilized for sorting data in a user-defined order, such as days of the week or months, and they can also be used in functions like MATCH or VLOOKUP to align data with these custom sequences. In xlwings, this functionality is accessible through the api property, which provides direct access to the underlying Excel object model. This method is particularly useful when you need to programmatically determine the position of a specific list within Excel’s custom list collection, enabling dynamic interactions with list-based operations.

Functionality
GetCustomListNum returns a numeric value representing the index of a custom list based on a provided list array. If the specified list matches one of the custom lists defined in Excel, the method returns its index number (starting from 1 for the first custom list). If no match is found, it returns 0. This can assist in validating or identifying custom lists before performing operations like sorting or data alignment.

Syntax
In xlwings, the method is called via the Application object. The syntax is:

index = xw.apps[0].api.GetCustomListNum(list_array)

• list_array: This is a required parameter that specifies the list to be checked. It should be passed as an array or range of values. In xlwings, you can use a Python list or an Excel range object. For example, a Python list like ["Mon", "Tue", "Wed"] or an xlwings range like sheet.range("A1:A3").value.

Parameters and Values
The list_array parameter must be a one-dimensional array of strings or numbers that correspond to the custom list entries in Excel. Excel stores custom lists in a specific order, and the method compares the input array to these stored lists. Note that custom lists are case-insensitive in Excel, so the matching process ignores letter case. If the input array is empty or invalid, the method may return an error or 0.

Code Examples
Here are some xlwings API code instances demonstrating the use of GetCustomListNum:

1. Basic Example with a Python List: Check if a custom list for weekdays exists and get its index.

import xlwings as xw

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

# Define a list to check (e.g., weekdays)
list_to_check = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]

# Get the custom list index
list_index = app.api.GetCustomListNum(list_to_check)
print(f"The custom list index is: {list_index}")
# Output might be 1 if this is the first custom list, or 0 if not found.

2. Using an Excel Range as Input: Retrieve data from a worksheet and check if it matches a custom list.

import xlwings as xw

# Open a workbook and reference a sheet
wb = xw.Book("example.xlsx")
sheet = wb.sheets["Sheet1"]

# Get values from a range (e.g., cells A1:A5)
range_values = sheet.range("A1:A5").value

# Ensure it's a flat list (xlwings returns a list of lists for 2D ranges)
if isinstance(range_values[0], list):
    range_values = [item for sublist in range_values for item in sublist]

# Check for custom list match
app = xw.apps.active
list_index = app.api.GetCustomListNum(range_values)
if list_index > 0:
    print(f"Custom list found at index: {list_index}")
else:
    print("No matching custom list found.")

3. Dynamic List Validation: Before sorting data, verify that a custom list exists to avoid errors.

import xlwings as xw

app = xw.apps.active
custom_list = ["Low", "Medium", "High"] # Example priority list

index = app.api.GetCustomListNum(custom_list)
if index == 0:
print("Warning: Custom list not defined. Consider adding it in Excel options.")
else:
# Proceed with sorting or other operations using the list index
print(f"Using custom list index {index} for sorting.")