none
Calendar change in Pivot Table based on OLAP data source RRS feed

  • Question

  • Hello,

    I'd like to automatically change calendar in Pivot Table Based on OLAP data source in Excel 2010.

    So, when have property EnableMultiplePageItems=False for the field Calendar. I writing macro using macrorecorder (setting year in PageFild calendar = 2006 year) and after stopping record im getting this code:

    ActiveSheet.PivotTables("Pivot Table 1").PivotFields( _
            "[Calendar].[Calendar].[Year]").ClearAllFilters
        ActiveSheet.PivotTables("Pivot Table 1").PivotFields( _
            "[Calendar].[Calendar].[Year]").CurrentPage = _
            "[Calendar].[Calendar].[Year].&[2006]"

    The problem is that when i run this macro the massage error apear:

    You can't set propertie CurrentPage of the class PivotField.

    Could somebody explain this problem or point on some other method of setting calendar field (EnableMultiplePageItems=False)?

    Thank you in advanced!

    Thursday, August 9, 2012 9:35 AM