Blog

How to use Application.ODBCTimeout in the xlwings API way

The ODBCTimeout property of the Application object in Excel is a setting that controls the timeout duration, in seconds, for ODBC (Open Database Connectivity) queries. When you execute a query that retrieves data from an external database via ODBC, this property determines how long Excel will wait for the query to complete before it times out and potentially returns an error. This is particularly useful in environments where database queries might be slow due to network latency, large datasets, or server load, allowing you to adjust the wait time to suit your specific needs.

In the xlwings API, you can access this property through the Application object. The property is both readable and writable, meaning you can retrieve the current timeout value and set it to a new one as needed. The syntax for using it in xlwings is straightforward, as it maps directly to the underlying Excel object model. The value is an integer representing the number of seconds, and it can be set to any positive number, though practical limits depend on your system and requirements.

Syntax in xlwings:

• To get the current ODBCTimeout value: app.api.ODBCTimeout
• To set a new ODBCTimeout value: app.api.ODBCTimeout = seconds

Here, app refers to the xlwings App instance connected to Excel, and seconds is an integer specifying the timeout duration. For example, setting it to 0 means no timeout (wait indefinitely), while a value like 60 sets a one-minute timeout. It’s important to note that this property applies globally to the Excel application session, affecting all ODBC queries run during that session.

Code Examples:
Below are practical examples demonstrating how to use the ODBCTimeout property with xlwings in Python. These examples assume you have Excel and xlwings installed, and they show common scenarios like checking the current timeout, adjusting it for long-running queries, and resetting it.

import xlwings as xw

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

# Example 1: Retrieve the current ODBCTimeout setting
current_timeout = app.api.ODBCTimeout
print(f"Current ODBC Timeout: {current_timeout} seconds")

# Example 2: Set a new timeout value, e.g., to 120 seconds for a slow database query
app.api.ODBCTimeout = 120
print("ODBC Timeout updated to 120 seconds.")

# Example 3: Use in a context where you run an ODBC query, then reset to default
# First, increase timeout for a complex query
app.api.ODBCTimeout = 300 # 5 minutes
# Here, you would typically execute your ODBC query via Excel methods or external connections
# For demonstration, we'll just print a message
print("Running a long ODBC query with extended timeout...")
# After query, you might reset to a shorter timeout or the original value
app.api.ODBCTimeout = current_timeout # Reset to previous value
print(f"Reset ODBC Timeout back to {app.api.ODBCTimeout} seconds.")

# Example 4: Set to 0 for no timeout (use with caution to avoid hanging)
app.api.ODBCTimeout = 0
print("ODBC Timeout set to 0 (no timeout).")

How to use Application.ODBCErrors in the xlwings API way

The Application.ODBCErrors property in the Excel object model returns a collection of ODBCError objects that represent errors generated by the most recent ODBC (Open Database Connectivity) query operation. This is particularly useful for debugging and error handling when working with external databases via ODBC connections in Excel, such as when using Microsoft Query or retrieving data through SQL queries. In xlwings, this property can be accessed to programmatically inspect and respond to these errors, enabling robust data integration workflows.

In xlwings, the Application object is accessed through the app instance, typically when connecting to an existing Excel application or creating a new one. The ODBCErrors property is a read-only collection that provides details about any ODBC-related issues encountered during data retrieval. Each error in the collection includes properties like ErrorString (a description of the error) and SqlState (the SQL state code), which can be used for diagnostic purposes. Note that this collection is only populated after an ODBC operation fails; if no errors occur, it remains empty.

The syntax for accessing ODBCErrors in xlwings is straightforward. After setting up an xlwings connection to Excel, you can reference it as follows:

import xlwings as xw

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

# Access the ODBCErrors collection
odbc_errors = app.api.ODBCErrors

Here, app.api is used to access the underlying Excel object model, and ODBCErrors is called as a property. This returns a COM object representing the collection, which can be iterated over to examine individual errors. The collection supports a Count property to check the number of errors, and you can access specific errors by index (e.g., odbc_errors.Item(1)). Key parameters or attributes for each ODBCError object include:

• ErrorString: A string describing the error.
• SqlState: A five-character SQL state code indicating the error type.
• NativeError: The native error code from the ODBC data source.
  These can be retrieved in Python by calling the respective properties on each error item.

For example, consider a scenario where an ODBC query fails due to a database connection issue. The following xlwings code demonstrates how to capture and display the errors:

import xlwings as xw

# Start or connect to Excel
app = xw.apps.active

# Assume an ODBC query has been executed and failed
# Access the ODBCErrors collection
errors = app.api.ODBCErrors

# Check if any errors occurred
if errors.Count > 0:
    print(f"Number of ODBC errors: {errors.Count}")
    for i in range(1, errors.Count + 1):
        error = errors.Item(i)
        print(f"Error {i}:")
        print(f" Description: {error.ErrorString}")
        print(f" SQL State: {error.SqlState}")
        print(f" Native Error Code: {error.NativeError}")
else:
    print("No ODBC errors detected.")

How to use Application.NewWorkbook in the xlwings API way

The Application.NewWorkbook property in xlwings provides a powerful way to create a new Excel workbook programmatically. It returns a Workbook object representing the newly created workbook, allowing for immediate manipulation of its contents, sheets, and properties. This functionality is essential for automating report generation, data processing workflows, or any task requiring the dynamic creation of Excel files without manual intervention.

Functionality
The primary function of Application.NewWorkbook is to generate a fresh, blank workbook in Excel. This new workbook becomes the active workbook, and you can start adding data, formatting, or charts right away. It is particularly useful in scenarios where you need to produce multiple output files from a single data source or when building templates on the fly. Unlike simply opening an existing file, this method ensures you begin with a clean slate, adhering to default Excel settings unless otherwise modified.

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

new_wb = app.books.add()

While the VBA object model uses Application.NewWorkbook, xlwings employs the books.add() method as the direct equivalent. The add() method can accept several optional parameters to customize the new workbook:

• Template: A string specifying the path to an Excel template file (.xltx, .xltm). If provided, the new workbook is based on this template instead of the default blank workbook.
• Before: A Sheet object. The new workbook is placed before this specified sheet (relevant when adding within a specific workbook context, though typically used with app.books.add() for a new file).
• After: A Sheet object. The new workbook is placed after this specified sheet.

For most common use cases, calling app.books.add() without arguments is sufficient. The method returns a Book object (xlwings’ term for a Workbook), which you can assign to a variable for further operations.

Code Examples

1. Creating a Simple New Workbook and Adding Data:

import xlwings as xw

# Start a new Excel instance (visible=False for background operation)
app = xw.App(visible=True)

# Create a new workbook
new_workbook = app.books.add()

# Access the first sheet and write data
sheet = new_workbook.sheets[0]
sheet.range('A1').value = 'Product'
sheet.range('B1').value = 'Sales'
sheet.range('A2').value = ['Widget A', 'Widget B', 'Widget C']
sheet.range('B2').value = [1500, 2100, 1850]

# Save the workbook
new_workbook.save(r'C:\Reports\NewReport.xlsx')
# new_workbook.close()
# app.quit()

2. Creating a Workbook from a Template:

import xlwings as xw

app = xw.App(visible=False)

# Specify the path to your template
template_path = r'C:\Templates\CompanyReport.xltx'

# Create a new workbook based on the template
new_wb = app.books.add(template_path)

# The new workbook already contains the template's formatting and sheets.
# You can populate predefined cells.
new_wb.sheets['Data'].range('C5').value = 'Q4-2023'
new_wb.sheets['Summary'].range('B10').value = 95000

# Save it with a new name
new_wb.save(r'C:\Reports\Q4_Report_Final.xlsx')
app.quit()

3. Creating Multiple Workbooks in a Loop:

import xlwings as xw

app = xw.App(visible=False)

departments = ['Sales', 'Marketing', 'Engineering', 'HR']

for dept in departments:
    # Create a new workbook for each department
    wb = app.books.add()
    wb.sheets[0].name = dept
    wb.sheets[0].range('A1').value = f'Department: {dept}'
    # ... add more department-specific data ...
    wb.save(fr'C:\DepartmentReports\{dept}_Data.xlsx')
    wb.close() # Close the workbook after saving to free memory

app.quit()

How to use Application.NetworkTemplatesPath in the xlwings API way

The Application.NetworkTemplatesPath property is a member of the Excel Object Model that returns a String representing the full network path where Microsoft Excel stores templates that are available to all users on a network. This path is typically set through Excel’s options or via group policy in an enterprise environment. In the context of xlwings, this property provides a convenient way to programmatically determine the central location for shared workbook and worksheet templates, enabling scripts to dynamically locate and utilize these resources for report generation, data standardization, and template-driven automation. Accessing this path via xlwings allows for robust, location-agnostic code that adapts to the specific network configuration of the deployment environment.

Functionality
The primary function is to retrieve the read-only network templates directory path. It is useful for operations such as:

• Opening a network template to create a new workbook.
• Saving a custom template to the shared network location for team-wide access.
• Listing available templates in the directory for user selection in a custom dialog.

Syntax in xlwings
The property is accessed through the xlwings App object, which represents the Excel application.

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

# Access the NetworkTemplatesPath property
network_path = app.api.NetworkTemplatesPath

• app: An xlwings App object.
• .api: This property provides direct access to the underlying Excel Application object’s API (the COM object).
• .NetworkTemplatesPath: The specific property call. It takes no parameters.

The return value is a Python string (str) containing the full UNC (Universal Naming Convention) path, e.g., "\\fileserver\companydata\ExcelTemplates". If no network path is configured, it may return an empty string ("").

Code Examples

1. Opening a Workbook from the Network Templates Path:
   This example checks if the path is configured and opens a specific template file from it.

import xlwings as xw
import os

app = xw.apps.active
base_path = app.api.NetworkTemplatesPath

if base_path:
    template_file = "Monthly_Report.xltx"
    full_path = os.path.join(base_path, template_file)

    if os.path.exists(full_path):
        # Opens the template, creating a new workbook based on it
        new_wb = app.books.open(full_path)
        print(f"Opened template from: {full_path}")
        # ... perform operations on new_wb ...
    else:
        print(f"Template file not found at {full_path}")
else:
    print("Network Templates Path is not configured.")

2. Saving a Custom Template to the Network Location:
   This example saves the active workbook as a template (.xltx) to the shared network directory.

import xlwings as xw
import os

app = xw.apps.active
wb = app.books.active
network_path = app.api.NetworkTemplatesPath

if network_path:
    # Ensure the directory exists (Excel usually manages this)
    if not os.path.isdir(network_path):
        os.makedirs(network_path)

    template_name = "Data_Analysis_Template.xltx"
    save_path = os.path.join(network_path, template_name)

    # Save the active workbook as a template
    # Note: The `FileFormat` parameter for .xltx is 54 (xlOpenXMLTemplate).
    # We use the .api to access the SaveAs method with specific parameters.
    wb.api.SaveAs(Filename=save_path, FileFormat=54)
    print(f"Template saved successfully to: {save_path}")
else:
    print("Cannot save template. Network Templates Path is not set.")

3. Listing Available Templates:
   This script retrieves and prints a list of all Excel template files (.xltx, .xltm, .xlt) in the network directory.

import xlwings as xw
import os

app = xw.apps.active
network_path = app.api.NetworkTemplatesPath

if network_path and os.path.isdir(network_path):
    template_extensions = ('.xltx', '.xltm', '.xlt')
    all_files = os.listdir(network_path)
    template_files = [f for f in all_files if     f.lower().endswith(template_extensions)]

    print(f"Templates found in '{network_path}':")
    for template in template_files:
        print(f" - {template}")
else:
    print("Network Templates Path is either not configured or not accessible.")

How to use Application.Names in the xlwings API way

The Names member of the Application object in Excel’s object model is a powerful collection that represents all the defined names within a workbook or application scope. In xlwings, this is accessed through the app.names property. Defined names are essentially named ranges or constants that make formulas more readable and dynamic. They can refer to a single cell, a range of cells, a constant value, or even a formula. Using the Names collection via xlwings allows you to programmatically create, modify, retrieve, and delete these names, which is crucial for building robust, maintainable Excel-based automation and data models.

The primary syntax in xlwings for interacting with this collection is through the app.names property, which returns a Names collection object. You can also access it via a specific workbook: wb.names. Key methods and properties include:

• add(name, refers_to): Creates a new defined name. name is a string for the name (cannot contain spaces and must begin with a letter or underscore). refers_to is a string defining the reference, using standard Excel notation (e.g., "=Sheet1!$A$1:$D$10" or "=5" for a constant).
• item(index_or_name): Returns a specific Name object, either by its string name or its numerical index in the collection.
• count: Returns the number of defined names in the collection.
• On a Name object, key properties are name (to get or set the name text) and refers_to (to get or set the reference formula string). The delete() method removes the name.

Here are practical xlwings code examples demonstrating the use of the Application.Names member:

import xlwings as xw

# Connect to the active Excel instance and its active workbook
app = xw.apps.active
wb = app.books.active

# Example 1: Adding a new defined name for a range
# This creates a name "DataRange" referring to cells A1:D20 on Sheet1
wb.names.add(name="DataRange", refers_to="=Sheet1!$A$1:$D$20")

# Example 2: Adding a named constant
# This creates a name "TaxRate" with a constant value of 0.075
app.names.add(name="TaxRate", refers_to="=0.075")

# Example 3: Retrieving and inspecting a defined name
# Get a specific name object and print its details
try:
    data_name = wb.names["DataRange"]
    print(f"Name: {data_name.name}")
    print(f"Refers to: {data_name.refers_to}")
except KeyError:
    print("Name not found.")

# Example 4: Iterating through all defined names in the workbook
print(f"\nTotal names in workbook: {wb.names.count}")
for name_obj in wb.names:
    print(f" - {name_obj.name}: {name_obj.refers_to}")

# Example 5: Modifying an existing name's reference
# Change the "DataRange" to refer to a dynamic range using the OFFSET function
data_name = wb.names["DataRange"]
data_name.refers_to = "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)"

# Example 6: Deleting a defined name
app.names["TaxRate"].delete()

How to use Application.Name in the xlwings API way

The Name member of the Application object in Excel refers to the name of the application itself, which is typically “Microsoft Excel”. In xlwings, this property is accessed through the app object, which represents the Excel application instance. It is a read-only property that returns a string. This can be useful for verifying the application environment, logging, or conditional logic in automation scripts that might interact with different versions or instances of Excel.

Syntax in xlwings:

app.name

• app: An instance of the xlwings App class, representing the Excel application.
• .name: The property that returns the application’s name as a string. No parameters are required.

Example Usage:
The primary use is to retrieve the application name. Here is a basic example:

import xlwings as xw

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

# Get the application name
app_name = app.name
print(f"The application name is: {app_name}") # Output: The application name is: Microsoft Excel

This property is straightforward and primarily serves informational purposes. For instance, in a more complex script, you might check the application name as part of a setup or validation routine:

import xlwings as xw

def initialize_excel_session():
app = xw.App(visible=True) # Start a new Excel application
if app.name == "Microsoft Excel":
    print("Excel application started successfully.")
    # Proceed with further operations like opening workbooks
    wb = app.books.add()
    # ... other code ...
else:
    print("Unexpected application. Script may not function correctly.")
app.quit()

initialize_excel_session()

Another scenario could involve logging details about the Excel environment for debugging or audit trails:

import xlwings as xw
import logging

logging.basicConfig(level=logging.INFO)
app = xw.apps.active
logging.info(f"Connected to {app.name} (Version: {app.version})")

How to use Application.MultiThreadedCalculation in the xlwings API way

The MultiThreadedCalculation property of the Application object in Excel is a key feature for enhancing performance in computationally intensive workbooks. It controls whether Excel uses multiple processor threads to recalculate formulas, which can significantly speed up calculation times on multi-core systems. This property is particularly useful for large datasets, complex models, or workbooks with numerous volatile functions. By enabling multi-threaded calculation, Excel can distribute the recalculation workload across available CPU cores, leading to more efficient processing. However, it’s important to note that not all calculations can be parallelized; some dependent formulas may still require sequential processing. The property is part of Excel’s calculation engine settings and can be managed programmatically via xlwings to optimize performance based on the workbook’s needs.

In xlwings, the MultiThreadedCalculation property is accessed through the Application object. The syntax for getting or setting this property is straightforward. It returns or accepts a boolean value: True enables multi-threaded calculation, and False disables it, forcing Excel to use a single thread. There are no additional parameters for this property. To use it, you reference the application instance from an xlwings App or Book object. For example, app.api.MultiThreadedCalculation allows direct access, where app is an xlwings App instance. This property is read/write, so you can both retrieve the current setting and modify it as needed.

Here is an example of using the MultiThreadedCalculation property with xlwings in Python. First, ensure you have xlwings installed and an Excel instance running. The code below demonstrates how to check the current setting, enable multi-threaded calculation if it’s disabled, and then verify the change. This can be integrated into scripts that prepare Excel for heavy calculations, such as in data analysis or financial modeling tasks.

import xlwings as xw

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

# Get the current MultiThreadedCalculation setting
current_setting = app.api.MultiThreadedCalculation
print(f"Current MultiThreadedCalculation setting: {current_setting}")

# Enable multi-threaded calculation if it's disabled
if not current_setting:
    app.api.MultiThreadedCalculation = True
    print("MultiThreadedCalculation has been enabled.")

    # Verify the new setting
    updated_setting = app.api.MultiThreadedCalculation
    print(f"Updated MultiThreadedCalculation setting: {updated_setting}")

# Example of using it in a workbook context
wb = app.books.active
# Perform some operations that benefit from multi-threading, like recalculating
wb.api.Calculate()
print("Workbook recalculated with multi-threaded calculation enabled.")

# Optionally, disable it later if needed for debugging or compatibility
# app.api.MultiThreadedCalculation = False