Archive

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()