Excel Bulkupdate with password protection and locked cells

19. Juni 2017
Allgemein

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&amp;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()

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()

Quick Support

Aktuell sind folgende Stellen zu vergeben:

Schreibe einen Kommentar Kommentar Antwort löschen