Make the excel sheet unprotected before creating Pivot table

PowerShell Help
1

Hi All,

I’m trying to make the pivot table by using power shell module Import Excel. I have mentioned the script below for that. And my script is working fine. But when I’m opening the file its saying "Can’t Edit Pivot table on protected sheet". Any idea how to resolve this error.

Thanks in advance :smiling_face:

Write-Host "Creating Pivot table" -ForegroundColor Yellow
$filterCriteria1 = @("Azure AD joined", "Hybrid Azure AD joined") 
$data = Import-Excel -Path $intuneFile 
$filteredData = $data| Where-Object { $_."Intune_vs_AD" -in $filterCriteria1}
$p = @{Show = $false; AutoSize = $true; AutoFilter = $true; IncludePivotTable = $true} 
$p.WorksheetName = 'In SEP' 
$p.PivotRows = 'Intune_vs_SEPM' 
$p.PivotData = 'Intune_vs_SEPM' 
$filteredData | Export-Excel -Path "$CurrentDir\Pivottable.xlsx" @p
2

Use the -PassThru parameter to continue working with the Excel package, you can then set the worksheet protection:

$Excel = $filteredData | Export-Excel -Path "$CurrentDir\Pivottable.xlsx" @p -PassThru
$Sheet = $Excel.Workbook.Worksheets['In SEPPivotTable']
Set-WorksheetProtection -Worksheet $Sheet -IsProtected:$false
$Excel | Close-ExcelPackage
3

Hi @matt-bloomfield ,
Thanks for you reply, i tried your suggestions but the error is still persistent. It didn’t work.

4

It worked for me :slight_smile:

Is the sheet password protected?

5

This script was working locally for me also. But when I’m running this in the user(for whom I’m developing) environment. I’m getting this error.

And First this file is not created before the script execution. The script is creating the file itself. And come to your answer NO the sheet is not password protected.

6

Ok, I’m not sure what’s happening then. The only way I could replicate it was to modify an existing workbook that had a protected sheet.

Have you tried using Add-PivotTable instead of using the -IncludePivotTable parameter?

7

no I didn’t try that method. I also don’t know what’s the problem here.