Pivot Table Grouping - PIA C# RRS feed

  • Question

  • This is regarding grouping of pivot table using Primary Interop Assemblies and (C#)

    In excel pivot table, if we manually drag and drop a Pivot table field to "ROWS" section, it automatically fits in the appropriate position and pivot refreshes accordingly
    For example, let us consider we have pivotfields Field1, Field2, Field3, Field4, Field5.
    In the rows section, I am dragging dropping Field1, Field4 as first and second fields.
    If I drag and drop Field3 in between, pivot table adds it as second field in rows section, and refreshes the pivot table for the changes (Now the order of the row fields will be Field1, Field3, Field4)

    Programmatically (using C#), I am unable to make Field3 as the second field in the above scenario in a single step.

    First, I need to make the orientation of Field3 as RowField and then change the position to second.
    Because when I make the Field3 orientation as rowfield, it will be added up as the last field.

    field3.Orientation = EXCEL.XlPivotFieldOrientation.xlRowField;
    field3.Position = _requiredposition;

    This affects the performance when the pivot table has more data as there are two steps involved.
    Could anyone pls let me know if there is a better way to accomplish this in a single step ?

    Toggling Manual update true and false, before and after the changes doesn't help. 



    Friday, July 22, 2016 3:39 PM


  • Hi VAISAR,

    >> Could anyone pls let me know if there is a better way to accomplish this in a single step ?

    I am afraid there is no better way. For adding RowField to specific position, you need to set Orientation and Position. If you record your manually steps, you will get below code, and you will find that when you manually add field to second position, it also achieved like you.

    Sub Macro3()
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("X")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Z")
            .Orientation = xlRowField
            .Position = 2
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Y")
            .Orientation = xlRowField
            .Position = 2
        End With
    End Sub

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 25, 2016 2:44 AM