Chunk222
04-10-2018, 02:57 PM
Goal: To filter each "pre-structured" pivot table with different ranges of dates, (the ranges are all continuous), the pivot tables are connected to a Tabular OLAP Cube
Script Description : In the script, (full script at the bottom of the post) I loop through the Years and then the Quarters (called Trimesters here), Months and finally the Dates as needed (the dates are calculated via a worksheet and called using a custom function), the idea here is to simulate what a user does with his mouse.
So at the moment with the below Sub, for a given PivotTable, in order to select for example all dates starting from the 1st of January 2017 up until Sunday the 8th of April included I will select :
2017
then the first Quarter of 2018 (called "T1-JFM" below)
then nothing for Months
then all the dates of April up until the 8th included
Problem :
Once I hit the Sub that concerns the Dates ie: Cycle_Date, I get an error on the line:
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
The type of error message encountered on that line is "Run-time error ' 13': Type mismatch"
that line of code is the line that actually applies the Date filters, (prior to applying the filters the variable DIM_ARRAY is an Array that "accumulates" the strings used in the filter).
Already attempted :
The full Macro at the bottom of the post (The Dim(s) are at the top and Functions at the bottom)
.PivotFilters.Add2 method but it doesn't seem to work seen as it is a
xlPageField field
Playing around with the quotation marks for DIM_ARRAY (both """" and Chr(32) )in multiple shapes and forms
Taking off the array() function --> ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = DIM_ARRAY
I also tried to apply the filter at each iteration, that works, but only the last "date" is kept, the previous ones are erased so i cannot add dates
BIG CLUE: Hardcoding the value of DIM-ARRAY works... so for some reason
THIS DOESN'T WORK--->
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)
DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")
If DATE_i_min = DATE_i_max Then ' Fork-out scenario
i = 1
DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
Exit Sub
End If
For i = DATE_i_min To DATE_i_max ' Loop through
DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
If i = DATE_i_min Then
DIM_ARRAY = DIM_ARRAY_elmt
ElseIf i = DATE_i_max Then
DIM_ARRAY = DIM_ARRAY & DIM_ARRAY_elmt
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & DIM_ARRAY_elmt
End If
Next
Debug.Print "Dates -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
End Sub
but replacing DIM_ARRAY with its value, this DOES WORK !! --->
(here I replaced DIM_ARRAY with exactly what is evaluated by Debug.Print DIM_ARRAY)
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)
DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")
If DATE_i_min = DATE_i_max Then ' Fork-out scenario
i = 1
DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
Exit Sub
End If
For i = DATE_i_min To DATE_i_max ' Loop through
DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
If i = DATE_i_min Then
DIM_ARRAY = """" & DIM_ARRAY_elmt & """"
ElseIf i = DATE_i_max Then
DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
End If
Next
Debug.Print DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array( _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-01T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-02T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-03T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-04T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-05T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-06T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-07T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-08T00:00:00]")
End Sub
Any ideas how I can resolve this? if you need additional information please don't hesitate
I'm on Excel VBA (MS0 365 - version 1708)
Full Macro below:
Option Explicit
' "_i" is for the iterating variables
' "min" and "max" are for the minimum and maximum bounds that define the iteration range
' "TRIMESTRE" is just another word for "QUARTER"
Dim DIM_DATE_MOD_YEAR As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_TRIMESTRE As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_MONTH As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_DATE As String ' for storing OLAP syntax
Dim DIM_DATE_CREATION_BASE As String 'changes for each PivotTable "structure" but here we are using just one type of "structure" and just filtering the dates differently
Dim DIM_DATE_SUB As String 'changes for each Sub only, so only as we "loop" and/or "fork" and/or "fork/past" throughout the relevant Years Quarters Months and Dates
Dim DIM_DATE_PTF As String 'is used for the PivotTableField when the filters are applied
Dim DIM_ARRAY_elmt As String ' "feeds" DIM_ARRAY
Dim DIM_ARRAY As String ' is used as the string applied to the pivottable filters and is fed/stacked by DIM_ARRAY_elmt
Dim DIM_TEST As String
Dim i As Integer ' standard iterator
Dim YEAR_i As Integer
Dim YEAR_i_min As String
Dim YEAR_i_max As String
Dim TRIMESTRE_i As String
Dim TRIMESTRE_i_max As String
Dim TRIMESTRE_i_min As String
Dim MONTH_i As String
Dim MONTH_i_max As Integer
Dim MONTH_i_min As Integer
Dim DATE_i As Variant
Dim DATE_i_min As Integer
Dim DATE_i_max As Integer
Dim ws As Variant
Dim SheetNames As Variant
Dim SheetName As String
'Space
Sub Launch_Update()
'Application.ScreenUpdating = False
Call Date_Filters
'Application.ScreenUpdating = True
End Sub
Private Sub Date_Filters()
SheetNames = Array("NOW", "A-0 || J-7", "A-1 || à Date Equiv.", "A-1 || J-7 Atterissage") 'list of relevant sheets
'setting variables:
DIM_DATE_CREATION_BASE = "[DIM_DATE_CREATION].[CALENDRIER_CREATION]"
DIM_DATE_SUB = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_YEAR
i = 0
For Each ws In SheetNames
Sheets(ws).Select
SheetName = ActiveSheet.Name
DIM_DATE_MOD_YEAR = ".[ANNEE]"
DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_YEAR
Call Cycle_Year
i = 0
DIM_DATE_MOD_TRIMESTRE = ".[TRIMESTRE]"
DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_TRIMESTRE
Call Cycle_Trimestre(YEAR_i_max)
i = 0
DIM_DATE_MOD_MONTH = ".[MOIS]"
DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_MONTH
Call Cycle_Month(YEAR_i_max, TRIMESTRE_i)
i = 0
DIM_DATE_MOD_DATE = ".[DATE]"
DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_DATE
Call Cycle_Date(YEAR_i_max, TRIMESTRE_i, MONTH_i, MONTH_i_max)
Next ws
MsgBox "Date Filter Sub has ended"
End Sub
Private Sub Cycle_Year()
YEAR_i_min = StdFilter(SheetName, "YEAR_i_min")
YEAR_i_max = StdFilter(SheetName, "YEAR_i_max")
If YEAR_i_min = YEAR_i_max Then ' Fork-out scenario
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
Exit Sub
End If
For YEAR_i = YEAR_i_min To YEAR_i_max - 1 ' Loop through
DIM_ARRAY_elmt = DIM_DATE_PTF & ".&[" & YEAR_i & "]"
If YEAR_i = YEAR_i_min Then
DIM_ARRAY = DIM_ARRAY_elmt
ElseIf YEAR_i = YEAR_i_max - 1 Then
DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
End If
Next
Debug.Print "Years -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
End Sub
Private Sub Cycle_Trimestre(YEAR_i_max As String)
TRIMESTRE_i_min = StdFilter(SheetName, "TRIMESTRE_i_min")
TRIMESTRE_i_max = StdFilter(SheetName, "TRIMESTRE_i_max")
If TRIMESTRE_i_min = TRIMESTRE_i_max Then ' Fork-out scenario
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
Exit Sub
End If
For i = TRIMESTRE_i_min To TRIMESTRE_i_max ' Loop through
If i = 1 Then
TRIMESTRE_i = "T1 - JFM"
ElseIf i = 2 Then
TRIMESTRE_i = "T2 - AMJ"
ElseIf i = 3 Then
TRIMESTRE_i = "T3 - JAS"
ElseIf i = 4 Then
TRIMESTRE_i = "T4 - OND"
End If
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]"
If i = TRIMESTRE_i_min Then
DIM_ARRAY = DIM_ARRAY_elmt
ElseIf i = TRIMESTRE_i_max Then
GoTo ApplyFilter1
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
End If
Next
ApplyFilter1:
Debug.Print "Trimesters -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
End Sub
Private Sub Cycle_Month(YEAR_i_max As String, TRIMESTRE_i As String)
MONTH_i_min = StdFilter(SheetName, "MONTH_i_min")
MONTH_i_max = StdFilter(SheetName, "MONTH_i_max")
If MONTH_i_min = MONTH_i_max Then ' Fork-out scenario
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, MONTH_i_min, 1), "[$-40C]MMMM"))
Exit Sub
End If
For i = MONTH_i_min To MONTH_i_max ' Loop through
MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, i, 1), "[$-40C]MMMM"))
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]"
If i = MONTH_i_min Then
DIM_ARRAY = DIM_ARRAY_elmt
ElseIf i = MONTH_i_max Then
DIM_ARRAY = DIM_ARRAY_elmt
GoTo ApplyFilter2
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
End If
Next
ApplyFilter2:
Debug.Print "Months -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, i - 1, 1), "[$-40C]MMMM"))
End Sub
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)
DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")
If DATE_i_min = DATE_i_max Then ' Fork-out scenario
i = 1
DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
Exit Sub
End If
For i = DATE_i_min To DATE_i_max ' Loop through
DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
If i = DATE_i_min Then
DIM_ARRAY = """" & DIM_ARRAY_elmt & """"
ElseIf i = DATE_i_max Then
DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
End If
Next
Debug.Print "Dates -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array( _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-01T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-02T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-03T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-04T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-05T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-06T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-07T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-08T00:00:00]")
End Sub
Script Description : In the script, (full script at the bottom of the post) I loop through the Years and then the Quarters (called Trimesters here), Months and finally the Dates as needed (the dates are calculated via a worksheet and called using a custom function), the idea here is to simulate what a user does with his mouse.
So at the moment with the below Sub, for a given PivotTable, in order to select for example all dates starting from the 1st of January 2017 up until Sunday the 8th of April included I will select :
2017
then the first Quarter of 2018 (called "T1-JFM" below)
then nothing for Months
then all the dates of April up until the 8th included
Problem :
Once I hit the Sub that concerns the Dates ie: Cycle_Date, I get an error on the line:
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
The type of error message encountered on that line is "Run-time error ' 13': Type mismatch"
that line of code is the line that actually applies the Date filters, (prior to applying the filters the variable DIM_ARRAY is an Array that "accumulates" the strings used in the filter).
Already attempted :
The full Macro at the bottom of the post (The Dim(s) are at the top and Functions at the bottom)
.PivotFilters.Add2 method but it doesn't seem to work seen as it is a
xlPageField field
Playing around with the quotation marks for DIM_ARRAY (both """" and Chr(32) )in multiple shapes and forms
Taking off the array() function --> ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = DIM_ARRAY
I also tried to apply the filter at each iteration, that works, but only the last "date" is kept, the previous ones are erased so i cannot add dates
BIG CLUE: Hardcoding the value of DIM-ARRAY works... so for some reason
THIS DOESN'T WORK--->
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)
DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")
If DATE_i_min = DATE_i_max Then ' Fork-out scenario
i = 1
DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
Exit Sub
End If
For i = DATE_i_min To DATE_i_max ' Loop through
DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
If i = DATE_i_min Then
DIM_ARRAY = DIM_ARRAY_elmt
ElseIf i = DATE_i_max Then
DIM_ARRAY = DIM_ARRAY & DIM_ARRAY_elmt
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & DIM_ARRAY_elmt
End If
Next
Debug.Print "Dates -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
End Sub
but replacing DIM_ARRAY with its value, this DOES WORK !! --->
(here I replaced DIM_ARRAY with exactly what is evaluated by Debug.Print DIM_ARRAY)
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)
DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")
If DATE_i_min = DATE_i_max Then ' Fork-out scenario
i = 1
DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
Exit Sub
End If
For i = DATE_i_min To DATE_i_max ' Loop through
DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
If i = DATE_i_min Then
DIM_ARRAY = """" & DIM_ARRAY_elmt & """"
ElseIf i = DATE_i_max Then
DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
End If
Next
Debug.Print DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array( _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-01T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-02T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-03T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-04T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-05T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-06T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-07T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-08T00:00:00]")
End Sub
Any ideas how I can resolve this? if you need additional information please don't hesitate
I'm on Excel VBA (MS0 365 - version 1708)
Full Macro below:
Option Explicit
' "_i" is for the iterating variables
' "min" and "max" are for the minimum and maximum bounds that define the iteration range
' "TRIMESTRE" is just another word for "QUARTER"
Dim DIM_DATE_MOD_YEAR As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_TRIMESTRE As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_MONTH As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_DATE As String ' for storing OLAP syntax
Dim DIM_DATE_CREATION_BASE As String 'changes for each PivotTable "structure" but here we are using just one type of "structure" and just filtering the dates differently
Dim DIM_DATE_SUB As String 'changes for each Sub only, so only as we "loop" and/or "fork" and/or "fork/past" throughout the relevant Years Quarters Months and Dates
Dim DIM_DATE_PTF As String 'is used for the PivotTableField when the filters are applied
Dim DIM_ARRAY_elmt As String ' "feeds" DIM_ARRAY
Dim DIM_ARRAY As String ' is used as the string applied to the pivottable filters and is fed/stacked by DIM_ARRAY_elmt
Dim DIM_TEST As String
Dim i As Integer ' standard iterator
Dim YEAR_i As Integer
Dim YEAR_i_min As String
Dim YEAR_i_max As String
Dim TRIMESTRE_i As String
Dim TRIMESTRE_i_max As String
Dim TRIMESTRE_i_min As String
Dim MONTH_i As String
Dim MONTH_i_max As Integer
Dim MONTH_i_min As Integer
Dim DATE_i As Variant
Dim DATE_i_min As Integer
Dim DATE_i_max As Integer
Dim ws As Variant
Dim SheetNames As Variant
Dim SheetName As String
'Space
Sub Launch_Update()
'Application.ScreenUpdating = False
Call Date_Filters
'Application.ScreenUpdating = True
End Sub
Private Sub Date_Filters()
SheetNames = Array("NOW", "A-0 || J-7", "A-1 || à Date Equiv.", "A-1 || J-7 Atterissage") 'list of relevant sheets
'setting variables:
DIM_DATE_CREATION_BASE = "[DIM_DATE_CREATION].[CALENDRIER_CREATION]"
DIM_DATE_SUB = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_YEAR
i = 0
For Each ws In SheetNames
Sheets(ws).Select
SheetName = ActiveSheet.Name
DIM_DATE_MOD_YEAR = ".[ANNEE]"
DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_YEAR
Call Cycle_Year
i = 0
DIM_DATE_MOD_TRIMESTRE = ".[TRIMESTRE]"
DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_TRIMESTRE
Call Cycle_Trimestre(YEAR_i_max)
i = 0
DIM_DATE_MOD_MONTH = ".[MOIS]"
DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_MONTH
Call Cycle_Month(YEAR_i_max, TRIMESTRE_i)
i = 0
DIM_DATE_MOD_DATE = ".[DATE]"
DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_DATE
Call Cycle_Date(YEAR_i_max, TRIMESTRE_i, MONTH_i, MONTH_i_max)
Next ws
MsgBox "Date Filter Sub has ended"
End Sub
Private Sub Cycle_Year()
YEAR_i_min = StdFilter(SheetName, "YEAR_i_min")
YEAR_i_max = StdFilter(SheetName, "YEAR_i_max")
If YEAR_i_min = YEAR_i_max Then ' Fork-out scenario
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
Exit Sub
End If
For YEAR_i = YEAR_i_min To YEAR_i_max - 1 ' Loop through
DIM_ARRAY_elmt = DIM_DATE_PTF & ".&[" & YEAR_i & "]"
If YEAR_i = YEAR_i_min Then
DIM_ARRAY = DIM_ARRAY_elmt
ElseIf YEAR_i = YEAR_i_max - 1 Then
DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
End If
Next
Debug.Print "Years -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
End Sub
Private Sub Cycle_Trimestre(YEAR_i_max As String)
TRIMESTRE_i_min = StdFilter(SheetName, "TRIMESTRE_i_min")
TRIMESTRE_i_max = StdFilter(SheetName, "TRIMESTRE_i_max")
If TRIMESTRE_i_min = TRIMESTRE_i_max Then ' Fork-out scenario
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
Exit Sub
End If
For i = TRIMESTRE_i_min To TRIMESTRE_i_max ' Loop through
If i = 1 Then
TRIMESTRE_i = "T1 - JFM"
ElseIf i = 2 Then
TRIMESTRE_i = "T2 - AMJ"
ElseIf i = 3 Then
TRIMESTRE_i = "T3 - JAS"
ElseIf i = 4 Then
TRIMESTRE_i = "T4 - OND"
End If
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]"
If i = TRIMESTRE_i_min Then
DIM_ARRAY = DIM_ARRAY_elmt
ElseIf i = TRIMESTRE_i_max Then
GoTo ApplyFilter1
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
End If
Next
ApplyFilter1:
Debug.Print "Trimesters -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
End Sub
Private Sub Cycle_Month(YEAR_i_max As String, TRIMESTRE_i As String)
MONTH_i_min = StdFilter(SheetName, "MONTH_i_min")
MONTH_i_max = StdFilter(SheetName, "MONTH_i_max")
If MONTH_i_min = MONTH_i_max Then ' Fork-out scenario
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, MONTH_i_min, 1), "[$-40C]MMMM"))
Exit Sub
End If
For i = MONTH_i_min To MONTH_i_max ' Loop through
MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, i, 1), "[$-40C]MMMM"))
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]"
If i = MONTH_i_min Then
DIM_ARRAY = DIM_ARRAY_elmt
ElseIf i = MONTH_i_max Then
DIM_ARRAY = DIM_ARRAY_elmt
GoTo ApplyFilter2
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
End If
Next
ApplyFilter2:
Debug.Print "Months -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, i - 1, 1), "[$-40C]MMMM"))
End Sub
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)
DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")
If DATE_i_min = DATE_i_max Then ' Fork-out scenario
i = 1
DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
Exit Sub
End If
For i = DATE_i_min To DATE_i_max ' Loop through
DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
If i = DATE_i_min Then
DIM_ARRAY = """" & DIM_ARRAY_elmt & """"
ElseIf i = DATE_i_max Then
DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
ElseIf DIM_ARRAY <> "" Then
DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
End If
Next
Debug.Print "Dates -->"; DIM_ARRAY
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array( _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-01T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-02T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-03T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-04T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-05T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-06T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-07T00:00:00]", _
"[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-08T00:00:00]")
End Sub