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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
##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() |