PDA

View Full Version : Getting “Type Mismatch” with VBA PivotTables and Filter Dates



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