VBA code to update pivot filters

[sbmaurer]

I am trying to automatically update multiple pivot tables from one fixed button at the begining of the work book.

I have been able to do it with this code for normal pivot tables. But the pivot tables at work are directly linked to an OLAP query, I have a suspicion that they do not have the same PivotFields.

Do you know how I can assign a pivot field to the OLAP query Pivot table, Is there a way to look up the properties of the OLAP pivot table and then assin the "pviotfield" = to the correct property. ???????????????????



Sub UpDatePivot(Worksht As String, TableName As String)
' CHANGES THE FILTER PIVOT TABLE
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterName As String

Set pvtTable = Worksheets(Worksht).PivotTables(TableName)
Set pvtField = pvtTable.PivotFields("Month") <--------- Unable to get the PivotFields Property Of the Pivot Table Class

filterName = Worksheets("UpdateingInstructions").Range("P10")

For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterName Then
pvtField.CurrentPage = filterName
Exit For
End If
Next pvtItem
End Sub

Sub UpdateAll()
Call UpDatePivot("Top15PL", "15PolymerSales")
Call UpDatePivot("Top15PL", "15PolymerRM")
Call UpDatePivot("Top15PL", "15PolymerCust")
Call UpDatePivot("Top15PL", "15IndustrialSales")
Call UpDatePivot("Top15PL", "15IndustrialRM")
Call UpDatePivot("Top15PL", "15IndustrialCust")
Call UpDatePivot("Top15PL", "15ConsumerSales")
Call UpDatePivot("Top15PL", "15ConsumerRM")
Call UpDatePivot("Top15PL", "15ConsumerCust")
End Sub

 

[Jerry Sullivan]

So I just thought that I can type Q4 2011 in cell E6 on "UpdatingIntstructions"

You're going to make the users work that hard to enter in both the date and quarter?

If your fiscal year aligns with the calendar year, you could use this formula in E6 to convert the date in A17 to your Quarter string.

="Q"&TRUNC(MONTH(A17)/3)&" "&YEAR(A17)

 

[sbmaurer]

Hehe, anything would be better than this current system, I am going to try what you just proposed

And when I was doing it the way with Q4 2011 as a string, it wouldn't work when qtIn was called through

Sub Filter_Multiple_Items_QTD_3(Worksht As String, TableName As String)
'------This code is used to update the Last secon pivots using cell g:5 and below
'------it uses the format quarter and using quarter format function
    Dim vDateItems() As Variant, c As Range
    Dim dtOldest As Date, lCount As Long
    dtOldest = #1/1/1970# 'used to detect blanks, incorrect dates
    
    '---build Variant of OLAP formatted date items
    If Worksheets("UpdatingInstructions").Range("E6") = vbNullString Then Exit Sub
    For Each c In Worksheets("UpdatingInstructions").Range("E6:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        If (Not IsDate(c)) Or c < dtOldest Then
            MsgBox "Invalid Date found in: " & c.Address
            Exit Sub
        End If
        lCount = lCount + 1
        ReDim Preserve vDateItems(lCount)
        vDateItems(lCount) = OLAP_Format_Quarter(qtIn:=c.Value)
    Next c
 
'-------------------
'For testing only: displays array items in VBA Editor Immediate Window
    Dim i As Long
    For i = LBound(vDateItems) To UBound(vDateItems)
        Debug.Print vDateItems(i)
    Next i
'-------------------
    Filter_Cube_PivotField _
       pvtField:=Worksheets(Worksht).PivotTables(TableName) _
        .PivotFields("[Date].[Quarter Filter].[Quarter Filter]"), _
            varArrIn:=vDateItems
End Sub

I am assuming because of a coupl parts in that code,

first Dim dtOldest As Date and second the If statement.

How would I modify the above to accept a string value? Hmmm..
Is it easy and quick?

I am now going to use your formula suggestion.

 

[sbmaurer]

Wait, lol, I think I wil stil run into the same problem as I stated above.

haha, I have never even seen a TRUNC formula before ! Gosh so much to learn!

Yes so the Filter_Multiple_Items_QTD_3 from above will still return a msg baox sating invalid date found.

I must need to modify some parts of it to tell it to look at the string

 

[Jerry Sullivan]

Wait, lol, I think I wil stil run into the same problem as I stated above.

haha, I have never even seen a TRUNC formula before ! Gosh so much to learn!

Yes so the Filter_Multiple_Items_QTD_3 from above will still return a msg baox sating invalid date found.

I must need to modify some parts of it to tell it to look at the string

The reason that it isn't working is because of the test that it is a valid date.
You could eliminate that since you are doing some validation at the cell level or modify it to do a different test.

Do you want to have the Quarter displayed in E6 or are you only doing that to feed the correct cube filter into your PivotTable?

If it's the latter, than you can take the value from A17 and do a conversion in VBA similar to the formula I suggested for E6.

 

[sbmaurer]

The test beiing the If loop, where is says (Not IsDate(c))?

I think what would be a great first place to start is having just the Q4 2010 in cell E:6 and everything below.

Kind of the same way that the months are listed in previous examples.

Really I think I want the quarter displayed in e:6 and below, sometimes I need like Q4 2010 and Q4 2011

 

[Jerry Sullivan]

The test beiing the If loop, where is says (Not IsDate(c))?

Yes, Delete the entire If block from If to End If.