使用Excel VBA更改连接字符串时创建的新数据连接 [英] New data connection created when changing connection string using Excel VBA

问题描述

我有一个工作簿，其中包含一个由宏更新的数据透视表.但是，在刷新数据之前，将更改连接字符串:

I have a workbook that contains a pivot table which is updated by a macro. Before the data is refreshed, though, the connection string gets changed:

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

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

This seems to cause the pivot table to create a new connection (called either Connection or Data1, and I can't seem to figure out what it does to choose between them) and point itself to that. So I then have to add lines like these:

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

哪一个似乎可行(除非不可行)，但留下大量无效连接，使工作簿陷入混乱.

Which seems to work (except when it doesn't), but leaves a lot of dead connections knocking around the workbook causing confusion.

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

I've tried manually deleting the Connection connection, but then it suddenly names itself Data1 itself for no apparent reason and the system gets upset because a non-existent Connection can't be deleted.

有什么明显的地方我做错了吗?是否有某种神奇的方法可以解决此问题，从而不会在第一时间造成第二种头痛呢?

Is there something obvious I'm doing wrong? Is there some magic way to fix this so it doesn't create the second one in the first place to cause these kinds of headaches?

注意:我正在Excel 2010中运行此代码，但是该工作簿必须在2003年前可以打开；但是，我在分发前先删除了VB模块，所以2010宏的内容还不错，这可能是因为工作簿中的某些内容会因此而被触发...

Note: I am running this code in Excel 2010, but the workbook has to be openable by 2003; however, I remove the VB module before distribution, so 2010 macro stuff is fine, it's just things in the workbook proper that might get tripped up by this...

推荐答案

我在Excel 2010中遇到了相同的问题(我不知道早期版本的问题).

I have experienced the same problem in Excel 2010 (might be the same for earlier versions, I dunno).

我尝试了与您相同的方法，即在我编辑了连接字符串的commandText之后，更改了VBA代码中的数据透视表的连接.正如您所说，我有时会指出成功，而有时会指出失败.

I have tried the same approach as you i.e. changing the connection of the Pivot Table in the VBA-code AFTER I have edited the commandText of the connection string. As you, I noted sometimes success and other times failure.

我一直无法弄清为什么会出现问题，在哪种情况下上述方法会导致成功或失败.

I haven't been able to find out why the problem arises and in which cases the above mentioned approach results in success or failure.

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

I have, however, found a working solution: In your VBA code, you need to perform the following steps in the said order:

1. 更改commandText(如您所知，这会导致创建一个新的 数据透视表正在使用此连接).
2. 删除旧的连接字符串.
3. 将第1步中的连接字符串重命名为第2步中删除的连接字符串的名称.
4. 刷新数据透视表.

1. Change the commandText (which as you know results in the creation of a new connection now in use by the Pivot Table).
2. Delete the old connection string.
3. Rename the connection string from step 1 to the name of the connection string deleted in step 2.
4. Refresh the Pivot Table.

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

NB: This only works if there is only one pivot table using the connection. If you have created extra Pivot Tables by copying the first one (i.e. they share the same Pivot Cache), the above mentioned procedure won't work (and I don't know why).

但是，如果仅将一个数据透视表与连接字符串一起使用，则该方法将起作用.

However, if you use only one Pivot Table with the connection string the approach will work.

这篇关于使用Excel VBA更改连接字符串时创建的新数据连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！