Problem
ExcelWriter fully preserves existing pivot tables however the .NET implementation of the ExcelApplication object does not offer any support for manipulating the pivot tables. Since there is no way to programmatically hide pivot table rows with the ExcelApplication object this must be done in VBA.
Solution
In order to hide rows in your pivot table, you can include the following VBA which will execute when the user opens the spreadsheet in Excel.
Due to the sequence in which events are fired it is necessary to explicitly refresh the data in a pivot table in the Workbook_Open event instead of relying on the “Refresh on Open” Option in the pivot table options.
Disabling the option in Excel and refreshing the data in Workbook_Open() guarantees that the data has been refreshed after which you can the hide rows:
Private Sub Workbook_Open()
Worksheets(1).PivotTables("Pivot1").PivotCache.Refresh
Worksheets(1).PivotTables("Pivot1").PivotFields("Field1").PivotItems("Row1").Visible = False Worksheets(1).PivotTables("Pivot1").PivotFields("Field1").PivotItems("Row2").Visible = False
End Sub
Follow