Output which Excel files are password protected

0

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
vba
powershell
export-to-excel
xlsm
asked on Stack Overflow Feb 1, 2021 by BPulido • edited Feb 2, 2021 by Pᴇʜ

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0