vba - 无效的过程调用或参数 - 尝试动态更改数据透视缓存

Question

我正在尝试动态刷新 Excel 中的一些数据透视表。数据透视表(和连接的切片器 - 每个切片器连接到多个数据透视表)已经存在，基础源数据表不存在。流程如下:

• 创建新工作表来保存“原始”数据
• 填充工作表，将数据包装到 ListObject(表)中
• 根据新数据创建新的数据透视缓存
• 取消切片器与数据透视表的链接
• 将数据透视表的缓存更改为新缓存
• 刷新数据透视表
• 重新链接切片器
• 删除数据表

To clarify the structure: One data source table. Multiple pivot tables pointing to the source. Multiple slicers, each connected to all the pivot tables (eg Week Ending slicer chages Week Ending on all the pivots)

但是，我在步骤 4 中遇到了问题。以下代码有效:

'dataTable is a ListObject that was created on a sheet earlier in the function. Can confirm 100% that it exists and is populated.    
Dim pt As PivotTable
For Each pt in PivotSheet.PivotTables
    pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataTable)
Next pt

但是，这意味着每个数据透视表有一个数据透视缓存，这意味着我在尝试设置操作多个数据透视表的切片器时遇到问题 - 它假设每个数据透视表都有不同的数据源，因此只会让我链接切片器到单个枢轴。

我决定的方法是创建一个数据透视表缓存，然后将每个数据透视表链接到它。然而，这段代码不起作用，第一次到达时向我抛出错误5:

'dataTable is a ListObject that was created on a sheet earlier in the function. Can confirm 100% that it exists and is populated.
Dim pc As PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=dataTable)

Dim pt As PivotTable
For Each pt in PivotSheet.PivotTables
    pt.ChangePivotCache pc  'Invalid Procedure Call Or Argument 
Next pt

我在这里做错了什么？

Answer 1

结合这两种方法:

Dim bCreated              As Boolean
Dim lngMasterIndex        As Long
Dim pt                    As PivotTable

For Each pt In PivotSheet.PivotTables
    If Not bCreated Then

        pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
                            SourceType:=xlDatabase, _
                            SourceData:=DataTable)
        bCreated = True
        lngMasterIndex = pt.CacheIndex
    Else
        pt.CacheIndex = lngMasterIndex
    End If
Next pt