excel - 使用 Excel VBA 更改连接字符串时创建的新数据连接

Question

我有一个包含由宏更新的数据透视表的工作簿。但是，在刷新数据之前，连接字符串会发生变化:

With ThisWorkbook.Connections("Data").ODBCConnection
    .Connection = [Redacted]
    .CommandText = "EXEC ExtractCases " & Client
    .BackgroundQuery = False
    .Refresh
End With

这似乎导致数据透视表创建一个新连接(称为 Connection 或 Data1，我似乎无法弄清楚它在它们之间进行选择的作用)并指向它。所以我必须添加这样的行:

Sheets("Pivot").PivotTables("Pivot").ChangeConnection ThisWorkbook.Connections("Data")
Sheets("Pivot").PivotTables("Pivot").PivotCache.Refresh

这似乎有效(除非它不起作用)，但会留下很多死连接在工作簿周围敲打造成困惑。

我试过手动删除 Connection连接，但随后它突然将自己命名为 Data1本身没有明显的原因，并且系统因为不存在 Connection 而感到不安无法删除。

有什么明显的我做错了吗？有没有什么神奇的方法可以解决这个问题，所以它不会首先产生第二个导致这些头痛的问题？

注意:我在 Excel 2010 中运行此代码，但工作簿必须在 2003 年之前可以打开；但是，我在分发之前删除了 VB 模块，所以 2010 宏的东西很好，只是工作簿中的东西可能会被这个绊倒......

Answer 1

我在 Excel 2010 中遇到过同样的问题(可能与早期版本相同，我不知道)。

我尝试了与您相同的方法，即在我编辑连接字符串的 commandText 之后更改 VBA 代码中数据透视表的连接。正如你一样，我注意到有时成功，有时失败。

我一直无法找出问题出现的原因以及在哪些情况下上述方法会导致成功或失败。

但是，我找到了一个可行的解决方案 :
在您的 VBA 代码中，您需要按上述顺序执行以下步骤:

更改 commandText(如您所知，这会导致创建一个新的
数据透视表现在正在使用连接)。

删除旧的连接字符串。

将步骤 1 中的连接字符串重命名为步骤 2 中删除的连接字符串的名称。

刷新数据透视表。

注意:这仅在只有一个数据透视表使用连接时才有效。如果您通过复制第一个数据透视表创建了额外的数据透视表(即它们共享相同的数据透视缓存)，则上述过程将不起作用(我不知道为什么)。

但是，如果您只使用一个带有连接字符串的数据透视表，则该方法将起作用。