Remove known Excel passwords with PowerShell

Question

I have this PowerShell code that loops through Excel files in a specified directory; references a list of known passwords to find the correct one; and then opens, decrypts, and saves that file to a new directory.

But it's not executing as quickly as I'd like (it's part of a larger ETL process and it's a bottleneck). At this point I can remove the passwords faster manually as the script takes ~40 minutes to decrypt 40 workbooks while referencing a list of ~50 passwords.

Is there a cmdlet or function (or something) that's missing which would speed this up, an overlooked flaw in the processing, or is PowerShell, perhaps, just not the right tool for this job?

Original Code (updated code can be found below):

$ErrorActionPreference = "SilentlyContinue"

CLS

# Paths
$encrypted_path = "C:\PoShTest\Encrypted\"
$decrypted_Path = "C:\PoShTest\Decrypted\"
$original_Path =  "C:\PoShTest\Originals\"
$password_Path =  "C:\PoShTest\Passwords\Passwords.txt"

# Load Password Cache
$arrPasswords = Get-Content -Path $password_Path

# Load File List
$arrFiles = Get-ChildItem $encrypted_path

# Create counter to display progress
[int] $count = ($arrfiles.count -1)

# Loop through each file
$arrFiles| % {
    $file  = get-item -path $_.fullname
    # Display current file
    write-host "Processing" $file.name -f "DarkYellow"
    write-host "Items remaining: " $count `n

    # Excel xlsx
    if ($file.Extension -eq ".xlsx") {

    # Loop through password cache
        $arrPasswords | % {
            $passwd = $_

            # New Excel Object
            $ExcelObj = $null
            $ExcelObj = New-Object -ComObject Excel.Application
            $ExcelObj.Visible = $false

            # Attempt to open file
            $Workbook = $ExcelObj.Workbooks.Open($file.fullname,1,$false,5,$passwd)
            $Workbook.Activate()

            # if password is correct - Save new file without password to $decrypted_Path
                if ($Workbook.Worksheets.count -ne 0) {
                    $Workbook.Password=$null
                    $savePath = $decrypted_Path+$file.Name
                    write-host "Decrypted: " $file.Name -f "DarkGreen"
                    $Workbook.SaveAs($savePath)
            # Close document and Application
                    $ExcelObj.Workbooks.close()
                    $ExcelObj.Application.Quit()

            # Move original file to $original_Path
                    move-item $file.fullname -Destination $original_Path -Force
                }
                else {
            # Close document and Application
                    write-host "PASSWORD NOT FOUND: " $file.name -f "Magenta"
                    $ExcelObj.Close()
                    $ExcelObj.Application.Quit()
                }
        }

    }

$count--
# Next File
}

Write-host "`n Processing Complete" -f "Green"

Updated code:

# Get Current EXCEL Process ID's so they are not affected but the scripts cleanup
# SilentlyContinue in case there are no active Excels
$currentExcelProcessIDs = (Get-Process excel -ErrorAction SilentlyContinue).Id

$a = Get-Date

$ErrorActionPreference = "SilentlyContinue"

CLS

# Paths
$encrypted_path = "C:\PoShTest\Encrypted"
$decrypted_Path = "C:\PoShTest\Decrypted\"
$processed_Path = "C:\PoShTest\Processed\"
$password_Path  = "C:\PoShTest\Passwords\Passwords.txt"

# Load Password Cache
$arrPasswords = Get-Content -Path $password_Path

# Load File List
$arrFiles = Get-ChildItem $encrypted_path

# Create counter to display progress
[int] $count = ($arrfiles.count -1)

# New Excel Object
$ExcelObj = $null
$ExcelObj = New-Object -ComObject Excel.Application
$ExcelObj.Visible = $false

# Loop through each file
$arrFiles| % {
    $file  = get-item -path $_.fullname
    # Display current file
    write-host "`n Processing" $file.name -f "DarkYellow"
    write-host "`n Items remaining: " $count `n

    # Excel xlsx
    if ($file.Extension -like "*.xls*") {

    # Loop through password cache
        $arrPasswords | % {
            $passwd = $_

            # Attempt to open file
            $Workbook = $ExcelObj.Workbooks.Open($file.fullname,1,$false,5,$passwd)
            $Workbook.Activate()

            # if password is correct, remove $passwd from array and save new file without password to $decrypted_Path
                if ($Workbook.Worksheets.count -ne 0) 

                {   
                    $Workbook.Password=$null
                    $savePath = $decrypted_Path+$file.Name
                    write-host "Decrypted: " $file.Name -f "DarkGreen"
                    $Workbook.SaveAs($savePath)

             # Added to keep Excel process memory utilization in check
                    $ExcelObj.Workbooks.close()

             # Move original file to $processed_Path
                    move-item $file.fullname -Destination $processed_Path -Force

                }
                else {
            # Close Document
                    $ExcelObj.Workbooks.Close()
                }
        }

    }



$count--
# Next File
}
# Close Document and Application
    $ExcelObj.Workbooks.close()
    $ExcelObj.Application.Quit()

Write-host "`nProcessing Complete!" -f "Green"
Write-host "`nFiles w/o a matching password can be found in the Encrypted folder."
Write-host "`nTime Started   : " $a.ToShortTimeString()
Write-host "Time Completed : " $(Get-Date).ToShortTimeString()
Write-host "`nTotal Duration : " 
NEW-TIMESPAN –Start $a –End $(Get-Date)

# Remove any stale Excel processes created by this script's execution
Get-Process excel -ErrorAction SilentlyContinue | Where-Object{$currentExcelProcessIDs -notcontains $_.id} | Stop-Process

Matt · Accepted Answer

If nothing else I do see one glaring performance issue that should be easy to address. You are opening a new excel instance for testing each individual password for each document. 40 workbooks with 50 passwords mean you have opened 2000 Excel instances one at a time.

You should be able to keep using the same one without a functionality hit. Get this code out of your inner most loop

# New Excel Object
$ExcelObj = $null
$ExcelObj = New-Object -ComObject Excel.Application
$ExcelObj.Visible = $false

as well as the snippet that would close the process. It would need to be out of the loop as well.

$ExcelObj.Close()
$ExcelObj.Application.Quit()

If that does not help enough you would have to consider doing some sort of parallel processing with jobs etc. I have a basic solution in a CodeReview.SE answer of mine doing something similar.

Basically what it does is run several excels at once where each one works on a chunk of documents which runs faster than one Excel doing them all. Just like I do in the linked answer I caution the automation of Excel COM with PowerShell. COM objects don't always get released properly and locks can be left on files or processes.

You are looping for all 50 passwords regardless of success or not. That means you could find the right password on the first go but you are still going to try the other 49! Set a flag in the loop to break that inner loop when that happens.

As far as the password logic goes you say that

At this point I can remove the passwords faster manually since the script takes ~40 minutes

Why can you do it faster? What do you know that the script does not. I don't see you being able to out perform the script but doing exactly what it does.

With what I see another suggestion would be to keep/track successful passwords and associated file name. So that way when it gets processed again you would know the first password to try.

Remove known Excel passwords with PowerShell

Answers (2)

Related Questions