{"id":2343,"date":"2026-10-02T07:39:24","date_gmt":"2026-10-01T23:39:24","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2343"},"modified":"2026-03-29T09:37:31","modified_gmt":"2026-03-29T09:37:31","slug":"how-to-use-worksheetquerytables-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetquerytables-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.QueryTables in the xlwings API way"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The <code>QueryTables<\/code> member of the Worksheet object in Excel&#8217;s object model represents a collection of <code>QueryTable<\/code> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Functionality:<\/strong><br>The <code>QueryTables<\/code> collection allows you to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Access existing query tables on a specific worksheet.<\/li>\n\n\n\n<li>Add new query tables (though note that creating new query tables via xlwings may require using the underlying Excel object model via the <code>.api<\/code> property, as xlwings does not have a dedicated high-level method for this).<\/li>\n\n\n\n<li>Refresh data in query tables to update the imported information.<\/li>\n\n\n\n<li>Modify properties of query tables, such as connection strings or refresh settings.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Syntax:<\/strong><br>In xlwings, you access the <code>QueryTables<\/code> collection through a Worksheet object. The basic syntax is:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>worksheet.api.QueryTables<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">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:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>first_query_table = worksheet.api.QueryTables(1)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Common methods and properties include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Refresh()<\/code>: Refreshes the query table to update data.<\/li>\n\n\n\n<li><code>BackgroundQuery<\/code>: A property that can be set to <code>True<\/code> or <code>False<\/code> to control whether the query runs in the background.<\/li>\n\n\n\n<li><code>Connection<\/code>: A property that holds the connection string for the data source.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example:<\/strong><br>Below is a practical example demonstrating how to use the <code>QueryTables<\/code> 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.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\n\n# Connect to the active workbook or open a specific one\nwb = xw.Book.active # Or use xw.Book('path_to_file.xlsx')\nws = wb.sheets&#91;'Sheet1'] # Replace with your sheet name\n\n# Access the QueryTables collection\nquery_tables = ws.api.QueryTables\n\n# Check if there are any query tables\nif query_tables.Count > 0:\n    print(f\"Found {query_tables.Count} query table(s).\")\n\n# Iterate through each query table and refresh it\nfor i in range(1, query_tables.Count + 1):\n    qt = query_tables(i)\n    print(f\"Refreshing query table: {qt.Name}\")\n    qt.Refresh()\n\n# Example: Disable background query for the first table\nif i == 1:\n    qt.BackgroundQuery = False\n    print(f\"Set BackgroundQuery to False for {qt.Name}.\")\nelse:\n    print(\"No query tables found on this worksheet.\")\n\n# Save the workbook if needed\nwb.save()<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The `QueryTables` member of the Worksheet object in Excel&apos;s object model represents a collection of &#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[],"class_list":["post-2343","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2343","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/comments?post=2343"}],"version-history":[{"count":1,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2343\/revisions"}],"predecessor-version":[{"id":3572,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2343\/revisions\/3572"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2343"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}