I am trying to modify the below script to be able to search for multiple Excel files that contain macros. The issue I'm having is being able to access password protected files. It is prompting to enter a password for each password protected file. I would like to skip it and just process the unprotected ones. Is there a way or script that I could use to output which files are password protected and just process the unprotected ones?
Here is the script I'm currently using:
Get-ChildItem -path "server path" -recurse | where {$_.extension -match "^\.xls(m|)$"} | ForEach-
Object -Begin {
$thisThread = [System.Threading.Thread]::CurrentThread
$originalCulture = $thisThread.CurrentCulture
$thisThread.CurrentCulture = New-Object System.Globalization.CultureInfo('en-US')
$excel = new-object -comobject excel.application
Try{
$excel.Workbooks.open($path, 0, 0, 5, $password)}
Catch{
Write-Host 'Book is password protected.'}
} -Process {
$workbook = $excel.workbooks.Open($_.FullName)
$_.FullName + " : " + $workbook.HasVBProject
$workbook.Close($false)
} -end {
$excel.Quit()
$thisThread.CurrentCulture = $originalCulture
}| out-file c:\results.csv
Here is the errors:
Book is password protected.
Unable to get the Open property of the Workbooks class
At line:11 char:5
+ $workbook = $excel.workbooks.Open($_.FullName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))
At line:13 char:4
+ $workbook.Close($false)
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
Unable to get the Open property of the Workbooks class
At line:11 char:5
+ $workbook = $excel.workbooks.Open($_.FullName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))
At line:13 char:4
+ $workbook.Close($false)
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
User contributions licensed under CC BY-SA 3.0