How to use Worksheet.QueryTables in the xlwings API way

The QueryTables member of the Worksheet object in Excel’s object model represents a collection of QueryTable objects, each of which corresponds to a data query table that retrieves and refreshes data from an external source, such as a database, web page, or text file. In xlwings, this collection can be accessed and manipulated to automate data import and management tasks directly from Python. This functionality is particularly useful for automating repetitive data refresh operations or integrating external data feeds into Excel workbooks programmatically.

Functionality:
The QueryTables collection allows you to:

• Access existing query tables on a specific worksheet.
• Add new query tables (though note that creating new query tables via xlwings may require using the underlying Excel object model via the .api property, as xlwings does not have a dedicated high-level method for this).
• Refresh data in query tables to update the imported information.
• Modify properties of query tables, such as connection strings or refresh settings.

Syntax:
In xlwings, you access the QueryTables collection through a Worksheet object. The basic syntax is:

worksheet.api.QueryTables

This returns a COM object representing the collection, which you can then iterate over or access by index. For example, to get the first query table on a worksheet:

first_query_table = worksheet.api.QueryTables(1)

Common methods and properties include:

• Refresh(): Refreshes the query table to update data.
• BackgroundQuery: A property that can be set to True or False to control whether the query runs in the background.
• Connection: A property that holds the connection string for the data source.

Example:
Below is a practical example demonstrating how to use the QueryTables member in xlwings to refresh all query tables on a worksheet and adjust a property. This assumes you have an existing Excel workbook with query tables set up.

import xlwings as xw

# Connect to the active workbook or open a specific one
wb = xw.Book.active # Or use xw.Book('path_to_file.xlsx')
ws = wb.sheets['Sheet1'] # Replace with your sheet name

# Access the QueryTables collection
query_tables = ws.api.QueryTables

# Check if there are any query tables
if query_tables.Count > 0:
    print(f"Found {query_tables.Count} query table(s).")

# Iterate through each query table and refresh it
for i in range(1, query_tables.Count + 1):
    qt = query_tables(i)
    print(f"Refreshing query table: {qt.Name}")
    qt.Refresh()

# Example: Disable background query for the first table
if i == 1:
    qt.BackgroundQuery = False
    print(f"Set BackgroundQuery to False for {qt.Name}.")
else:
    print("No query tables found on this worksheet.")

# Save the workbook if needed
wb.save()