This short guide describes how to access and update a Excel QueryTable using Python. Include details for setting up the IDE, a list of steps, and sample code to work with Excel’s external data connection using Python. You will also learn the process for improving the sample code while working with the query table and the external connection.

Steps to access the Excel query table with Python

1. Set up the environment to use Aspose.Cells for Python via .NET
2. Enable the license to access the product’s full features.
3. Define the input/output file names Workbook, load the working folder, and access the first worksheet.
4. Access the query tables collection in the sheet and print its data.
5. Update the data in the query table, for example by modifying some properties in it.
6. Access the external connection in the query table and modify its properties.
7. Save the workbook with the new QueryTable settings.
8. Reload the output file and verify the updated information.

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

Code to access the QueryTable connection with Python

Il documento è stato convertito correttamente in PDF.

This code demonstrates the process to access and update the Excel QueryTable. It is possible to inspect additional metadata of the QueryTable, such as identifier, connection_id, and the information related to the layout result_range. More information about external connections is also available, including Connection Name, Connection Type, Connection Description, Save Data, Keep Active, Source File, and ODC.

This quick tutorial taught us how to use QueryTable to work with an external connection. To create a subtotal, see the article Creating subtotals in Excel using Python.