Powershell excel(xlsx) how unprotect excel sheet and excel workbooks with know password-CodePudding

I have below code:

$excelfile="C:\Users\Administrator\Pictures\unprotect_org - Copy (2)\unprotect - Copy (2).xlsx"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$wb = $excel.Workbooks.Open($excelfile,$false,123)
$wb.Unprotect(123);
$wb.Settings.Password = "";
$wb.Save($excelfile);
$excel.Quit()

I have problem with, PS script is opening ui excel application instead of remove password as without open excel.

Getting below error:

Unable to get the Open property of the Workbooks class
At line:1 char:1
  $wb = $excel.Workbooks.Open($excelfile,$false,123)
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      CategoryInfo          : OperationStopped: (:) [], COMException
      FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Please help to unprotect excel sheets and workbook using powershell.

CodePudding user response：

Ok, here you go:

To open an Excel workbook with a password, you need to specify that password as the fith parameter on the Workbooks.Open() method:

$password = '123'
$excelfile = "C:\Users\Administrator\Pictures\unprotect_org - Copy (2)\unprotect - Copy (2).xlsx"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$wb = $excel.Workbooks.Open($excelfile, $false, $false, [Type]::Missing, $password)
# remove the protection from this workbook
$wb.Unprotect($password)
# should not be needed, but does no harm
$wb.Password=$null
# close the workbook and save the changes
$wb.Close($true)
$excel.Quit()
# Important: remove the used COM objects from memory
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()