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.
