Quick Support

An einem Meeting teilnehmen:

Mauricio Schäpers

An einer Fernwartung teilnehmen:

VS Qloud Support


Für den Betritt der Fernunterstützung wird ein Passwort von Agenten festgelegt.
Dieses wird Ihnen telefonisch oder per Email bekannt gegeben.


Kunden Login

Partner Login

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

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

*

Suche

Cookies erleichtern die Bereitstellung unserer Dienste. Mit der Nutzung unserer Dienste erklären Sie sich damit einverstanden, dass wir Cookies verwenden. weitere Informationen

Auf einigen unserer Internetseiten kommen Cookies zum Einsatz. Cookies sind kleine Textdateien, die auf Ihrem Computer gespeichert werden, wenn Sie unsere Internetseite besuchen. Dadurch können wir unsere Website für den Anwender individueller gestalten. Die Daten, die ein Cookie verwendet, wie zum Beispiel ein Zugangskennwort, werden auf Ihrem Computer gespeichert. Wenn Sie die Verwendung von Cookies nicht wünschen, ändern Sie bitte in Ihrem Internet-Browser die Einstellungen zur Cookie-Verwaltung. Bitte beachten Sie, dass einzelne Funktionen unserer Website möglicherweise nicht funktionieren, wenn Sie die Verwendung von Cookies deaktiviert haben.

Schließen