This short guide describes how to access and update an Excel QueryTable using Python. It has details to set the IDE, a list of steps, and a sample code to work with the Excel external data connection using Python. You will also learn the process to enhance the sample code while working with the query table and the external connection in it.

Steps to Access Excel Query Table using Python

1. Set the environment to use Aspose.Cells for Python via .NET
2. Enable the license to access the complete features of the product
3. Define the input/output Workbook file names, load the Workbook, and access the first worksheet
4. Access the collection of query tables in the sheet and print its data
5. Update the data in the query table, such as changing a few properties in it
6. Access the external connection in the query table and change its properties
7. Save the workbook with new QueryTable settings
8. Load the output file again and verify the updated information

These steps define the process to work with the Excel external data connection using Python through the QueryTable object. Load the license, open the source Excel file using the Workbook class object, access the first sheet, check the existence of the QueryTable in the sheet, and display it properties. Update the query table property, such as preserve formatting, flag to refresh data in the background for the external connection, and save the workbook.

Code to Access QueryTable Connection using Python

This code demonstrates the process to access and update the Excel QueryTable. You may inspect more QueryTable metadata, such as identifier, connection_id and layout-related info result_range. More external connection information is also available, including Connection Name, Connection Type, Connection Description, Save Data, Keep Alive, Source File, and ODC.

This quick tutorial has taught us how to use QueryTable for working with an external connection. For creating a subtotal, refer to the article Creating subtotals in Excel using Python.