当查询表列发生变化时如何保留单元格引用

Question

在 Excel 中，我有一个链接到 SQL 表的查询表。我的电子表格中的公式包含对表格内单元格的引用，使用绝对引用，例如Z9。

我使用 vba 更新表的 SQL 查询。当我更改从 SQL 中提取的列时，问题就开始了。单元格引用中断，即使我感兴趣的数据在同一个单元格中。

如果使用引用方法，我显然会遇到同样的问题，=Table1[Column1]因为列名会发生变化。有没有办法更改查询表中的列而不破坏对该列的工作表公式引用？

Answer 1

当我的 vba 代码更改 SQL 查询时，我无法找到防止#REF中断的方法。但我能够解决错误！我没有根据用户输入更改查询，而是一次性提取整个数据集，然后在此通用集上使用索引匹配查找来获取所需的列。

典型的 Excel 解决方案：功能齐全，但不雅致。如果您有更好的答案，我会选择您的答案！

Answer 2

使用 QueryTables.Add 方法从网站下载数据时，我遇到了类似的问题。每当我重新执行查询时，对包含查询数据的工作表的引用都会不断变化。我改变了方法，将数据从查询工作表复制并粘贴到另一个工作表中。起初，这也不起作用，因为源值会随着后续查询而改变列。为了解决这个问题，我做了以下事情：

1. 发出查询之前清除查询工作表。
2. 减少查询以仅检索必要数据，减少了大约 20 倍。

3. 使用以下代码进行复制/粘贴：

   Sheets("Data").Cells(FromRow, FromCol).Copy
   Destination:=Sheets("Port").Cells(ToRow, ToCol)
   

这似乎有效。填充“数据”表的后续查询已正确粘贴到“数据”表中。我怀疑查询很大时存在同步问题，在查询完成之前执行了另一张表中的引用。

Answer 3

我已经弄清楚了，你插入的工作表QueryTable 一定不有任何预定义的列标题或其他数据。它必须完全是空的。否则 Excel 会将列向右移动，并且其他工作表中的所有现有公式将变为#REF。