Excel VBA：更新Pivot Sourcedata [英] Excel VBA: Update Pivot Sourcedata

问题描述

我试图记录代码来更新一个pivot sourcedata，它给了我：

  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;）_ 
）
  

但这甚至不允许我指定WHICH透视表我想更新.. 。或甚至做我真正想做的是更新pivotcache，以便更新使用同一来源的所有数据透视表。

那么什么是更新的好方法the sourcedata？

感谢

EDIT： b

但我甚至得到应用程序定义或对象定义错误错误与一些简单的：

  str = Sheets（Totals）。PivotTables（PivotTable2）。PivotCache.CommandText 
 Sheets（Totals）。PivotTables（PivotTable2）。PivotCache.CommandText = str 
  

我已经仔细检查了我的数据透视表是否仍在点击实时数据，刷新仍然可以工作...但我不能将命令字符串设置为当前它是什么？非常奇怪。

感谢

解决方案

PivotCaches可通过工作簿。您可以使用以下子目录列出您当前的所有缓存：

 选项显式
 
私有子列表缓存）
 Dim selectedCache As PivotCache 
 
对于每个selectedCache在ThisWorkbook.PivotCaches 
 Debug.Print selectedCache.Index 
 Debug.Print selectedCache.Connection 
下一个selectedCache 
 
 End Sub 
  

您可以访问要编辑的连接：

  ThisWorkbook.PivotCaches（yourIndex）.Connection 
  

注意：更改连接后，您应该调用：

  ThisWorkbook.PivotCaches ）.Refresh 
  

编辑：不要更改SourceData， CommandText。这应该有同样的效果。以下代码适用于我：

  ThisWorkbook.PivotCaches（1）.CommandText =SELECT movies.title，movies.rating， movies.comments from`C：\Folder\moviesDB `.movi​​es movies
 ThisWorkbook.PivotCaches（1）.Refresh 
  

此代码还更新了我的SourceData。

Edit2：更改CommandText throgh PivotTable：

 表格（mySheet）。PivotTables（PivotTable1）。PivotCache.CommandText =选择movies.title作为meh，movies.rating，电影.comments FROM`C：\Folder\moviesDB`.movi​​es movies
表格（mySheet）。数据透视表（PivotTable1）。PivotCache.Refresh 
  

注意：moviesDB是一个.mdb文件，电影是表/查询

注意：也可以帮助您更改工作的CommandText Debug.Print 。这应该给你一个模板为你的新CommandText。

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

解决方案

The PivotCaches are accessible through Workbooks. You can list all your current caches with the following sub:

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:

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.

Edit2: Changing CommandText throgh PivotTable:

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

Note2: It might also help you to Debug.Print the working CommandText before changing it. This should give you a template for your new CommandText.

这篇关于Excel VBA：更新Pivot Sourcedata的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！