Excel Bulkupdate with password protection and locked cells

Hey there,

a customer decommissioned his 2003 Server and migrated his files to a new server running a current version of Windows

The issue:
– 1000’s of excel files
– The excel files contain server paths which are referencing to each other
– Some files are password protected
– different passwords used
– locked cells

As there is nothing out there, we decided to write up a script: Use at your own risk!

This script scans a directory for files containing the extension „.xls“ or „.xlsm“ and tries a list of passwords.
Excel must be installed on the system running the script.
It has been written for a german version of excel, so you may need to edit the part #Exceptionhandling in the later section of the script to your regional settings

##parameter section#
####################
$folder = "D:\fileshare\data"    

$oldname = "\\oldserver\fileshare"
$newname = "\\newserver\fileshare"


#Passwords
#keep "no password" and "errorpassword" as it is used by the script!
$arrPasswords = @("no password","secretpassword","verysecret","ultrasecret","errorpassword")


#reporting
$ScriptReportFolder = "C:\scripts\report\"
$ScriptReport =@()

############
## Main ####
############
$object = New-Object -comObject Shell.Application  

#get excel files
$excelfiles = Get-ChildItem $folder -Recurse | where {($_.Extension -eq ".xls") -or ($_.Extension -eq ".xlsm")}

if (!$folder -or !$oldname -or !$newname) {exit} 

#Excel COM Object
$excel = New-Object -comObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$progressI = 0

#loop through results
foreach ($file in $excelfiles) {
    Write-Host "--------------------------"
    Write-Host "Datei:" $file.Fullname


    #Prepare Report set
    $Report = "" | Select "File", "Result", "Date"
    $Report.File = $file.FullName
    $Report.Date = Get-Date

    #start password cracker
    Write-Host "Start password cracker"

    foreach ($password in $arrPasswords){
        #open file with excel trying all known passwords
        try {
            Write-Host "password: $password"
            $workbook = $excel.Workbooks.Open($file.FullName,0,0,5,"$password")

            if ($workbook.Name -ne ""){
                Write-Host "Workbook is opened sucessfull"
                Write-Host "Starting foreach Sheet"

                #set indicator for modified data
                $modifieddata = $false
            } 

            #replace links
            foreach ($sheet in $workbook.Sheets) { 
                 Write-Host "##Sheet:" $sheet.Name

                 #get used cells
                 $objRange = $sheet.UsedRange

                 #check if pattern was found
                 if ($objRange.Find($oldname)){
                     #set indicator for modified data
                     $modifieddata = $true

                     #check if there is protected content
                     if ($sheet.ProtectContents -eq "True"){
                        Write-Host "Protected Cells found!"

                        #save current protection
                        $sheetprotectcontents = $sheet.ProtectContents
                        $sheetprotectdrawingobjects = $sheet.ProtectDrawingObjects

                        #unprotect sheet
                        Write-Host "Unprotect Sheet"
                        $sheet.Unprotect()

                        #here we need to check each cell one by one
                        $i = 0
                        while ($cell = $objRange.Find($oldname)){
                            Write-Host "Column:" $cell.column "Row:" $cell.row

                            #save current locked status
                            $cellprotected = $cell.Locked

                            if ($cellprotected -eq $true){
                                $cell.Locked = $false
                            }
                        
                            $cell.Replace($oldname, $newname)

                            if ($cellprotected -eq $true){
                                $cell.Locked = $true
                            }
                        
                            $i++
                            if ($i -eq 999) { Write-Host "maximum retries reached"; break }
                        }#end while cell findings

                        #protect the sheet again
                        Write-Host "Protecting Sheet again"
                        $sheet.Protect($null,$sheetprotectcontents, $sheetprotectdrawingobjects)

                     } else {
                        #Use the replace function
                        Write-Host "Replacing Data by Search&Replace Function"
                        $objRange.Replace($oldname, $newname)
                     }
                } else {
                    Write-Verbose "pattern not found"
                }#end if pattern found

            }#end foreach sheet

            #work is finished, save and close the document
            if ($modifieddata -eq $true){
                Write-Host "Save file"
                $workbook.Save()
                
                $Report.Result = "Success"
            }else {
                Write-Host "skipped, pattern not found"
                $Report.Result = "skipped, pattern not found"
            }

            Write-Host "close file"
            $workbook.Close()
            #password worked, exit foreach
            break

        } catch {
            #Exceptionhandling

            #error not related to the password
            if (($($Error[0].Exception.Message)) -notlike "*Das eingegebene Kennwort ist ungültig*"){
                $Report.Result = "Failed with error $($Error[0].Exception.Message)"
                break
            }

            #error, all passwords have been tried, non worked out
            if (($password -eq "errorpassword") -and ($($Error[0].Exception.Message)) -like "*Das eingegebene Kennwort ist ungültig*"){
               $Report.Result = "Unknown Password"
               break
            }

            continue
        }

    }#end password cracker

    #send results to report array
    $ScriptReport += $Report

    #update progressbar
    $progressI++
    Write-Progress -activity "Updating Files...." -Status "Scanned: $progressI of  $($excelfiles.count)" -percentComplete (($progressI / $excelfiles.count) * 100)

}#end excel file
$excel.quit()

#save the report
$ScriptReport | ConvertTo-Csv -Delimiter ";" -NoTypeInformation | Out-File -FilePath (Join-Path "$ScriptReportFolder" "$(Get-Date -Format yyyMMddHHmm)excelupdate.csv")


[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers()