Archive

How to use Range.DialogBox in the xlwings API way

The DialogBox member of the Range object in the Excel object model is accessible through the xlwings API, enabling programmatic interaction with Excel’s built-in dialog boxes. This functionality is particularly useful for automating tasks that involve user interface elements, such as opening files, printing, or applying filters, directly from a Python script. By leveraging DialogBox, developers can simulate the actions a user would take through dialog boxes, thereby integrating these UI-driven processes into automated workflows.

Functionality:
The DialogBox method displays the Excel dialog box associated with the specified Range object. This is often used to trigger dialogs related to the range’s context, such as the “Format Cells” dialog for formatting options or the “Sort” dialog for sorting data. In xlwings, this method allows you to open these dialogs programmatically, though note that it typically pauses execution until the dialog is closed by the user, as it mimics interactive behavior. This can be beneficial for scenarios where you want to prompt users for input or confirm actions within an automated sequence.

Syntax:
In xlwings, the DialogBox method is called on a Range object. The syntax is straightforward, as it does not take additional parameters in its basic form. The method corresponds to the VBA Range.DialogBox method, which in Excel VBA might accept arguments for specific dialog types, but in xlwings, it is generally used without parameters to open the default dialog associated with the range. The call format is:

range_object.api.DialogBox()

Here, range_object is an xlwings Range object (e.g., obtained via sheet.range('A1') or app.range('A1')), and .api accesses the underlying Excel object model. The DialogBox method is invoked directly without arguments, as it relies on Excel’s internal context to determine which dialog to display based on the range’s properties or current selection.

Parameters:
The DialogBox method in xlwings does not explicitly require parameters; however, its behavior is context-dependent. In Excel’s object model, the DialogBox method can sometimes accept optional arguments to specify dialog options, but these are not commonly exposed in xlwings. Instead, the method opens the dialog that Excel deems appropriate for the range. For example, if the range is part of a table, it might open a filtering dialog. Developers should refer to Excel’s official documentation for dialog-specific details, as xlwings primarily wraps the native functionality without extensive customization.

Example Code:
Below is an xlwings API code example demonstrating the use of Range.DialogBox. This example assumes you have an Excel application running and a workbook open. It selects a range and opens the associated dialog box, such as the “Format Cells” dialog for a cell range.

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active if xw.apps.active else xw.App()
wb = app.books.active # Assume a workbook is open
sheet = wb.sheets.active

# Define a range object, e.g., cell A1
target_range = sheet.range('A1')

# Open the dialog box associated with the range
# This will pause the script until the dialog is closed by the user
target_range.api.DialogBox()

# Continue with other operations after the dialog closes
print("Dialog box closed. Proceeding with automation...")

# Example: Set a value after dialog interaction
target_range.value = "Dialog completed"

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

How to use Range.Delete in the xlwings API way

The Delete method of the Range object in Excel is a powerful tool for removing cells, rows, or columns from a worksheet, shifting the surrounding cells to fill the space. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. Understanding how to use Delete effectively is essential for data manipulation and cleanup tasks.

Functionality:
The Delete method removes the specified range and shifts adjacent cells. The direction of the shift is determined by the Shift argument. This is useful for deleting unwanted data, removing blank rows/columns, or dynamically restructuring a worksheet. Unlike simply clearing contents, Delete physically removes the cells, altering the worksheet layout.

Syntax:
In xlwings, the syntax for calling the Delete method is:

range.api.Delete(Shift)

Where range is an xlwings Range object, and Shift is an optional parameter that specifies how to shift cells to replace the deleted ones. The Shift parameter can be one of the following constants from the xlwings.constants module (or their equivalent numeric values):

Constant (from xlwings.constants)ValueDescription
xlShiftToLeft-4159Shifts cells to the left.
xlShiftUp-4162Shifts cells upward.

If the Shift argument is omitted, Excel will default to shifting cells up (xlShiftUp) for entire rows and to the left (xlShiftToLeft) for entire columns. For smaller ranges that are not entire rows/columns, Excel uses the default based on the shape of the range.

Examples:
Here are several xlwings API code instances demonstrating the use of Range.Delete.

  1. Delete a single cell and shift up:
import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
sheet = wb.sheets['Sheet1']
# Delete cell B2 and shift the cells below it upward
sheet.range('B2').api.Delete()
wb.save()
app.quit()
  1. Delete a range of rows:
import xlwings as xw
from xlwings.constants import DeleteShiftDirection
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
sheet = wb.sheets['Sheet1']
# Delete rows 5 through 7, shifting cells up
sheet.range('5:7').api.Delete(Shift=DeleteShiftDirection.xlShiftUp)
wb.save()
app.quit()
  1. Delete a column and shift left:
import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
sheet = wb.sheets['Sheet1']
# Delete column C, shifting columns to the right of it leftward
sheet.range('C:C').api.Delete()
wb.save()
app.quit()
  1. Delete a non-contiguous range:
import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
sheet = wb.sheets['Sheet1']
# Delete cells A1:B2 and D1:D3, shifting cells up for each area
delete_range = sheet.range('A1:B2, D1:D3')
delete_range.api.Delete()
wb.save()
app.quit()

How to use Range.DataSeries in the xlwings API way

The DataSeries member of the Range object in Excel VBA is a powerful feature for creating and manipulating data series within charts. When using xlwings in Python, this functionality is accessed through the api property, which exposes the underlying VBA object model. The DataSeries object itself is typically a collection (SeriesCollection) associated with a Chart object, but it is often referenced from a specific range of data that defines the series values or categories. In xlwings, you primarily interact with chart series via the Chart object, but understanding how ranges feed into data series is crucial.

Functionality:
The DataSeries (or more precisely, a series within a SeriesCollection) represents a single data series in a chart, such as a line in a line chart or a set of columns in a column chart. Key operations include defining the source data range for series values (e.g., y-values) and category labels (e.g., x-values), formatting the series, and modifying its properties. Through xlwings, you can automate the creation and customization of charts by setting these ranges programmatically.

Syntax and Parameters:
In xlwings, you typically work with the Series object from a chart’s series_collection. To set or get the range defining the series values, you use the values property. For category labels, use the x_values property. These properties accept a range address string or an xlwings Range object. The general approach is:

series = chart.series_collection[0] # Access the first series
series.values = sheet.range("B2:B10").api # Set values range via .api
series.x_values = sheet.range("A2:A10").api # Set categories range via .api

Here, chart is an xlwings Chart object, and sheet is an xlwings Sheet object. The .api property returns the underlying VBA-compatible object, allowing you to assign ranges directly. Parameters:

  • values: Specifies the range containing the data points (y-values) for the series. It can be a string like "Sheet1!B2:B10" or a VBA Range object.
  • x_values: Specifies the range for category labels (x-values). It is optional; if omitted, Excel uses sequential numbers.

Example Code:
Suppose you have an Excel workbook with sales data in columns A (Month) and B (Sales), and you want to create a line chart. The following xlwings code demonstrates how to define the data series from a range:

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.Book.active
sheet = wb.sheets["Sheet1"]

# Create a chart object
chart = sheet.charts.add(left=100, top=10, width=400, height=300)
chart.set_source_data(sheet.range("A1:B10")) # Set overall data range

# Access the first series in the chart and modify its data range via .api
series = chart.api.SeriesCollection(1) # Using .api for VBA-like access
series.Values = sheet.range("B2:B10").api # Set values to Sales column
series.XValues = sheet.range("A2:A10").api # Set categories to Month column

# Alternatively, use xlwings' higher-level methods (if available) for simplicity
# For instance, you can also set series data directly during chart creation:
chart = sheet.charts.add()
chart.chart_type = "line"
chart.series_collection.add(sheet.range("B2:B10"), rowcol="columns", series_from="columns")

How to use Range.DataTypeToText in the xlwings API way

The DataTypeToText property of the Range object in Excel is a powerful feature that allows you to convert cells containing data types (such as Stocks or Geography) into their corresponding textual representations. This is particularly useful when you have cells linked to external data services and you need to extract the displayed text for further analysis or reporting. In xlwings, you can access this property to programmatically retrieve these text values, enabling seamless integration of Excel’s dynamic data types into your Python workflows.

Functionality
The primary function of DataTypeToText is to return the text displayed in a cell that contains a linked data type. For example, if a cell has a stock data type showing “Microsoft Corp.” as its text, this property will return that string. It works on ranges that have been converted to data types via Excel’s built-in features. If the cell does not contain a data type, it may return an error or an empty value, so it’s important to handle such cases in your code.

Syntax in xlwings
In xlwings, you can call DataTypeToText as a property on a Range object. The syntax is straightforward:

range_object.data_type_to_text

This property is read-only and does not accept any parameters. It returns a string or a list of strings (for multi-cell ranges) representing the text from the data types. If applied to a range with multiple cells, it will return a list where each element corresponds to the text in each cell.

Parameters and Values
Since DataTypeToText is a property, there are no parameters to specify. However, it’s essential to ensure that the target range contains valid data types. You can check this by verifying the cell’s Value2 or formatting before accessing the property. In case of errors, xlwings may raise exceptions, so using try-except blocks is recommended for robust code.

Code Examples
Here are practical examples of using DataTypeToText with xlwings:

  1. Retrieving Text from a Single Cell with a Stock Data Type
    Assume cell A1 in an Excel workbook contains a stock data type for Microsoft.
import xlwings as xw
# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets['Sheet1']
# Get the range and access DataTypeToText
cell = sheet.range('A1')
text_value = cell.data_type_to_text
print(f"Text from data type: {text_value}") # Output: e.g., "Microsoft Corp."
  1. Handling a Range with Multiple Data Type Cells
    If you have a column of stock data types from A1 to A5, you can extract all text values at once.
import xlwings as xw
wb = xw.books.active
sheet = wb.sheets['Sheet1']
range_cells = sheet.range('A1:A5')
text_list = range_cells.data_type_to_text
for i, text in enumerate(text_list, start=1):
    print(f"Cell A{i}: {text}")
    # This will print the text for each cell, like ["Apple Inc.", "Tesla Inc.", ...]
  1. Error Handling for Cells Without Data Types
    To avoid crashes when a cell lacks a data type, implement error checking.
import xlwings as xw
wb = xw.books.active
sheet = wb.sheets['Sheet1']
cell = sheet.range('B1') # Assume B1 has regular text or a number
try:
    text_value = cell.data_type_to_text
    if text_value:
        print(f"Data type text: {text_value}")
    else:
        print("No data type text available.")
except Exception as e:
    print(f"Error accessing data type: {e}")

How to use Range.Cut in the xlwings API way

The Range.Cut method in Excel’s object model is a powerful tool for moving a range of cells to a new location, effectively performing a cut-and-paste operation. In xlwings, this functionality is exposed through the cut method of a Range object. It is particularly useful when you need to relocate data while preserving cell formatting, formulas, or other properties, as it mimics the standard cut action in the Excel user interface.

Functionality
The primary purpose of the cut method is to move the contents and formatting of a specified range to a different range or destination. Unlike a simple copy, a cut operation typically removes the original data from its source location after pasting, though in xlwings, the behavior aligns with Excel’s programmatic actions where the source range remains until the cut is pasted elsewhere. This method is often used in data reorganization, report generation, or when integrating data from multiple sheets.

Syntax
In xlwings, the syntax for the cut method is straightforward:

Range.cut(destination=None)
  • destination (optional): This parameter specifies the target range where the cut cells will be pasted. It can be a Range object or a string representing the cell address (e.g., "A1" or "Sheet2!B5"). If omitted, the cut range is placed on the clipboard, and you would need to use the paste method separately to complete the operation. In practice, providing a destination directly is more efficient for automation.

Example Usage
Here is a practical example demonstrating the use of cut in xlwings. Suppose you have an Excel workbook with data in a range that needs to be moved to another location within the same worksheet or a different one.

import xlwings as xw

# Connect to the active Excel workbook
wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Define the source range to cut (e.g., cells A1 to C3)
source_range = sheet.range('A1:C3')

# Cut the source range and paste it to a new destination (e.g., starting at E1)
source_range.cut(destination=sheet.range('E1'))

# Alternatively, cut without specifying a destination and then paste
source_range.cut() # Cuts to clipboard
sheet.range('G1').paste() # Pastes from clipboard to G1

# For moving to another sheet, specify the sheet in the destination
other_sheet = wb.sheets['Sheet2']
source_range.cut(destination=other_sheet.range('A1'))

How to use Range.CreateNames in the xlwings API way

The CreateNames method in the Excel object model is a powerful feature for automating the creation of named ranges from a selection. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This method is particularly useful when you have a range where the top row, left column, bottom row, or right column contains labels that you want to use as names for adjacent cells or ranges. By using CreateNames, you can programmatically define these names, enhancing workbook organization and making formulas more readable and maintainable.

Functionality
The CreateNames method generates named ranges based on the labels located at the edges of a specified range. It examines the selected range and uses the labels in the top row, left column, bottom row, or right column to create names for the contiguous cells in the opposite direction. For example, if you have labels in the top row, it will create names for the columns below each label. This is especially beneficial in scenarios like financial models or data tables where you need to assign descriptive names to data blocks dynamically.

Syntax
In xlwings, you invoke CreateNames via the api property of a Range object. The method signature is:

range.api.CreateNames(Top, Left, Bottom, Right)
  • Top (Optional, Boolean): If True, uses labels in the top row to create names. Default is False.
  • Left (Optional, Boolean): If True, uses labels in the left column to create names. Default is False.
  • Bottom (Optional, Boolean): If True, uses labels in the bottom row to create names. Default is False.
  • Right (Optional, Boolean): If True, uses labels in the right column to create names. Default is False.

All parameters are optional, and if none are specified, Excel will guess the label positions based on the range’s layout. Typically, you set one of these to True depending on where your labels are located. Note that the method corresponds to the “Create from Selection” feature in Excel’s Formulas tab.

Examples
Here are practical xlwings API code instances demonstrating the use of CreateNames:

  1. Creating names from top row labels: Suppose you have a range where the first row contains headers like “Revenue”, “Expenses”, and you want to name the data below each header.
import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Define the range including headers and data (e.g., A1:C10)
target_range = sheet.range('A1:C10')

# Use CreateNames with Top=True to generate names based on top row
target_range.api.CreateNames(Top=True)

# This will create named ranges like "Revenue" for A2:A10, "Expenses" for B2:B10, etc.
  1. Creating names from left column labels: If your labels are in the first column and you want to name the cells to the right.
import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['Data']

# Range with labels in column A and data in columns B onward (e.g., A1:D5)
target_range = sheet.range('A1:D5')

# Use CreateNames with Left=True
target_range.api.CreateNames(Left=True)

# This creates names like "Jan" for B1:D1 if A2 contains "Jan", and so on.
  1. Combining multiple directions: In complex tables, you might have labels on both top and left. You can call CreateNames multiple times or use it with caution, as overlapping names may cause conflicts. Here’s an example for top and left:
import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Assume a table with top row headers and left column labels
target_range = sheet.range('A1:E6')

# First, create names from top row
target_range.api.CreateNames(Top=True)
# Then, create names from left column (if needed, but note potential overwrites)
target_range.api.CreateNames(Left=True)

# It's advisable to manage ranges separately to avoid errors.
  1. Using without parameters for automatic detection: If the range has a clear layout, you can let Excel decide.
import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['Report']

# Select a range where labels are intuitively placed (e.g., A1:B10 with headers in row 1)
target_range = sheet.range('A1:B10')
target_range.api.CreateNames() # Excel guesses label positions

# This is useful for quick automation but may require verification in complex cases.

How to use Range.CopyPicture in the xlwings API way

The Range.CopyPicture member in Excel’s object model allows you to copy a range of cells as a picture, which can then be pasted into other locations within Excel or into external applications. This is particularly useful for creating visual snapshots of data, charts, or formatted tables that need to be shared or used in reports, presentations, or dashboards. In xlwings, this functionality is accessible through the API, enabling automation of picture-copying tasks directly from Python scripts.

The syntax for CopyPicture in xlwings follows the Excel object model closely. It is a method that can be called on a Range object. The method takes two parameters: Appearance and Format. These parameters determine how the picture is copied, affecting its visual quality and file format. In xlwings, you invoke it as range_object.api.CopyPicture(Appearance, Format), where range_object is an instance of xlwings’ Range class. The parameters are optional in Excel, but when specified in xlwings, they should be passed as constants or integer values.

The Appearance parameter controls whether the picture is copied as it appears on screen or as it would look when printed. It accepts two primary values: xlScreen (value 1) for screen appearance, which is the default, and xlPrinter (value 2) for printer appearance, which may include higher resolution or print-specific settings. The Format parameter defines the picture format, with key options being xlPicture (value -4147) for bitmap format and xlBitmap (value 2) for a bitmap image. In practice, xlPicture is commonly used for copying ranges as pictures in Excel. If parameters are omitted, Excel uses default values, typically xlScreen and xlPicture.

Here is a table summarizing the parameter options:

ParameterValue (Constant)Integer ValueDescription
AppearancexlScreen1Copies the range as it appears on screen.
xlPrinter2Copies the range as it would look when printed.
FormatxlPicture-4147Copies in picture format (default).
xlBitmap2Copies as a bitmap image.

To use CopyPicture in xlwings, you first need to establish a connection to an Excel application and workbook. After copying the range as a picture, you can paste it elsewhere using the Paste method on a destination range or sheet. Below is a code example that demonstrates this process:

import xlwings as xw

# Connect to the active Excel instance or create a new one
app = xw.apps.active
wb = app.books.active
sheet = wb.sheets['Sheet1']

# Define the source range to copy as a picture (e.g., cells A1 to D10)
source_range = sheet.range('A1:D10')

# Copy the range as a picture with screen appearance and picture format
source_range.api.CopyPicture(Appearance=1, Format=-4147) # Using integer values for clarity

# Specify a destination range to paste the picture (e.g., starting at cell F1)
dest_range = sheet.range('F1')
sheet.api.Paste(Destination=dest_range.api) # Paste the picture at the destination

# Alternatively, you can use constants from the xlwings.constants module for better readability
import xlwings.constants as win32c
source_range.api.CopyPicture(Appearance=win32c.xlScreen, Format=win32c.xlPicture)
sheet.api.Paste(Destination=dest_range.api)

# Save the workbook if needed
wb.save()

How to use Range.CopyFromRecordset in the xlwings API way

The CopyFromRecordset method in Excel’s object model is a powerful feature that allows developers to efficiently populate a range of cells with data from an ADO or DAO Recordset object. In the context of xlwings, a Python library that enables interaction with Excel through its COM interface, this method can be accessed via the Range object’s API. It is particularly useful for automating data import from databases directly into Excel worksheets without manual copying and pasting, thereby enhancing performance when dealing with large datasets.

Functionality:
The primary function of CopyFromRecordset is to transfer data from a Recordset—a data structure representing a set of records from a database—into a specified Excel range. This method starts inserting data at the top-left cell of the target range and fills it row by row, column by column, based on the Recordset’s fields and records. It automatically adjusts to the size of the data, making it a dynamic and efficient way to load data. In xlwings, this is leveraged through COM automation, allowing Python scripts to interact with Excel instances seamlessly.

Syntax in xlwings:
In xlwings, the CopyFromRecordset method is called on a Range object. The syntax follows the pattern of Excel’s VBA object model but adapted for Python. The general format is:

range_object.api.CopyFromRecordset(Recordset, MaxRows, MaxColumns)
  • Recordset: This is a required parameter representing the ADO or DAO Recordset object that contains the data to be copied. In Python, this typically involves using libraries like pyodbc or adodbapi to create the Recordset from a database query.
  • MaxRows (Optional): An optional Variant parameter specifying the maximum number of rows to copy. If omitted, all rows in the Recordset are copied.
  • MaxColumns (Optional): An optional Variant parameter specifying the maximum number of columns to copy. If omitted, all columns in the Recordset are copied.

Example with xlwings:
Below is a practical example demonstrating how to use CopyFromRecordset with xlwings. This example assumes you have an Excel workbook open and a database connection established via pyodbc to fetch data.

import xlwings as xw
import pyodbc

# Connect to an Excel workbook
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']

# Establish a database connection and create a Recordset
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db;UID=user;PWD=password')
cursor = conn.cursor()
cursor.execute("SELECT * FROM your_table")
# In pyodbc, the cursor itself can be treated as a Recordset-like object for CopyFromRecordset
recordset = cursor

# Define the target range in Excel (starting at cell A1)
target_range = sheet.range('A1')

# Use CopyFromRecordset to populate the range with data
target_range.api.CopyFromRecordset(recordset)

# Close the database connection
cursor.close()
conn.close()

# Save and close the workbook
wb.save()
wb.close()

How to use Range.Copy in the xlwings API way

The Copy method in Excel’s Range object is a fundamental operation for duplicating cell contents, formatting, and formulas to another location within the same workbook or to a different workbook. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model. This allows for precise control over the copy operation, including specifying a destination range.

Functionality
The primary purpose of the Copy method is to replicate a source range. It copies all elements: values, formulas, number formats, cell formatting (like borders and fill), and column widths/row heights if the entire column/row is selected. This is equivalent to performing a standard copy-paste operation in the Excel UI. The method can be used without a destination to copy to the Clipboard, or with a destination to paste directly.

Syntax
The xlwings API call follows the pattern of the Excel VBA Range.Copy method.

range_object.api.Copy(Destination)
  • range_object: Your xlwings Range object (e.g., sheet.range('A1:B2')).
  • .api: The gateway to the native Excel object model.
  • .Copy: The method itself.
  • Destination (Optional): An xlwings Range object specifying the top-left cell of the destination range. If omitted, the range is copied to the Clipboard.

Parameter Details

ParameterTypeDescriptionRequired
Destinationxlwings Range ObjectThe target cell where the copied range will be pasted. Only the top-left cell needs to be specified; the paste area will match the size of the source range.No

Code Examples

  1. Copy to a specific destination (direct paste):
    This is the most common use case. It copies A1:D5 from Sheet1 and pastes it starting at cell F1 on the same sheet.
import xlwings as xw
wb = xw.Book.active
sht = wb.sheets['Sheet1']
source_range = sht.range('A1:D5')
destination_cell = sht.range('F1')
source_range.api.Copy(Destination=destination_cell.api)
  1. Copy to the Clipboard:
    By not providing a Destination argument, the range is placed on the Windows/macOS Clipboard. Another application (like another Excel instance or Notepad) can then paste it.
import xlwings as xw
sht = xw.Book.active.sheets[0]
sht.range('A1:A10').api.Copy() # Data is now on the clipboard
  1. Copy between different worksheets:
    This example copies data from Sheet1 to Sheet2, starting at cell A1 on the target sheet.
import xlwings as xw
wb = xw.Book.active
source_sht = wb.sheets['Sheet1']
target_sht = wb.sheets['Sheet2']
source_sht.range('C10:F20').api.Copy(Destination=target_sht.range('A1').api)
  1. Copy using a Range object variable for the destination:
    This demonstrates using a pre-defined Range object as the target, which can be useful in dynamic scripts.
import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('report.xlsx')
source = wb.sheets['Data'].range('Table1[#All]') # Copy an entire Table
target_start = wb.sheets['Summary'].range('B5')
source.api.Copy(Destination=target_start.api)
wb.save()
app.quit()

How to use Range.ConvertToLinkedDataType in the xlwings API way

The ConvertToLinkedDataType method in Excel, accessible through the xlwings API, is a powerful feature for converting static data in a range into dynamic linked data types. This functionality is particularly useful when working with data types like Stocks or Geography, where Excel can pull real-time or updated information from online sources. By converting a range to a linked data type, you enable automatic data refresh and access to rich, structured data fields without manual updates.

In xlwings, this method is called on a Range object. The syntax is straightforward: range_object.api.ConvertToLinkedDataType(ServiceID, LanguageCulture). Here, range_object refers to the Excel range you want to convert, typically selected using xlwings methods like sheet.range() or app.range(). The api property grants access to the underlying Excel object model, allowing direct use of Excel VBA-equivalent methods. The method takes two parameters: ServiceID and LanguageCulture. ServiceID is an optional parameter that specifies the linked data service to use; for example, use "Finance" for stock data or "Geography" for geographic information. If omitted, Excel will attempt to auto-detect the service based on the data content. LanguageCulture is also optional and sets the language and culture for the data, such as "en-US" for English (United States). If not provided, it defaults to the application’s current language settings.

For example, suppose you have a list of company ticker symbols in cells A1:A5 of an Excel worksheet, and you want to convert them to linked stock data types to fetch real-time financial information. Using xlwings, you can achieve this with a few lines of Python code. First, ensure xlwings is installed and import it. Then, connect to Excel, specify the target range, and call ConvertToLinkedDataType. Below is a code snippet demonstrating this:

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.apps.active
wb = app.books.active
sheet = wb.sheets['Sheet1']

# Define the range containing ticker symbols (e.g., "MSFT", "AAPL")
target_range = sheet.range('A1:A5')

# Convert the range to linked data type for stocks
target_range.api.ConvertToLinkedDataType(ServiceID="Finance", LanguageCulture="en-US")

# Save and close if needed
wb.save()