Archive

How to use Application.DDEExecute in the xlwings API way

The DDEExecute member of the Application object in Excel enables dynamic data exchange (DDE) commands to be sent from Excel to another application that supports DDE. This is a legacy method primarily used for inter-process communication in older Windows systems, where Excel can instruct another program (like a data source or another Office application) to perform specific actions via established DDE channels. While modern automation often uses COM or other APIs, DDEExecute remains available for compatibility with legacy systems. In xlwings, this functionality is accessed through the api property, which exposes the underlying Excel object model.

Syntax in xlwings:
app.api.DDEExecute(Channel, Command)

• Channel: Required. A Long integer representing the DDE channel number previously opened using the DDEInitiate method. This channel establishes the connection to the external application.
• Command: Required. A String specifying the command to be sent to the external application. The format of this command depends entirely on the receiving application’s DDE interface (e.g., it might be a macro name or data instruction).

Example with xlwings:
Below is a step-by-step example demonstrating how to use DDEExecute via xlwings to send a command to another application (e.g., a hypothetical data server). First, ensure xlwings is installed (pip install xlwings). The code initiates a DDE channel with an external application and then executes a command.

import xlwings as xw

# Start Excel application
app = xw.apps.active # Use active instance or xw.App() for new

# Initiate a DDE channel to an external application (e.g., a server named "MyServer")
# Note: DDEInitiate requires the application and topic; adjust based on target app.
channel = app.api.DDEInitiate("MyServer", "System")

# Send a command via DDEExecute to request data or trigger an action
# For instance, a command to refresh data in the external app
command = "[RefreshAll]" # Example command; refer to target app's DDE documentation
app.api.DDEExecute(channel, command)

# Close the DDE channel after use
app.api.DDETerminate(channel)

print("DDE command executed successfully.")

Notes:

• The Channel must be valid and active; otherwise, an error occurs.
• The Command string should match the syntax expected by the external application—consult its DDE documentation for specifics.
• DDE is outdated and may not be supported in all environments; consider alternatives like COM or APIs for new projects.
• Error handling (e.g., try-except blocks) is recommended to manage potential failures in channel initiation or command execution.

How to use Application.ConvertFormula in the xlwings API way

The Application.ConvertFormula method in Excel is a powerful tool for transforming formula references between different reference styles, such as converting between A1 and R1C1 notation, or between relative, absolute, and mixed references. In xlwings, this functionality is exposed through the api property, allowing Python scripts to leverage Excel’s native conversion capabilities programmatically. This is particularly useful when generating or modifying formulas dynamically, ensuring compatibility across different workbook settings or user preferences.

Functionality
The primary purpose of ConvertFormula is to change the reference style of a formula. It can convert a formula string from the A1 reference style to R1C1, or vice versa. Additionally, it can modify the reference type—converting relative references (like A1) to absolute ($A$1), mixed (A$1 or $A1), or back. This is essential for tasks like template generation, where formulas need to be adjusted based on cell positions, or for macros that interact with formulas in a style-agnostic manner.

Syntax in xlwings
In xlwings, you access this method via the api property of the App or Book objects. The full syntax is:

app.api.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

The parameters are as follows:

• Formula (string): The formula string to be converted. This should be provided as text, without a leading equals sign.
• FromReferenceStyle (int): The reference style of the input formula. Use xlA1 (or 1) for A1 style, and xlR1C1 (or -4150) for R1C1 style.
• ToReferenceStyle (int): The desired reference style for the output. Same options as FromReferenceStyle.
• ToAbsolute (int): Specifies the type of absolute reference conversion. This parameter is optional and defaults to xlAbsolute (or 1). The common values are:
• xlAbsolute (1): Converts to absolute references.
• xlRelRowAbsColumn (2): Converts to mixed references with relative row and absolute column (e.g., A$1 becomes A1 in relative terms).
• xlAbsRowRelColumn (3): Converts to mixed references with absolute row and relative column (e.g., $A1 becomes A1 in relative terms).
• xlRelative (4): Converts to relative references.
• RelativeTo (object): A Range object that specifies the starting cell for relative references. This is required if ToAbsolute is set to xlRelRowAbsColumn, xlAbsRowRelColumn, or xlRelative. It defines the context for relative conversions.

Code Examples

1. Converting from A1 to R1C1 style:

import xlwings as xw
app = xw.App(visible=False)
# Convert the formula "SUM(A1:B2)" from A1 to R1C1 style
result = app.api.ConvertFormula("SUM(A1:B2)", 1, -4150)
print(result) # Output: SUM(R1C1:R2C2)
app.quit()

2. Changing relative references to absolute:

import xlwings as xw
app = xw.App(visible=False)
# Convert "A1+B2" to absolute references in A1 style
result = app.api.ConvertFormula("A1+B2", 1, 1, 1)
print(result) # Output: $A$1+$B$2
app.quit()

3. Using relative conversion with a specific cell context:

import xlwings as xw
app = xw.App(visible=False)
book = app.books.add()
sheet = book.sheets[0]
# Define the relative starting cell as C3
relative_cell = sheet.range("C3").api
# Convert "A1" to a relative reference based on C3
result = app.api.ConvertFormula("A1", 1, 1, 4, relative_cell)
print(result) # Output: This will be a relative formula like "RC[-2]" in R1C1, but in A1 style, it adjusts accordingly.
book.close()
app.quit()

How to use Application.CheckSpelling in the xlwings API way

The Application.CheckSpelling method in Excel is a useful tool for checking the spelling of a single word or a text string programmatically. When accessed through the xlwings library in Python, it provides a way to integrate Excel’s built-in spelling checker into automated scripts and data processing workflows. This can be particularly valuable for validating user inputs, cleaning text data, or ensuring consistency in reports before they are finalized.

In xlwings, the CheckSpelling method is called from the Application object. The syntax follows the pattern of the Excel Object Model, adapted for Python. The basic xlwings API call format is:

app.api.CheckSpelling(Word, CustomDictionary, IgnoreUppercase, MainDictionary, CustomDictionary2, CustomDictionary3, CustomDictionary4, CustomDictionary5, CustomDictionary6, CustomDictionary7, CustomDictionary8, CustomDictionary9, CustomDictionary10)

The parameters are:

• Word (Required, String): The word or text string you want to check.
• CustomDictionary (Optional, String): The file name of the custom dictionary to examine if the word is not found in the main dictionary. The default is an empty string.
• IgnoreUppercase (Optional, Boolean): True to ignore words in all uppercase letters. False to check them. The default is False.
• MainDictionary (Optional, Variant): This can be a language identifier (e.g., “en-US”) or a constant representing a built-in dictionary. It is often left as an optional argument in xlwings, defaulting to the application’s current language setting.
• CustomDictionary2 to CustomDictionary10 (Optional, String): Additional custom dictionary file names.

The method returns a Boolean value. It returns True if the word is found in at least one of the specified dictionaries, and False if it is not found in any. This allows you to use the method in conditional logic within your Python code.

Here are two practical xlwings code examples:

Example 1: Checking a Single Word
This example checks if the word “Analyzze” is spelled correctly according to Excel’s dictionaries.

import xlwings as xw

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

# Check the spelling of a word
word_to_check = "Analyzze"
is_correct = app.api.CheckSpelling(word_to_check)

if is_correct:
    print(f"'{word_to_check}' is spelled correctly.")
else:
    print(f"'{word_to_check}' is misspelled.")
    # This will output: 'Analyzze' is misspelled.

Example 2: Checking Multiple Words from a List
This example demonstrates iterating through a list of potential product codes or terms, using the spelling checker as a simple validation filter, and ignoring terms that are in all caps.

import xlwings as xw

app = xw.apps.active

term_list = ["Project", "XYZZY", "Maintainance", "API", "Delevopment"]
valid_terms = []
flagged_terms = []

for term in term_list:
    # Check spelling, ignoring words in all uppercase
    if app.api.CheckSpelling(term, IgnoreUppercase=True):
        valid_terms.append(term)
    else:
        flagged_terms.append(term)

print("Terms considered valid:", valid_terms)
print("Terms flagged for review:", flagged_terms)
# Expected output:
# Terms considered valid: ['Project', 'XYZZY', 'API']
# Terms flagged for review: ['Maintainance', 'Delevopment']

How to use Application.CheckAbort in the xlwings API way

The Application.CheckAbort member in Excel’s object model is a property that allows developers to check whether a user has requested to abort a running macro or operation, typically by pressing the Esc key or Ctrl+Break. In xlwings, this functionality is accessed through the Application object, enabling you to programmatically determine if an abort has been initiated, which is useful for implementing graceful termination in long-running scripts. This property is read-only and returns a Boolean value, indicating the state of the abort request.

In xlwings, the Application.CheckAbort property is accessed via the api property of the App or Book objects, which provides direct access to the underlying Excel object model. The syntax for using it in xlwings is as follows:

app = xw.apps.active # Get the active Excel application
check_abort = app.api.CheckAbort

Here, app is an instance of the xlwings App object, and app.api exposes the native Excel Application object. The CheckAbort property does not take any parameters. It returns True if an abort has been requested by the user, and False otherwise. This property is typically used within loops or iterative processes to check for user interruptions, allowing the code to exit cleanly without causing errors or crashes.

For example, consider a scenario where you are processing a large dataset in Excel using xlwings, and you want to allow the user to cancel the operation. You can periodically check the Application.CheckAbort property within a loop. Below is a code instance demonstrating its usage:

import xlwings as xw
import time

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

# Simulate a long-running process, such as iterating through rows
for i in range(1, 10001):
    # Check if the user has requested an abort
    if app.api.CheckAbort:
        print("Abort requested by user. Exiting loop.")
        break

    # Perform some operation, e.g., updating a cell value
    sheet = app.books.active.sheets[0]
    sheet.range(f'A{i}').value = f'Processed row {i}'

    # Simulate a delay to mimic processing time
    time.sleep(0.01)

# Optional: Update status every 1000 iterations
if i % 1000 == 0:
    print(f"Processed {i} rows...")

print("Process completed or aborted.")

In this example, the loop iterates through 10,000 rows, updating cells in column A. Before each iteration, it checks app.api.CheckAbort. If the user presses Esc or Ctrl+Break during execution, the property becomes True, triggering the break statement to exit the loop early. This ensures that the macro stops gracefully, and a message is printed to indicate the abort. Without this check, the user might have to force-close Excel or encounter unresponsive behavior.

How to use Application.CentimetersToPoints in the xlwings API way

The Application.CentimetersToPoints method in Excel’s object model is a utility function that converts a measurement from centimeters to points. In the context of xlwings, which provides a Pythonic interface to automate Excel, this method is accessible through the Application object. It is particularly useful when you need to set dimensions, such as row heights, column widths, or shape sizes, in points—Excel’s native unit for such measurements—while working with centimeter-based data. This conversion ensures precision and consistency in layout and formatting tasks, especially in international settings where centimeters are a common metric unit.

Syntax in xlwings:
In xlwings, you call this method via the app object, which represents the Excel application. The syntax is:
app.api.CentimetersToPoints(Centimeters)

• Centimeters: Required. A numeric value or expression representing the length in centimeters that you want to convert to points. This parameter can be a single number, a variable, or a calculated result.
  The method returns a Single (floating-point) value representing the equivalent measurement in points. Note that 1 centimeter is approximately equal to 28.3465 points in Excel, as points are defined as 1/72 of an inch, and 1 inch equals 2.54 centimeters.

Example Usage with xlwings:
Below are practical examples demonstrating how to use CentimetersToPoints in xlwings for various Excel automation tasks. These examples assume you have an Excel application instance running via xlwings.

1. Converting a Single Measurement:
   This example converts 5 centimeters to points and prints the result. It is useful for quick calculations or debugging.

import xlwings as xw
app = xw.App(visible=False) # Start Excel in the background
points_value = app.api.CentimetersToPoints(5)
print(f"5 cm is equal to {points_value} points.") # Output: ~141.7325 points
app.quit()

2. Setting Column Width Based on Centimeters:
   Here, we set the width of column A in the active workbook to a specific centimeter value by converting it to points. Excel’s column width is measured in points (or character units, but points are used for precise control via API).

import xlwings as xw
app = xw.App(visible=True)
wb = app.books.active
ws = wb.sheets[0]
# Convert 3.5 cm to points and set as column width for column A
width_in_points = app.api.CentimetersToPoints(3.5)
ws.api.Columns("A").ColumnWidth = width_in_points
wb.save()
app.quit()

3. Adjusting Row Height Dynamically:
   This example uses a loop to set row heights for multiple rows based on a list of centimeter values. It showcases how to integrate the conversion into batch operations.

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.add()
ws = wb.sheets[0]
cm_heights = [2.0, 2.5, 3.0] # Heights in centimeters for rows 1 to 3
for i, cm in enumerate(cm_heights, start=1):
points_height = app.api.CentimetersToPoints(cm)
ws.api.Rows(i).RowHeight = points_height
wb.save("adjusted_heights.xlsx")
app.quit()

4. Calculating Shape Dimensions:
   When adding or resizing shapes, you might need to specify sizes in points. This example creates a rectangle with width and height derived from centimeter measurements.

import xlwings as xw
app = xw.App(visible=True)
wb = app.books.active
ws = wb.sheets[0]
# Define dimensions in centimeters
width_cm, height_cm = 4.0, 2.0
width_pts = app.api.CentimetersToPoints(width_cm)
height_pts = app.api.CentimetersToPoints(height_cm)
# Add a rectangle shape at position (100, 100) with converted dimensions
shape = ws.shapes.add_shape(
1, # Type: rectangle
100, 100, # Left and top positions in points
width_pts, height_pts
)
shape.name = "MetricRectangle"
wb.save()
app.quit()

How to use Application.CalculateUntilAsyncQueriesDone in the xlwings API way

The Application.CalculateUntilAsyncQueriesDone property is a member of the Excel object model that provides control over the calculation process when asynchronous queries, such as those from Power Query (Get & Transform Data), are involved. In scenarios where a workbook contains data connections that refresh asynchronously, Excel’s standard calculation might proceed before these queries have fully completed. This can lead to formulas returning results based on outdated or incomplete data. The CalculateUntilAsyncQueriesDone property addresses this by forcing Excel to pause further calculation until all pending asynchronous queries have finished refreshing. This ensures subsequent calculations operate on the complete, current dataset.

In xlwings, you access this property through the Application object. The property is a read/write Boolean.

xlwings API Syntax and Parameters

The property is accessed directly on the app object (an instance of xw.App). There are no method parameters as it is a property, not a method.

• Get the current value: current_state = app.api.CalculateUntilAsyncQueriesDone
• Set the value: app.api.CalculateUntilAsyncQueriesDone = True or app.api.CalculateUntilAsyncQueriesDone = False

Property Value:
The property accepts and returns a Boolean value.

Value	Meaning
True	Excel will wait for all asynchronous queries to complete before continuing with any pending calculations.
False	(Default) Excel will not wait for asynchronous queries to finish; calculations may proceed with potentially stale query data.

Usage Example with xlwings

A typical use case is to set this property to True before triggering a full workbook calculation or before running a macro that depends on the latest query data. It is good practice to restore the original setting afterward.

import xlwings as xw

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

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

try:
    # Ensure Excel waits for async queries
    app.api.CalculateUntilAsyncQueriesDone = True

    # Refresh all data connections (queries)
    app.api.ActiveWorkbook.RefreshAll()

    # Now perform a full calculation. Excel will wait for RefreshAll to finish.
    app.api.Calculate()

    # Your code to work with the calculated data...
    ws = app.api.ActiveSheet
    print(f"Value in A1 after refresh and calculation: {ws.Range('A1').Value}")

finally:
    # Restore the original setting
    app.api.CalculateUntilAsyncQueriesDone = original_setting
    print(f"CalculateUntilAsyncQueriesDone restored to:      {app.api.CalculateUntilAsyncQueriesDone}")

How to use Application.CalculateFullRebuild in the xlwings API way

The Application.CalculateFullRebuild member in Excel performs a complete recalculation of all formulas in all open workbooks, including those that may depend on external data sources or custom functions. It ensures that every calculation is refreshed, which is particularly useful after making significant changes to data or formulas that might not update automatically through standard calculation methods. In xlwings, this functionality can be accessed via the api property, allowing Python scripts to trigger a full rebuild of calculations in Excel, similar to pressing Ctrl+Alt+Shift+F9 in the Excel interface. This is beneficial in scenarios where partial recalculations might leave stale values, such as when working with complex financial models, data analysis pipelines, or macros that modify large datasets.

Syntax in xlwings:
To use CalculateFullRebuild in xlwings, you need to reference the Excel Application object through the xlwings App or via an existing workbook. The member is a method with no parameters. The basic syntax is:

app.api.CalculateFullRebuild()

Here, app represents an xlwings App instance connected to Excel. The api property provides direct access to the underlying Excel object model, enabling you to call the CalculateFullRebuild method. There are no arguments to pass, as the method simply triggers a full recalculation across all open workbooks in that Excel instance.

Example Usage:
Below is a practical example demonstrating how to use CalculateFullRebuild in a Python script with xlwings. This example assumes you have Excel open with workbooks containing formulas that need a complete refresh.

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active # Use the currently running Excel application
# Alternatively, start a new instance: app = xw.App()

# Trigger a full recalculation of all formulas in all open workbooks
app.api.CalculateFullRebuild()

print("Full recalculation completed for all open workbooks.")

# You can also specify a particular workbook if needed, but note that CalculateFullRebuild applies globally
wb = app.books['MyWorkbook.xlsx'] # Reference a specific workbook
# Even when referencing a workbook, CalculateFullRebuild still affects all open workbooks in the app
app.api.CalculateFullRebuild()

# To ensure changes are saved, you might add:
wb.save()
app.quit() # Close the Excel application if done

How to use Application.CalculateFull in the xlwings API way

The CalculateFull method of the Application object in Excel is a powerful feature for ensuring complete and accurate recalculation of all formulas in all open workbooks. This method forces a full calculation, meaning it recalculates every formula, regardless of whether Excel’s calculation engine considers them dirty or not. This is particularly useful in scenarios where you have complex, interdependent formulas, or when you have programmatically changed a large number of cells and want to guarantee that all subsequent formulas reflect these changes before proceeding. Unlike the standard Calculate method, which might only recalculate formulas marked as needing an update, CalculateFull provides a thorough and definitive recalculation cycle.

In the xlwings API, you access this method through the app object, which represents the Excel application. The syntax is straightforward, as the method does not take any parameters.

Syntax:

app.api.CalculateFull()

• app: This is your xlwings App instance.
• .api: This property provides direct access to the underlying Excel object model (the COM/API layer).
• .CalculateFull(): This is the method call. It requires no arguments.

Key Points:

• It affects all open workbooks in the Excel application instance.
• It is a synchronous operation; your xlwings code will wait until the full calculation is complete before executing the next line.
• This method is equivalent to pressing Ctrl+Alt+Shift+F9 in the Excel desktop application.

Code Examples:

1. Basic Full Calculation:
   This example ensures that after writing new data to a sheet, every formula in the application is recalculated.

import xlwings as xw

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

# Write some values that are inputs to formulas
app.books['MyWorkbook.xlsx'].sheets['Sheet1'].range('A1').value = 100
app.books['MyWorkbook.xlsx'].sheets['Sheet1'].range('A2').value = 200

# Force a full recalculation of all formulas in all open workbooks
app.api.CalculateFull()

# Now read a result from a formula cell, confident it's up-to-date
result = app.books['MyWorkbook.xlsx'].sheets['Sheet1'].range('C1').value
print(f"The calculated result is: {result}")

2. Using with Manual Calculation Mode:
   This is a common use case. When calculation mode is set to manual, formulas are not updated automatically. CalculateFull gives you precise control over when the heavy computation occurs.

import xlwings as xw

app = xlwings.App(visible=True) # Start a new Excel app
wb = app.books.add()

# Set calculation mode to manual for performance
app.api.Calculation = -4135 # xlCalculationManual

# Perform extensive data manipulation
sheet = wb.sheets[0]
for i in range(1, 1001):
sheet.range(f'A{i}').value = i
# Formulas in column B reference column A
sheet.range(f'B{i}').formula = f'=A{i}*2'

# After all data is written, trigger one comprehensive calculation
print("Starting full calculation...")
app.api.CalculateFull() # This will recalculate all 1000 formulas
print("Calculation complete.")

# Sample the result
print(sheet.range('B500').value) # Will correctly output 1000.0
app.quit()

How to use Application.Calculate in the xlwings API way

The Application.Calculate member in Excel’s object model is a method that forces a full recalculation of all open workbooks. In xlwings, this is exposed through the api property, allowing Python scripts to trigger the same recalculation engine that Excel uses. This is particularly useful after programmatically modifying cell values or formulas, ensuring that all dependent calculations are updated before proceeding with further operations, such as reading results or generating reports.

Functionality
The primary function of Application.Calculate is to perform a complete recalculation across all data in all open workbooks. It recalculates all formulas, updating any cells that depend on changed precedents. This is equivalent to pressing F9 in the Excel application. It is essential when your VBA macro or xlwings script changes values and needs immediate, accurate results from formulas that reference those cells. Without an explicit calculate call, Excel might not update all formulas until the next natural recalculation cycle, potentially leading to stale data being read.

Syntax
In xlwings, you access this method via the Application object obtained from a workbook or app instance. The typical syntax is:

app.application.Calculate()

Here, app refers to an xlwings App instance. The application property returns the underlying COM object (Excel’s Application), on which you call the Calculate method. The method takes no parameters. It simply triggers the recalculation.

Example
Consider a scenario where you have an Excel workbook with formulas in column B that sum values from column A. You use xlwings to write new numbers into column A and then need to read the updated totals from column B. Without a calculate, column B might still show old results.

import xlwings as xw

# Connect to the active Excel instance or create a new one
app = xw.apps.active # Or xw.App() for a new instance

# Open a specific workbook (adjust the path)
wb = app.books.open(r'C:\path\to\your\workbook.xlsx')
sheet = wb.sheets['Sheet1']

# Write new values to cells A1:A10
for i in range(1, 11):
    sheet.range(f'A{i}').value = i * 10

# Force a full recalculation to update formulas in column B
app.application.Calculate()

# Now read the recalculated sums from column B (assuming B1:B10 contain formulas like =SUM(A$1:A1))
for i in range(1, 11):
    total = sheet.range(f'B{i}').value
    print(f'Row {i} total: {total}')

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

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.