title: How to programmatically wrap a QueryTable in a ListObject?
body: <p>Historically external data queries in Excel were expressed with <a href="https://msdn.microsoft.com/en-us/library/office/ff198271.aspx" rel="nofollow noreferrer"><code>QueryTable</code>s</a>.<br />
  <a href="https://msdn.microsoft.com/en-us/library/office/ff197604.aspx" rel="nofollow noreferrer"><code>ListObjects</code></a> appeared at some point, but they were not yet compatible with <code>QueryTable</code>s and could not contain them.</p>
  <p>In Office 2007 they became not only compatible, but the default. Since then, creating an external query meant creating a <code>ListObject</code>, and the <code>QueryTable</code> was reduced to its internal component.</p>
  <p>&quot;Stand-alone&quot; 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.</p>
  <p>Then Excel 2016 came along and introduced a bug that, in case of a &quot;stand-alone&quot; 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.</p>
  <hr />
  <p>So I have 10k+ legacy Excel documents where the existing <code>QueryTable</code>s need to be wrapped with <code>ListObject</code>s. Importantly, most query tables have formulas to the right of them that are <a href="https://msdn.microsoft.com/en-us/library/office/ff198337.aspx" rel="nofollow noreferrer">filled down automatically</a>.</p>
  <p>Using the Excel interface, wrapping a query in a list is straightforward (activate a cell inside the query result, <kbd>Insert</kbd> - <kbd>Table</kbd> - <kbd>Ok</kbd>) and works like one would expect, leaving a fully functioning list that gets its data from a query.</p>
  <p>Recording this action as a macro yields:</p>
  <pre><code>ActiveSheet.ListObjects.Add(xlSrcRange, Range(&quot;$B$5:$D$9&quot;), , xlYes).Name = _
      &quot;Table_Query_from_MS_Access&quot;
  </code></pre>
  <p>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 <code>xlSrcRange</code> as opposed to <code>xlSrcQuery</code> reported by the interface-created list.</p>
  <p>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):</p>
  <p><a href="https://i.stack.imgur.com/NdnqK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NdnqK.png" alt="enter image description here" /></a></p>
  <p>Very apparently, the interface does much more when converting the table than was captured by the macro recorder.</p>
  <p>I have tried calling <code>ListObjects.Add</code> with various parameters, providing a <code>Range</code> as a source, a <code>WorkbookConnection</code> as a source, a <a href="https://msdn.microsoft.com/en-us/library/office/ff837414.aspx" rel="nofollow noreferrer"><code>QueryTable</code>'s <code>Connection</code></a> as a source - it all does not work as long it overlaps the existing QueryTable.</p>
  <p>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.</p>
  <p><strong>What is the complete, correct code to programmatically wrap an existing <code>QueryTable</code> with a <code>ListObject</code>, matching exactly what the interface does?</strong></p>
  <p>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.</p>