excel - 同步切片机

Question

我有两个源表，以及基于它们的几十个数据透视表。

两个表有一个公共(public)字段，具有公共(public)的可能值集。

我有两个切片器(每个源表一个)。每个切片器控制多个关联的数据透视表。

我希望能够同步它们。

也就是说，如果用户在 Slicer_1 中选择值 A，Slicer_2 也会自动更新以选择值 A。

所以到目前为止我所拥有的都是非常基本的


ActiveWorkbook.SlicerCaches("Slicer_1").SlicerItems("A").Selected = ActiveWorkbook.SlicerCaches("Slicer_2").SlicerItems("A").Selected
ActiveWorkbook.SlicerCaches("Slicer_1").SlicerItems("B").Selected = ActiveWorkbook.SlicerCaches("Slicer_2").SlicerItems("B").Selected
ActiveWorkbook.SlicerCaches("Slicer_1").SlicerItems("C").Selected = ActiveWorkbook.SlicerCaches("Slicer_2").SlicerItems("C").Selected


现在，当 slicer_1 更改时，我如何自动触发它？我已将宏分配给 slicer_2，但直到单击切片器框后才会发生更新。

以及如何延迟执行，直到应用所有更改。此时它更新 A 字段(选择是/否)刷新我的表并移至 B 等。

我希望它等待刷新，直到所有切片器字段都已更新

谢谢

Answer 1

同步切片器可以通过通用方式完成。
对于“通用”，我的意思是不应该依赖于(字面意思)切片器缓存名称，并且同步可以从任何切片器缓存开始。

实现这一切的方法是保存所有切片器缓存对象的状态。在数据透视表(位于一个或多个切片器高速缓存之下)发生更改后，可以将新状态与旧状态进行比较，并识别更新的高速缓存。从那里可以完成同步。

我的解决方案包含 4 个步骤:
1)创建clsWrapperCache ，Excel SlicerCache 对象的包装类
2)创建clsWrapperCaches ，clsWrapperCache对象的集合类
3)创建clsCacheManager ，用于处理 SlicerCache 对象状态的管理器类
4)ThisWorkbook ，设置对经理的调用

1) clsWrapperCache，Excel SlicerCache 对象的包装类

' wrapper class around Excel SlicerCache object
Option Explicit
Public Object As SlicerCache
Public OldState As String

Public Function CurrentState() As String
    ' state is set by:
    ' a) name of first visible slicer item
    ' b) number of visible slicer items
    Dim s As String

    If Object.VisibleSlicerItems.Count > 0 Then
        s = Object.VisibleSlicerItems.Item(1).Name
    Else
        s = ""
    End If
    s = s & vbCrLf ' separator that cannot be found in a SlicerItem name
    s = s & CStr(Object.VisibleSlicerItems.Count)

    CurrentState = s
End Function

clsWrapperCache保存一个 Excel SlicerCache 对象。
更重要的是:它可以管理 SlicerCache 的状态。获取状态可以非常快地完成，即通过连接:

• 第一个 VisibleSlicerItem 的名称和
• VisibleSlicerItems 的数量。

OldState最初设置在 Set_Caches例程(步骤 3)，可以在 de Synchronize_Caches 中重置如果同步过程中涉及切片器缓存，则例程(步骤 3)。

2) clsWrapperCaches，clsWrapperCache对象的集合类

' clsWrapperCaches, collection class of clsWrapperCache objects
Option Explicit

Private mcol As New Collection

Public Sub Add(oWC As clsWrapperCache)
    mcol.Add oWC, oWC.Object.Name
End Sub

Public Property Get Item(vIndex As Variant) As clsWrapperCache
    ' vIndex may be of type integer or string
    Set Item = mcol(vIndex)
End Property

Public Property Get Count() As Integer
    Count = mcol.Count
End Property

这是一个简单的集合类，仅包含 clsWrapperCache对象。它将用于容纳 AllCaches 中的物体。收藏。

3) clsCacheManager，处理SlicerCache对象状态的类

Option Explicit

Public AllCaches As New clsWrapperCaches

Public Sub Set_Caches()
    Dim sc As SlicerCache
    Dim oWC As clsWrapperCache
    Dim i As Integer

    If Me.AllCaches.Count <> ThisWorkbook.SlicerCaches.Count Then
        ' a) on Workbook_Open event
        ' b) maybe the user has added/deleted a Slice Cache shape by hand
        Set AllCaches = New clsWrapperCaches
        For Each sc In ThisWorkbook.SlicerCaches
            'create a wrapper SlicerCache object
            Set oWC = New clsWrapperCache
            Set oWC.Object = sc
            'save current state of SlicerCache into OldState
            oWC.OldState = oWC.CurrentState

            ' add wrapper object to collection
            AllCaches.Add oWC
        Next
    End If
End Sub

Sub Synchronize_Caches()
    ' copy current selections from slicer caches "FromCaches" into any other slicer cache with same SourceName
    On Error GoTo ErrEx
    Dim oWCfrom As clsWrapperCache
    Dim oWCto As clsWrapperCache
    Dim scFrom As SlicerCache
    Dim scTo As SlicerCache
    Dim si As SlicerItem

    Dim i As Integer
    Dim j As Integer

    Application.EnableEvents = False ' prevent executing Workbook_SheetPivotTableUpdate event procedure
    Application.ScreenUpdating = False

    For i = 1 To Me.AllCaches.Count
        Set oWCfrom = Me.AllCaches.Item(i)
        If oWCfrom.CurrentState <> oWCfrom.OldState Then
            Set scFrom = oWCfrom.Object
            For j = 1 To Me.AllCaches.Count
                Set oWCto = Me.AllCaches.Item(j)
                Set scTo = oWCto.Object

                ' Debug.Print oWCto.Name
                If scTo.Name <> scFrom.Name And scTo.SourceName = scFrom.SourceName Then
                    scTo.ClearAllFilters ' triggers a Workbook_SheetPivotTableUpdate event
                    On Error Resume Next
                        For Each si In scFrom.SlicerItems
                            scTo.SlicerItems(si.Name).Selected = si.Selected
                        Next
                    On Error GoTo 0

                    ' update old state of wrapper object oWCto
                    oWCto.OldState = oWCto.CurrentState
                End If
            Next
            ' update old state of wrapper object oWCfrom
            oWCfrom.OldState = oWCfrom.CurrentState
        End If
    Next

Ex:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
ErrEx:
    MsgBox Err.Description
    Resume Ex
End Sub

clsCacheManager 类使用方法 Set_Caches 管理缓存状态和Synchronize_Caches .
Set_Caches :如果 ThisWorkbook 中的缓存数量与 AllCaches 的数量不同，则(重新)构建 AllCaches 集合。特此OldState每个切片器缓存都被保存。

Synchronize_Caches :这里遍历所有缓存。如果切片器缓存已更新( oWCfrom.CurrentState <> oWCfrom.OldState )，则具有相同 SourceName(例如“年份”)的任何其他缓存也将被更新。更新是通过将切片器项的所有选择从源缓存复制到目标缓存来实现的。 OldState在同步过程结束时，所有涉及的缓存都会重置为当前状态。

4) ThisWorkbook，设置对缓存管理器的调用

Option Explicit
Private mCacheManager As New clsCacheManager

Private Sub Workbook_Open()
    SetCacheManager
    mCacheManager.Set_Caches
End Sub

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    SetCacheManager
    mCacheManager.Set_Caches
    mCacheManager.Synchronize_Caches
End Sub

Private Sub SetCacheManager()
    If mCacheManager Is Nothing Then
        Set mCacheManager = New clsCacheManager
    End If
End Sub

步骤 1 到步骤 3 的所有好处都可以在步骤 4 中获得:我们可以调用 CacheManager，如 SetCaches或Synchronize_Caches 。这段代码很容易阅读。

该解决方案的优点:

1. 适用于工作簿中的所有切片器缓存
2. 不依赖于 SlicerCache 名称
3. 非常快，因为切片器缓存对象的状态获取得非常快
4. 可扩展。类 clsCacheManager可以扩展以处理切片器缓存之间的依赖关系。