PDA

View Full Version : [SOLVED] Need Assistance with Pivot Table Change



BBlevins
10-12-2018, 07:58 AM
I am trying to automate date selection changes in a pivot table. Below is the code that I cannot seem to get to work. I have hard-coded the value for my variable "MYString" just for the purposes of testing. I have added some notes in the code to clarify. I am using Excel 2013.




Sub MOM_2()




Dim MYString As String


MYString = " Array("""", """", ""[Report Date].[Date].[Day].&[2018-10-01T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-10-02T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-09-01T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-09-02T00:00:00]"")"
Debug.Print MYString


ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
"[Report Date].[Date].[Year]").VisibleItemsList = Array("")

ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
"[Report Date].[Date].[Quarter]").VisibleItemsList = Array("")

ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
"[Report Date].[Date].[Month]").VisibleItemsList = Array("")

'If uncommented, the line below works.
'ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = Array("", "", "[Report Date].[Date].[Day].&[2018-10-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-10-02T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-02T00:00:00]")

'This is the line where I use my variable. When I check the value of the variable from debug.print it matches the line above exactly from the word Array to the end.
'In fact, if I copy the value of "MYString" from the immediate window and paste it over the word MyString it runs fine.

ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = MYString

End Sub



Thanks in advance. I've been wracking my brain on this one and it is probably a simple fix.

p45cal
10-12-2018, 08:30 AM
A guess, try:
x = Array("", "", "[Report Date].[Date].[Day].&[2018-10-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-10-02T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-02T00:00:00]")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x

BBlevins
10-12-2018, 08:46 AM
A guess, try:
x = Array("", "", "[Report Date].[Date].[Day].&[2018-10-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-10-02T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-02T00:00:00]")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x



Interesting. It works. The difference is that your method makes the variable a "Variant' Data Type by default since it is not being "Dim'ed". So by changing my declaration to make the variable a Variant data type and removing the double quotes it works. I would have thought that passing it as a string would have worked. Let me do just a little testing and then I will mark this as solved. Thank You!

BBlevins
10-12-2018, 10:47 AM
A guess, try:
x = Array("", "", "[Report Date].[Date].[Day].&[2018-10-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-10-02T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-02T00:00:00]")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x


OK, so here is my question. If I hardcode it, it works and that is great. However, I need to be able to build the string dynamically based on how many days are elapsed in the month. I do that using two different variables. I need them to feed into the Array.


'So instead of this
x = Array("", "", "[Report Date].[Date].[Day].&[2018-10-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-10-02T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-02T00:00:00]")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x

I need something like this where VIL and ItemString are my variables that I build:
x = Array(VIL & ItemString
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x

p45cal
10-12-2018, 04:05 PM
x = Array(VIL & ItemString,VIL2 & ItemString2,VIL3 & ItemString3)

BBlevins
10-14-2018, 05:38 AM
x = Array(VIL & ItemString,VIL2 & ItemString2,VIL3 & ItemString3)



OK, thank you for helping me out. I wanted to post the entire function in case it might ever help anyone else. I am sure I could clean it up a bit and make it look better but I have spent way too much time already for what this is. I had to use the split function to break the string down into elements to populate the array. I am marking this as solved and moving on with my life.




Sub MoM_Arr()'
' Updates MTD Pivot to show correct dates for Month over Month comparison
'


Dim i As Long
Dim NUMDAYS As Integer
Dim ArrCnt As Integer
Dim VIL As Variant
Dim ItemString As Variant
Dim pvtItems As Variant
Dim CurYear As String
Dim CompYear As String
Dim CurMonth As String
Dim CompMonth As String
Dim MyDay As String




'Populate Date Variables
NUMDAYS = ActiveWorkbook.Worksheets("Tools").Range("NUMDAYS")
CurMonth = ActiveWorkbook.Worksheets("Tools").Range("MAXMONTH")
CompMonth = ActiveWorkbook.Worksheets("Tools").Range("COMPMONTH")
CurYear = ActiveWorkbook.Worksheets("Tools").Range("MAXYEAR")
CompYear = ActiveWorkbook.Worksheets("Tools").Range("COMPYEAR")

'Create the strings for the array
For i = 1 To NUMDAYS
Select Case i
Case Is = NUMDAYS
VIL = VIL & "" & Chr(44) & Chr(32)
Case Else
VIL = VIL & "" & Chr(44) & Chr(32)
End Select
Next i




For i = 1 To NUMDAYS
Select Case i
Case Is < 10
MyDay = CStr(0 & i)
Case Else
MyDay = CStr(i)
End Select

Select Case Len(CurMonth)
Case Is = 1
CurMonth = CStr(0 & CurMonth)
Case Else
CurMonth = CStr(CurMonth)
End Select

ItemString = ItemString & "[Report Date].[Date].[Day].&[" & CurYear & "-" & CurMonth & "-" & MyDay & "T00:00:00]" & Chr(44) & Chr(32)


Select Case Len(CompMonth)
Case Is = 1
CompMonth = CStr(0 & CompMonth)
Case Else
CompMonth = CStr(CompMonth)
End Select

ItemString = ItemString & "[Report Date].[Date].[Day].&[" & CompYear & "-" & CompMonth & "-" & MyDay & "T00:00:00]"

Select Case i
Case Is = NUMDAYS
'Dont add a delimiter at the end, this is the last element

Case Else
ItemString = ItemString & Chr(44) & Chr(32)
Debug.Print ItemString
End Select
Next i


'Combine the array strings
ItemString = VIL & ItemString

'Populate the array
pvtItems = Split(ItemString, ", ")


'Update the pivot table to show the correct dates
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = Array(pvtItems)



End Sub

p45cal
10-14-2018, 10:18 AM
You're creating a long string then splitting it. You can create the array directly. Try (this produces the same as your code (as far as I can tell)):
Sub MoM_Arr01() '
' Updates MTD Pivot to show correct dates for Month over Month comparison
Dim i As Long, NUMDAYS As Integer, Posn As Long
Dim pvtItems() As Variant
Dim CurYear As String, CompYear As String, CurMonth As String, CompMonth As String, MyDay As String

'Populate Date Variables
With ActiveWorkbook.Worksheets("Tools")
NUMDAYS = .Range("NUMDAYS")
CurMonth = .Range("MAXMONTH")
CompMonth = .Range("COMPMONTH")
CurYear = .Range("MAXYEAR")
CompYear = .Range("COMPYEAR")
End With
ReDim pvtItems(0 To 3 * NUMDAYS - 1)
CurMonth = Format(CurMonth, "00")
CompMonth = Format(CompMonth, "00")
Posn = NUMDAYS
'Create the strings for the array
For i = 1 To NUMDAYS
MyDay = Format(i, "00")
pvtItems(i - 1) = ""
pvtItems(Posn) = "[Report Date].[Date].[Day].&[" & CurYear & "-" & CurMonth & "-" & MyDay & "T00:00:00]"
pvtItems(Posn + 1) = "[Report Date].[Date].[Day].&[" & CompYear & "-" & CompMonth & "-" & MyDay & "T00:00:00]"
Posn = Posn + 2
Next i
'Update the pivot table to show the correct dates
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = pvtItems
End Sub


but I think that perhaps you don't need so many "" items (they're all the same) so you might be able to get away with just one:
Sub MoM_Arr02()
' Updates MTD Pivot to show correct dates for Month over Month comparison
Dim i As Long, NUMDAYS As Integer
Dim pvtItems() As Variant
Dim CurYear As String, CompYear As String, CurMonth As String, CompMonth As String, MyDay As String
'Populate Date Variables
With ActiveWorkbook.Worksheets("Tools")
NUMDAYS = .Range("NUMDAYS")
CurMonth = .Range("MAXMONTH")
CompMonth = .Range("COMPMONTH")
CurYear = .Range("MAXYEAR")
CompYear = .Range("COMPYEAR")
End With
ReDim pvtItems(0 To 2 * NUMDAYS)
pvtItems(0) = ""
CurMonth = Format(CurMonth, "00")
CompMonth = Format(CompMonth, "00")
'Create the strings for the array
For i = 1 To NUMDAYS
MyDay = Format(i, "00")
pvtItems(i * 2 - 1) = "[Report Date].[Date].[Day].&[" & CurYear & "-" & CurMonth & "-" & MyDay & "T00:00:00]"
pvtItems(i * 2) = "[Report Date].[Date].[Day].&[" & CompYear & "-" & CompMonth & "-" & MyDay & "T00:00:00]"
Next i
'Update the pivot table to show the correct dates
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = pvtItems
End Sub
I'm a bit surprised your code's last line ends with:
.VisibleItemsList = Array(pvtItems)
rather than just:
.VisibleItemsList = pvtItems
but I can't test here without the pivot table.