Using Add-PivotTable and then adding more worksheets

[DavidYacouel]

Hi Doug,

Is there any issue with using Add-PivotTable, closing the Excel package, and then adding more worksheets?

This is the fragment I use to create a couple of pivot tables. The issue is the same with just one pivot table.
I only added the Add-Worksheet here in order to help position the pivot tables before the 'Statistics' tab which I want to be last (I know that Add-PivotTable uses Add-Worksheet internally).

# create a fourth tab - user count by Department
write-host 'Creating Excel file with tab #4 - Users by Dept'
$excel = Open-ExcelPackage -Path $filepath
Add-Worksheet -ExcelPackage $excel -WorksheetName 'Users by Dept' -MoveBefore 'Statistics'
Add-Worksheet -ExcelPackage $excel -WorksheetName 'Users by City' -MoveBefore 'Statistics'
Add-PivotTable -ExcelPackage $excel -PivotTableName 'Users by Dept' -SourceWorksheet $excel.Workbook.Worksheets[1] -PivotRows 'Department' -PivotColumns 'City' -PivotData 'UserPrincipalName' -Activate # -NoTotalsInPivot
Add-PivotTable -ExcelPackage $excel -PivotTableName 'Users by City' -SourceWorksheet $excel.Workbook.Worksheets[1] -PivotRows 'City'                            -PivotData 'UserPrincipalName' -Activate # -NoTotalsInPivot
Close-ExcelPackage -ExcelPackage $excel

When I use this code BEFORE creating the Statistics tab (and without using the Add-Worksheet cause I create Statistics with -MoveToEnd ), and then I create the last worksheet 'Statistics' (sample code below), then the resulting Excel file is corrupt and when I open it I receive the following message.

# create the last tab - Statistics
write-host 'Creating Excel file with tab #5 - Statistics'
$excel = Open-ExcelPackage -Path $filepath
Add-Worksheet -ExcelPackage $excel -WorksheetName 'Statistics' -MoveToEnd
$SummaryReport | Export-Excel -ExcelPackage $excel -WorksheetName 'Statistics' -TableStyle Medium1 -BoldTopRow -AutoSize -Numberformat '@'

I do make sure I use -Activate with Add-PivotTable and then I Close-ExcelPackage.

Also, when I debug the code when creating the pivot table before Statistics, and I stop running before creating the last tab (Statistics), then the file is healthy and readable. It's the adding of the additional tab that 'causes' the corruption. Well, I am guessing it's not the adding of the last tab, but something in Add-PivotTable leaves the workbook in a non healthy way.

OR, I am doing something wrong, in which case I'd love to find what it is.

I reviewed the code for Add-PivotTable and I can't find a reason for this either, but maybe I am missing it. I do admit that I don't understand the need for -Activate parameter, but I didn't dig in the Add-Worksheet code for it.

I can provide a full script sample for this.

Thank you,
David Y.

[dfinke]

Thanks @DavidYacouel, any way you can make a small repro? -Activate makes the pivot table sheet the active sheet.

[DavidYacouel]

yes, @dfinke , I will put something simple together, thanks.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.