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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:05:14  来源:igfitidea点击:

Excel VBA: Update Pivot Sourcedata

databasevbaexcel-vbapivotexcel

提问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 提供一个模板。