excel - 如何以编程方式将 QueryTable 包装在 ListObject 中？

Question

历史上，Excel 中的外部数据查询用 QueryTables 表示。
ListObjects 出现在某个时候，但它们还不兼容 QueryTable 并且不能包含它们。

在 Office 2007 中，它们不仅兼容，而且成为默认设置。从那时起，创建一个外部查询就意味着创建一个 ListObject，而 QueryTable 被缩减为它的内部组件。

“独立”QueryTables，没有包装在 ListObject 中，不能再用接口(interface)创建，但可以用代码创建，并且在新旧文件格式中都得到完美支持。

然后 Excel 2016 出现并引入了一个错误，在“独立”QueryTable 的情况下，在某些情况下会永久损坏工作簿。从错误中拯救 QueryTables 的唯一方法是将它们包装在 ListObject 中。

---

所以我有 10k+ 遗留 Excel 文档，其中现有的 QueryTable 需要用 ListObject 包装。重要的是，大多数查询表的右侧都有公式，即 filled down automatically。

使用 Excel 界面，将查询包装在列表中非常简单(在查询结果中激活一个单元格，Insert - Table - Ok) 并像人们期望的那样工作，留下一个功能齐全的列表，从查询中获取其数据。

将此操作记录为宏会产生:

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

然而，执行这段代码而不做任何修改会导致 Frankenstein 查询表:它在外面看起来像一个列表对象，但它不起作用，无法刷新，无法编辑，显示旧式属性对话框，并且列表对象报告它是一个 xlSrcRange 而不是由接口(interface)创建的列表报告的 xlSrcQuery。

就像电子一样，它会干扰自身(列表对象部分与查询表部分重叠并因此拒绝刷新，即使它们应该是同一个 - 记得右边有公式查询表，它们现在也必须是列表的一部分):

很明显，在转换表格时，该界面所做的工作比宏记录器捕获的要多得多。

我试过使用各种参数调用 ListObjects.Add，提供一个 Range 作为源，一个 WorkbookConnection 作为源，一个 QueryTable's Connection作为源 - 只要它与现有的 QueryTable 重叠，它就不起作用。

我研究过取消链接现有的 QueryTable 并从头开始重新创建 ListObject，但这会导致表格周围的公式出现各种问题。

以编程方式用 ListObject 包装现有 QueryTable 的完整、正确的代码是什么，与接口(interface)的功能完全匹配？

目前在我看来，我将不得不通过直接操作我讨厌的 xslx 格式内的 XML 来做到这一点。

Answer 1

我无法以编程方式将 QueryTable 包装在 ListObject 中，但我可以为 Microsoft 的 Excel 2016/2019 QueryTable 错误提供缓解措施，因为避免它的影响似乎是您的真正目标。

1. Closed-source 3rd-party Excel add-in by Event 1 Software - 根据我的经验，这通常会解决问题，但我收到的报告仍然受到影响。您需要 2.11 或更高版本。您可以使用公式检查加载的插件版本:=XLQT3Version()

2. 更改报告设计以降低 Microsoft 错误的风险(来源:报告设计方面的广泛工作，包括在未安装任何 Event 1 产品的系统上):

2.1 确保您的 QueryTable 的 header 不是 QueryTable 的一部分。您仍然可以在表格上方有一个手动标题。

2.2 在查询表的第一行上方插入一个空行(在查询表和手册页眉之间。

2.3 在新的空白行中，粘贴您的 QueryTable 所需的所有格式和公式。

2.4 将新行的高度设置为“3”。这将防止标题被完全或部分复制到 QueryTable 的主体中。相反，该错误将导致将所需的格式和公式复制到 QueryTable 中，而不是破坏 oueryTable 的那些部分。重要提示:您不得隐藏该行或将行高设置得太低。这样做会消除其保护作用。

2.5 刷新前禁用所有数据过滤器。您可以在刷新完成后重新应用它们。如果您需要自动执行此操作，请挂接 QueryTable 的刷新前/刷新后事件，或者使用删除它们的宏，开始刷新，然后恢复它们。

2.6 分组的行和小计在刷新之前也应该被删除，并恢复。刷新后。分组列是可以的。

1. 手动 finagle(来源:Event 1 Software 的技术支持):

3.1 打开受影响的文档，该文档在错误损坏后尚未保存。即，未损坏但会被 MS 错误损坏的文档。

3.2 不允许文档连接到数据源或刷新。取消任何登录请求。

3.3 等待至少 4 秒。

3.4 刷新 QueryTable(如果需要请登录)。

3.5 再次刷新QueryTable(有时是第三次。