collapse rows in excel pivot table - Nprinting

[#1]

I have created an excel pivot chart in nprinting reports. After refresh the chart is always expanded. Is a way to get it collapsed after refresh?

[Daniel_Jenkins · Accepted solution]

Hi Christiana,

You will need to use XLSM format and write VBA code in the Workbook Open event.

Taken from the Training Materials: NPrinting 16 Training & Installation/Upgrade Information

If you have a Pivot Table in your template like this:

You can use this code to collapse the field <Country>:

Private Sub Workbook_Open()

        Sheets("Excel Pivot Table").Select

        ActiveSheet.PivotTables("PivotTable1").PivotSelect "Country[All]", xlLabelOnly _

        + xlFirstRow, True

        ActiveSheet.PivotTables("PivotTable1").PivotFields("Country").ShowDetail = _

        False

        Range("B2").Select

End Sub

Remember to save and preview as XLSM.

HTH - Daniel.

[Anonymous]

Hi Daniel,

Is this aplicable for NPrinting 17.2 as well?

Thanks

[Daniel_Jenkins]

Hi Ladislav,

In NPrinting 17.2 you cannot set the output format to XLSM. Therefore not applicable.

- Daniel.

[stig1984]

For reference the xlsm templates are back in place for sept 2018 onwards