使用VBA在Visibleitemslist中过滤OLAP枢轴表中的年份和月份

Question

我一直试图自动化这个宏，它根据输入日期字段过滤日期范围(我将输入日期作为2022年7月)。应用这种日期范围过滤器的方式应包括输入年份(即2020年至2022年)之前的三年和输入月份(1月至7月)之前的所有月份。

我不知道如何为下面的过滤器创建一个通用的for循环，该循环将根据输入字段自动更改年份和月份。我尝试为当前年份的月份创建一个for循环，但也不知道如何组合前三年的范围。

Sub Macro2()
'
' Macro2 Macro
'

'
ActiveSheet.PivotTables("NetRate22").PivotFields( _
"[Policy State Dimensions].[Effective Date].[Effective Month]"). _
VisibleItemsList = Array( _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[1]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[2]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[3]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[4]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[5]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[6]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[7]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[1]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[2]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[3]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[4]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[5]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[6]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[7]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[1]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[2]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[3]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[4]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[5]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[6]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[7]")

End Sub

我已经尝试了一个月来解决这个问题，并且还没有找到VBA中最好的资源来帮助这个解决方案。任何对这个问题的帮助都是非常感谢的。谢谢!

Answer 1

未经测试。

Sub Macro2()

   ' visible item list
    Const VIL = "[Policy State Dimensions].[Effective Date].[Effective Month]"
    Const YRS = 3 ' prior years

    Dim dt As Date
    dt = "1/7/2022" ' July 2022
     
    Dim mth As Long, yr As Long
    Dim m As Long, y As Long, i As Long
    Dim ar
   
    mth = Month(dt)
    yr = Year(dt)
    ReDim ar(0 To mth * YRS - 1)

    ' build array
    i = 0
    For y = YRS To 1 Step -1
        For m = 1 To mth
           ar(i) = VIL & ".&[" & (yr - y + 1) & "]&[" & m & "]"
           i = i + 1
        Next
    Next
    'Debug.Print Join(ar, vbLf)
    
    ActiveSheet.PivotTables("NetRate22").PivotFields(VIL).VisibleItemsList = ar
    
End Sub