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.

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}")