How to programmatically wrap a QueryTable in a ListObject?

Question

Historically external data queries in Excel were expressed with QueryTables.
ListObjects appeared at some point, but they were not yet compatible with QueryTables and could not contain them.

In Office 2007 they became not only compatible, but the default. Since then, creating an external query meant creating a ListObject, and the QueryTable was reduced to its internal component.

"Stand-alone" QueryTables, not wrapped in a ListObject, could not be created with the interface any more, but could be created with code and were perfectly supported in both new and old file formats.

Then Excel 2016 came along and introduced a bug that, in case of a "stand-alone" QueryTable, will permanently corrupt the workbook under certain circumstances. The only way to save QueryTables from the bug is to wrap them in a ListObject.

---

So I have 10k+ legacy Excel documents where the existing QueryTables need to be wrapped with ListObjects. Importantly, most query tables have formulas to the right of them that are filled down automatically.

Using the Excel interface, wrapping a query in a list is straightforward (activate a cell inside the query result, Insert - Table - Ok) and works like one would expect, leaving a fully functioning list that gets its data from a query.

Recording this action as a macro yields:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$5:$D$9"), , xlYes).Name = _
    "Table_Query_from_MS_Access"

However executing this very code without any modifications results in a Frankenstein query table: it looks like a list object on the outside, but it does not work, cannot be refreshed, cannot be edited, displays the old-style properties dialog, and the list object reports that it's an xlSrcRange as opposed to xlSrcQuery reported by the interface-created list.

Like an electron, it interferes with itself (the list-object part overlaps the query-table part and refuses to refresh for that reason, even though they should be one and the same - recall that there were formulas to the right of the query table, and they now must be a part of the list too):

Very apparently, the interface does much more when converting the table than was captured by the macro recorder.

I have tried calling ListObjects.Add with various parameters, providing a Range as a source, a WorkbookConnection as a source, a QueryTable's Connection as a source - it all does not work as long it overlaps the existing QueryTable.

I have looked into unlinking the existing QueryTable and recreating the ListObject from scratch where it was, but this causes various issues in formulas around the table.

What is the complete, correct code to programmatically wrap an existing QueryTable with a ListObject, matching exactly what the interface does?

It currently looks to me like I am going to have to do it by directly manipulating the XMLs inside the xslx format which I would hate.

Answer 1

I cannot wrap a QueryTable in a ListObject programmatically, but I can offer mitigations for Microsoft's Excel 2016/2019 QueryTable bug, since avoiding its effects seems to be your real goal.

[2024-06-30: The actual bug turns out to not be in the QueryTable Object, but rather the FillDown feature. If you do not need formulas and styles to automatically get copied down a column, you can disable FillDown to prevent the bug.]

OPTION 1: Closed-source 3rd-party Excel add-in by Event 1 Software - This will usually solve the problem, in my experience, but I've had a few files that were still affected. You want version 2.11 or newer. You can check the version of the loaded add-in with formula: =XLQT3Version()

OPTION 2: Change report design to reduce risks of the Microsoft bug (Source: Extensive work on report designs, including on systems that cannot be expected to have any Event 1 products installed):

2.1 Ensure that your QueryTable's header is not part of the QueryTable. You can still have a manual header above the table.

2.2 Insert a blank row above the first row of the QueryTable (between the QueryTable and the manual header).

2.3 In the new blank row, paste all the formatting and formulas needed by your QueryTable.

2.4 Set the new row's heigt to '3'. This will prevent the any part of the headers being copied into the body of the QueryTable. We will use the bug to cause the formatting and formulas to be copied from the semi-hidden row into the QueryTable, instead of destroying the those parts of the QueryTable. IMPORTANT: You MUST NOT hide the row or set the row height too low. Doing so will remove its protective effect.

2.5 Disable all data filters before refresh. You can reapply them after the refresh completes. Hook the QueryTable's before/after refresh events, if you need to automate this, or use a macro which removes them, starts the refresh, and then restores them.

2.6 Grouped rows and subtotals should be removed before refresh, and restored after refresh. Grouped columns are OK.

OPTION 3: Manual finagle (Source: Event 1 Software's tech support):

3.1 Open an affected document that has not been saved after the bug damaged it. That is, a document which is undamaged but which will be damaged by the MS bug.

3.2 DO NOT allow the document to connect to a data source or refresh. Cancel any login requests.

3.3 Wait at least 4 seconds.

3.4 Refresh the QueryTable (sign in if needed).

3.5 Refresh the QueryTable again (or sometimes a third time).

Answer 2

I encountered the same problem when trying to convert my querytable CSV's into a table. I did not find a direct way to covert the querytable into a listobject, but since my information was static I used a simple work around.

1. Create a temporary sheet to import the data with the querytable method
2. Copy the data from the temp worksheet to the planned working sheet
3. Covert copied data into a table and then insert formulas

If your XML data is not dynamic and do not need to keep a link to the external data then this may work for you. Otherwise this would not be a valid work around.