VBA PivotCache & PivotCharts not working in Excel 2010 but working in Excel 2013 RRS feed

  • Question

  • When I attempt to run the following code in Excel 2010, I get an error with assigning the PivotCache.

    Is there a difference between Excel 2010 and 2013 related to using a PivotCache that I should be aware of?

         Application.ScreenUpdating = False
        Application.EnableEvents = False
        Dim PivotCache1, PivotCache2, PivotCache3 As PivotCache
        Dim QueryArray() As String
        ReDim QueryArray(NumberOfDays)
    'Delete all current charts
        Dim Chrt As ChartObject
        For Each Chrt In Sheets(1).ChartObjects
        Next Chrt
    'Build array for specified dates
    JulianDate = Date2Julian(Date)
    For i = 0 To NumberOfDays
    QueryArray(i) = "[001-Process Reporting Date].[Julian Date].&[" & JulianDate - i & "]"
    Next i
    ''''''''''''''''''''''''''''''''''''''''''''''''Chart 1''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Assign cache and create chart
        Set PivotCache1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, _
        SourceData:=ActiveWorkbook.Connections(Plant), Version:=xlPivotTableVersion15)
        PivotCache1.CreatePivotChart(ChartDestination:=Sheets(1), XlChartType:=xlLineMarkers, Left:=200, Top:=0, Width:=600, Height:=300).Select
        With ActiveChart
            '.ChartStyle = 236 Dark Theme
            .ChartStyle = 232
            .ApplyLayout (5)
            .HasTitle = False
        End With
    'Filter for Ribbon only
        With ActiveChart.PivotLayout.PivotTable.CubeFields("[002-Process Area].[Process Area]")
            .Orientation = xlPageField
            .Position = 1
        End With
        ActiveChart.PivotLayout.PivotTable.PivotFields("[002-Process Area].[Process Area].[Plant]").CurrentPageName = _
            "[002-Process Area].[Process Area].[Plant].&[" & PlantCode & "].&[Positive Flow].&[Ribbon].&[" & ProcessArea & "]"
    'Filter date
        With ActiveChart.PivotLayout.PivotTable.CubeFields( _
            "[001-Process Reporting Date].[Julian Date]")
            .Orientation = xlPageField
            .Position = 2
        End With
        ActiveChart.PivotLayout.PivotTable.CubeFields(13).EnableMultiplePageItems = True
        ActiveChart.PivotLayout.PivotTable.PivotFields("[001-Process Reporting Date].[Julian Date].[Julian Date]").VisibleItemsList = QueryArray
    'Add horizontal julian date & week day
        With ActiveChart.PivotLayout.PivotTable.CubeFields("[001-Process Reporting Date].[Julian Date]")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveChart.PivotLayout.PivotTable.CubeFields("[001-Process Reporting Date].[Day Of Week]")
            .Orientation = xlRowField
            .Position = 2
        End With
    'Add Chart Fields
        ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout.PivotTable.CubeFields("[Measures].[FPY%]")
        ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout.PivotTable.CubeFields("[Measures].[OEE Performance (%)]")
        ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout.PivotTable.CubeFields("[Measures].[OEE Availability Operation(%)]")

    Monday, October 30, 2017 8:46 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel


    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, October 31, 2017 2:01 AM
  • Hello,

    >>Set PivotCache1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, _
        SourceData:=ActiveWorkbook.Connections(Plant), Version:=xlPivotTableVersion15)

    XlPivotTableVersionList Enumeration (Excel) specifies the version of a PivotTable or a PivotCache.

    xlPivotTableVersion15 indicates you create for Office 2013. Please change it into xlPivotTableVersion14 when you use the macro in Office 2010.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 1, 2017 6:05 AM