Excel 2016中创建SlicerCache关联透视表时Add方法调用失败求助

问题根源 #

你的代码报错的核心原因是：你尝试从不包含JobGroup字段的第二个透视表（OneYear_pvTbl_Sparkline）创建针对该字段的切片器缓存。Excel 2016及以后版本对切片器缓存的创建逻辑做了更严格的校验，要求用于创建切片器缓存的透视表必须包含指定字段；而Excel 2010/2013版本的校验规则更宽松，允许基于数据源字段创建，即使透视表未包含该字段。

看你的代码，第二个透视表只添加了ExtractDate行字段和一系列数值字段，完全没有引入JobGroup字段，所以在2016中调用.SlicerCaches.Add(pvt, "JobGroup")时会触发错误。

解决方案 #

修改切片器缓存的创建逻辑，从包含JobGroup字段的第一个透视表（OneYear_pvTbl_InOut）来创建切片器缓存，再将第二个透视表关联到这个缓存即可。

修改后的完整代码片段 #

Sub OneYearTrend(wb As Workbook, wk As Worksheet)
    With wb
        Dim pvc As PivotCache
        ' 创建数据透视缓存
        Set pvc = .PivotCaches.Create(xlDatabase, wk.Range("A1").CurrentRegion, xlPivotTableVersion14)
        Dim pvtInOut As PivotTable
        ' 创建第一个透视表（入职/离职趋势）
        Set pvtInOut = pvc.CreatePivotTable(TableDestination:=.Worksheets("Dashboard").Range("M6"), _
            TableName:="OneYear_pvTbl_InOut")
        With pvtInOut
            With .PivotFields("Type")
                .Orientation = xlColumnField
                .Position = 1
                .PivotItems("Total Workforce").Visible = False
                .PivotItems("NEW HIRES").Caption = "In"
                .PivotItems("SEPARATIONS").Caption = "Out"
            End With
            With .PivotFields("JobGroup")
                .Orientation = xlPageField
                .Position = 1
                .EnableMultiplePageItems = True
            End With
        End With

        Dim pvtSparkline As PivotTable
        ' 创建第二个透视表（迷你图数据）
        Set pvtSparkline = pvc.CreatePivotTable(TableDestination:=.Worksheets("Dashboard").Range("AA1"), _
            TableName:="OneYear_pvTbl_Sparkline")
        With pvtSparkline
            With .PivotFields("ExtractDate")
                .Orientation = xlRowField
                .Position = 1
            End With
            .AddDataField .PivotFields("White"), " White", xlSum
            .AddDataField .PivotFields("Black"), " Black", xlSum
            .AddDataField .PivotFields("Hispanic"), " Hispanic", xlSum
            .AddDataField .PivotFields("Asian"), " Asian", xlSum
            .AddDataField .PivotFields("AmericanIndian"), " AmericanIndian", xlSum
            .AddDataField .PivotFields("Male"), " Male", xlSum
            .AddDataField .PivotFields("Female"), " Female", xlSum
            .AddDataField .PivotFields("Total"), " Total", xlSum
        End With

        ' 创建隐藏切片器关联两个透视表
        Dim SC As SlicerCache
        ' 从包含JobGroup字段的第一个透视表创建切片器缓存
        Set SC = .SlicerCaches.Add(pvtInOut, "JobGroup")
        ' 添加切片器（若不需要显示可设置Visible=False）
        SC.Slicers.Add wk, , "JobGroup", "JobGroup", 50, 1000, 150, 200
        ' 将第二个透视表关联到切片器缓存
        SC.PivotTables.AddPivotTable pvtSparkline
    End With
End Sub

额外说明 #

1. 确保两个透视表共享同一个PivotCache：你的代码已经做到了（用同一个pvc创建两个透视表），这是切片器能同时控制两个表的前提。
2. 若不需要显示切片器，可以在创建后设置SC.Slicers("JobGroup").Visible = False。
3. Excel 2016+对VBA操作切片器的兼容性要求更高，建议尽量使用对应版本的对象模型（比如将xlPivotTableVersion14改为xlPivotTableVersion15或更高，不过xlPivotTableVersion14在2016中也能兼容）。

内容的提问来源于stack exchange，提问作者Yuqian