Excel VBA：更新数据透视源数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1867459/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Update Pivot Sourcedata
提问by Dan
I tried to record the code to update a pivot sourcedata which gave me this:
我试图记录代码来更新一个数据透视源数据，它给了我这个：
ActiveSheet.PivotTableWizard SourceType:=xlExternal, _
SourceData:=QueryArry1, _
Connection:=Array( _
Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _
Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _
)
But this doesn't even allow me to specify WHICH pivot table I want to update... or even do what I really want to do which is update the pivotcache so that all pivot tables using that same source are updated.
但这甚至不允许我指定我想要更新的数据透视表......或者甚至做我真正想做的事情，即更新数据透视缓存，以便更新所有使用相同源的数据透视表。
So what is a good way to update the sourcedata?
那么更新源数据的好方法是什么？
Thanks
谢谢
EDIT:
编辑：
But I even get the "application-defined or object-defined error" error with something as simple as:
但我什至得到了“应用程序定义或对象定义错误”的错误，就像这样简单：
str = Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText
Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText = str
And I did double check that my pivot table is still hitting the live data and refreshing it still works... yet I can't set the command string to what it is currently? So bizarre.
我确实仔细检查了我的数据透视表是否仍然命中实时数据并刷新它仍然有效......但我无法将命令字符串设置为当前的值？太奇怪了。
Thanks
谢谢
回答by marg
The PivotCaches are accessible through Workbooks. You can list all your current caches with the following sub:
PivotCache 可通过工作簿访问。您可以使用以下子项列出所有当前缓存：
Option Explicit
Private Sub listCaches()
Dim selectedCache As PivotCache
For Each selectedCache In ThisWorkbook.PivotCaches
Debug.Print selectedCache.Index
Debug.Print selectedCache.Connection
Next selectedCache
End Sub
You can access the connection you want to edit with:
您可以访问要编辑的连接：
ThisWorkbook.PivotCaches(yourIndex).Connection
Note: After changing the Connection you should call:
注意：更改连接后，您应该调用：
ThisWorkbook.PivotCaches(yourIndex).Refresh
Edit:Instead of changing the SourceData you can change the CommandText. That should have the same effect. The following code worked for me:
编辑：您可以更改 CommandText，而不是更改 SourceData。那应该有同样的效果。以下代码对我有用：
ThisWorkbook.PivotCaches(1).CommandText = "SELECT movies.title, movies.rating, movies.comments FROM `C:\Folder\moviesDB`.movies movies"
ThisWorkbook.PivotCaches(1).Refresh
This code also updated my SourceData.
此代码还更新了我的 SourceData。
Edit2:Changing CommandText throgh PivotTable:
Edit2：通过数据透视表更改命令文本：
Sheets("mySheet").PivotTables("PivotTable1").PivotCache.CommandText = "SELECT movies.title as meh, movies.rating, movies.comments FROM `C:\Folder\moviesDB`.movies movies"
Sheets("mySheet").PivotTables("PivotTable1").PivotCache.Refresh
Note: moviesDB is a .mdb file and movies is the table/query
注意：moviesDB 是一个 .mdb 文件，movies 是表/查询
Note2: It might also help you to Debug.Printthe working CommandText before changing it. This should give you a template for your new CommandText.
注意2：Debug.Print在更改它之前，它也可能会帮助您找到工作的 CommandText。这应该为您的新 CommandText 提供一个模板。
