时间戳

首页 > 解决方案 > 使用 VBA 将数据透视表设置为基于另一个字段的特定日期

excel - 使用 VBA 将数据透视表设置为基于另一个字段的特定日期

问题描述

我已经尝试了很多我在网上找到的迭代，但无法让它工作。我有一个需要每周更新的数据透视表，并且需要根据工作表中的字段将日期设置为变量。日期是一个过滤器。使用下面的代码，我可以将日期更改为特定日期。

Sub Macro7()

'
 ActiveSheet.PivotTables("PivotTable2").PivotFields("[Test_Data].[Date].[Date]" _
        ).ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("[Test_Data].[Date].[Date]" _
        ).CurrentPageName = "[Test_Data].[Date].&[2020-07-12T00:00:00]"
End Sub

但是，当我尝试将日期设置为变量（例如指向包含日期的工作表单元格的字符串）时，它会出错，通常会显示“无法设置 PivotField 类的 CurrentPage 属性”。我尝试了许多不同的选项和不同的代码，但没有任何效果。D2 是日期发生变化的地方。它根据公式进行更新。我也尝试过硬编码单元格中的日期，但这也不起作用。还尝试将日期格式与枢轴匹配，但没有帮助。如果重要的话，该字段会提取上周五的日期。如果有帮助的话，我会很好地将它放入代码中（我试过了，我得到了一个错误，即字符串无法转换为日期）。

Sub My_macro()
    Dim str As String
    str = ActiveSheet.Range("d2")
    ActiveSheet.PivotTables("PivotTable2").PivotFields("[Test_Data].[Date].[Date]" _
        ).ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("[Test_Data].[Date].[Date]").CurrentPage = str
End Sub

有人可以告诉我如何纠正这个吗？已经尝试了几个小时，只是无法弄清楚。谢谢。PS--我试着上传一张表格，但我不知道怎么做，看起来我只能做图片，所以我上传了。下面是日期过滤器问题所在的支点，下面是提供支点的示例。
样本

数据源示例

编辑：已在此处共享文件示例： https ://www.dropbox.com/sh/4tzrrue0mggf6om/AAAd6024feSdc9MbCd9IZD0Wa?dl=0

标签： excelvbapivot-tablefiltering

解决方案

当你录制一个宏时，你会得到这样的东西

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable2").PivotFields("[_Data].[Date].[Date]"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("[_Data].[Date].[Date]"). _
        CurrentPage = "[_Data].[Date].&[2019-08-16T00:00:00]"
End Sub

即使宏为您提供了该代码，如果您直接运行它而不更改任何内容，它也会出错。

解决方案：这是一个非常古老的问题，可以通过更改CurrentPage为CurrentPageName

现在改变它以满足您的需要......

Sub Sample()
    Dim ws As Worksheet
    Set ws = Sheet1
    
    Dim rngDt As Range
    Set rngDt = ws.Range("D2")
    
    Dim dtString As String
    dtString = Format(rngDt.Value2, "yyyy-mm-dd")
    
    Dim pt As PivotTable
    Set pt = ws.PivotTables("PivotTable2")
    
    Dim pf As PivotField
    Set pf = pt.PivotFields("[_Data].[Date].[Date]")
    
    pf.ClearAllFilters
    pf.CurrentPageName = "[_Data].[Date].&[" & dtString & "T00:00:00]"
End Sub

编辑

请问...我如何在同一张纸上选择多个枢轴并对它们的过滤器执行此操作，为所有它们选择相同的日期（单元格 D2）？只需重复代码，替换名称，或者像数组这样的东西可以工作吗？再次感谢您，将您的标记为答案！– learningthisstuff 2天前

您可以循环执行此操作。对于 ws.PivotTables 中的每个 pt，然后对于 pt.PivotFields 中的每个 pf :) – Siddharth Rout 2 天前

谢谢....我把这些线放在哪里？我尝试了几个我认为有意义但不断出错的地方。我会继续尝试选项，想知道是否需要删除“set pt/pf”行？谢谢你。– learningthisstuff 14 小时前 >

他们有相同的字段“日期”吗？– Siddharth Rout 13 小时前

是的，它们几乎完全相同。相同的过滤器。– learningthisstuff 11 小时前

您可以尝试这样的事情（未经测试）

Sub Sample()
    Dim ws As Worksheet
    Set ws = Sheet1
    
    Dim rngDt As Range
    Set rngDt = ws.Range("D2")
    
    Dim dtString As String
    dtString = Format(rngDt.Value2, "yyyy-mm-dd")
    
    Dim pt As PivotTable
    Dim pf As PivotField
    
    For Each pt In ws.PivotTables
        Set pf = pt.PivotFields("[_Data].[Date].[Date]")
        pf.ClearAllFilters
        pf.CurrentPageName = "[_Data].[Date].&[" & dtString & "T00:00:00]"
    Next pt
End Sub

编辑

根据 OP 对此答案的编辑要求，由于存在多个枢轴，因此最终对上述代码进行了以下更改。

Sub Sample2()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")

    Dim rngDt As Range
    Set rngDt = ws.Range("F32")

    Dim dtString As String
    dtString = Format(rngDt.Value2, "yyyy-mm-dd")

    Dim pt As PivotTable
    Dim pf As PivotField

    For Each pt In ws.PivotTables
        If pt = "PivotTable2" Then
            Set pf = pt.PivotFields("[_Data].[Date].[Date]")
            pf.ClearAllFilters
            pf.CurrentPageName = "[_Data].[Date].&[" & dtString & "T00:00:00]"
        Else
            Set pf = pt.PivotFields("[Sales_Data].[Date].[Date]")
            pf.ClearAllFilters
            pf.CurrentPageName = "[Sales_Data].[Date].&[" & dtString & "T00:00:00]"
        End If
    Next pt
End Sub

推荐阅读