Consulting

Results 1 to 7 of 7

Thread: Need Assistance with Pivot Table Change

  1. #1
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location

    Question Need Assistance with Pivot Table Change

    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.
    Last edited by BBlevins; 10-12-2018 at 08:33 AM.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location
    Quote Originally Posted by p45cal View Post
    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!

  4. #4
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location
    Quote Originally Posted by p45cal View Post
    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

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    x = Array(VIL & ItemString,VIL2 & ItemString2,VIL3 & ItemString3)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location

    Red face

    Quote Originally Posted by p45cal View Post
    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

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •