切片器连接未显示数据透视表更改后数据源 [英] Slicer connections does not show pivottable post change datasource

问题描述

我在两个不同的工作表(Sheet1和Sheet2)中有两个数据透视表，它们从同一个工作表(Sheet3)中获取数据.在Sheet1的数据透视表上创建切片器.如果我们要报告连接，则可以在列表中看到两个数据透视表.

I have two pivottables in two different sheets (Sheet1 and Sheet2) sourcing data from a common sheet (Sheet3). A slicer is created on the pivottable on Sheet1. If we go to report connections, we can see both pivottables in the list.

现在，我正在一张一张地动态更改两个数据透视表的数据透视源.唯一的变化是范围被扩展为包括由不同过程复制的新行.执行代码后，报表连接不再显示两个数据透视表.它只显示一个.

Now I am dynamically changing the pivot data source for both the pivottables one by one. Only change is the range is extended to include the new rows copied by a different process. Once the code is executed, the report connections does not show both the pivottables anymore. It just shows one.

使用以下代码更改数据透视表数据源.

using the below code to change pivot data source.

                    Dim objwrksheet As Worksheet = mWorkBook.Worksheets(mPivotWorksheetname)
                    Dim objwrksheet2 As Worksheet = mWorkBook.Worksheets(mDataWorksheetname)
                    If Not IsNothing(objwrksheet) Then
                        Dim objpivottable As PivotTable = objwrksheet.PivotTables(mPivotTable)
                        If objpivottable IsNot Nothing Then
                            Dim sourceDataRange As Range = objwrksheet2.Range(mSourceRange)
                            Dim cache As PivotCache = mWorkBook.PivotCaches.Create(SourceType:=XlPivotTableSourceType.xlDatabase, SourceData:=sourceDataRange)
                            objpivottable.ChangePivotCache(cache)
                            objpivottable.RefreshTable()
                            mRetval = "Successful"
                        Else
                            mRetval = "Pivot open failed"
                        End If
                    Else
                        mRetval = "Worksheet open failed"
                    End If

预期结果应该是在两个数据透视表的更改数据源之后，切片器报告连接应继续在列表中显示两个数据透视表名称.

Expected result should be that after the change data source for both the pivot tables, the slicer report connections should continue to show both the pivottable names in the list.

推荐答案

这是一种通用的VBA方法:

This is a general VBA approach:

通常，可以通过向工作簿添加新的PivotCache来更改PivotTable.SourceData.但是，如果此数据透视表包含在切片器中，则必须先通过SlicerCache.PivotTables.RemovePivotTable()取消选中其报表连接.

You can change the PivotTable.SourceData in general by adding a new PivotCache to the workbook. But if this pivottable is included in a slicer, you have to uncheck its report connection first by SlicerCache.PivotTables.RemovePivotTable().

如果更改了多个数据透视表的源数据，则所有包含的数据透视表都基于同一PivotCache时，您只能在切片器中重新分配其报表连接.

If you change several pivottables' sourcedata, you can only reassign their report connections within the slicer again, if all included pivottables are based on the same PivotCache.

因此，在更改第一个数据透视表的源数据之后，您必须为所有其他数据透视表重用"其新的数据透视表缓存.这种重用"可以通过设置PivotTable.CacheIndex来完成，只要其他枢轴表使用与第一个枢轴表相同的枢轴域(或它们的子集)即可.

Therefore after changing the first pivottable's sourcedata, you have to "reuse" its new pivotcache for all other pivottables. This "reuse" can be done by setting the PivotTable.CacheIndex, which works as long as the further pivottables use the same pivotfields (or subset of them) like the first pivottable.

备注:要使用以下代码，首先需要启用切片器的所有报告连接(因为SlicerCache.PivotTables仅返回已检查的报告连接).

Remark: To use following code, all report connections of the slicer need to be enabled first (as SlicerCache.PivotTables returns the checked report connections only).

Private Sub ChangeAllPivotSources()
    Dim objSlicerCache As SlicerCache
    Dim objPivotTable As PivotTable
    Dim objPivotTables() As PivotTable
    Dim i As Long

    ' get the slicercache, e. g. via its first pivottable:
    Set objPivotTable = ActiveWorkbook.Sheets(1).PivotTables(1)
    Set objSlicerCache = objPivotTable.Slicers(1).SlicerCache

    ' dimension array with all pivottable objects of the slicercache
    ReDim objPivotTables(1 To objSlicerCache.PivotTables.Count)

    ' remove all pivottables from slicer's report connections
    For i = objSlicerCache.PivotTables.Count To 1 Step -1
        Set objPivotTables(i) = objSlicerCache.PivotTables(i)
        objSlicerCache.PivotTables.RemovePivotTable objPivotTables(i)
    Next i

    ' create new pivotcache based on a new range for the first pivottable,
    ' use this pivotcache for all other pivottables also
    For i = 1 To UBound(objPivotTables)
        If i = 1 Then
            objPivotTables(i).ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
                SourceType:=xlDatabase, _
                SourceData:=ActiveWorkbook.Sheets(3).Range("A1").CurrentRegion)
        Else
            objPivotTables(i).CacheIndex = objPivotTables(1).PivotCache.Index
        End If
    Next i

    ' reassign the report connections again
    For i = 1 To UBound(objPivotTables)
        objSlicerCache.PivotTables.AddPivotTable objPivotTables(i)
    Next i

End Sub

这篇关于切片器连接未显示数据透视表更改后数据源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！