时间戳

首页 > 解决方案 > 切片器连接不显示可透视的更改后数据源

c# - 切片器连接不显示可透视的更改后数据源

问题描述

我在两个不同的工作表（Sheet1 和 Sheet2）中有两个数据透视表，从一个公共工作表（Sheet3）采购数据。在 Sheet1 的数据透视表上创建切片器。如果我们去报告连接，我们可以在列表中看到两个数据透视表。

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

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


                    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

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

标签： c#excelvbavb.netinterop

解决方案

这是一个通用的 VBA 方法：

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

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

因此，在更改第一个数据透视表的源数据后，您必须为所有其他数据透视表“重用”其新的数据透视缓存。这种“重用”可以通过设置 来完成PivotTable.CacheIndex，只要进一步的数据透视表使用与第一个数据透视表相同的数据透视字段（或它们的子集），它就可以工作。

备注：要使用以下代码，首先需要启用切片器的所有报表连接（因为SlicerCache.PivotTables仅返回检查的报表连接）。

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

推荐阅读