Nutzen: Dieses Skript dient der Stapelverarbeitung von Excel-Dateien, um Formeln in geschützten Blättern zu korrigieren.

Funktion:

  Verarbeitet einen Ordner voller Excel-Dateien.

  Hebt den Schutz für ein bestimmtes Arbeitsblatt ("Erhebungsbogen") mit einem Kennwort auf.

  Korrigiert dynamisch die Zeilenbezüge in den Formeln der Zellen D24, D25, F24 und F25.

  Setzt den Blattschutz mit demselben Kennwort wieder.

  Speichert die geänderte Datei.

  Gibt am Ende eine Zusammenfassung der ausgelesenen Werte als Tabelle in der Konsole aus.

#=============== KONFIGURATION ===============
$folderPath = "C:\Users\Stefan.Agethen\OneDrive - servg.de\SpoolerZDF\EMAIL-ANHANG-ZIEL\basteln"
$worksheetNameToEdit = "Erhebungsbogen"
$password = "ZDF"
#=============================================
--- Skript-Start ---
 
[System.Console]::OutputEncoding = [System.Text.Encoding]::UTF8
$OutputEncoding = [System.Text.Encoding]::UTF8
 
if (-not (Test-Path $folderPath -PathType Container)) {
Write-Host "FEHLER: Das Verzeichnis '$folderPath' wurde nicht gefunden." -ForegroundColor Red
Start-Sleep -Seconds 10
exit
}
 
$results = @()
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
 
$excelFiles = Get-ChildItem -Path folderPath−Filter"∗.xlsx"Write−Host"($excelFiles.Count) Dateien gefunden. Beginne Verarbeitung..."
 
try {
foreach ($file in $excelFiles) {
$workbook = $null
Write-Host ("-"*80)
Write-Host "Verarbeite Datei: $($file.Name)" -ForegroundColor Cyan
try {
$workbook = $excel.Workbooks.Open($file.FullName)
$worksheetEdit = $workbook.Worksheets($worksheetNameToEdit)
if ($null -eq $worksheetEdit) { Throw "Arbeitsblatt '$worksheetNameToEdit' nicht gefunden." }
 
        Write-Host "-> Hebe Schutz für '$($worksheetEdit.Name)' auf..."
        $worksheetEdit.Unprotect($password)
 
        Write-Host "-> Korrigiere Formeln in D24, D25, F24, F25..."
        foreach ($cellAddress in @("D24", "D25", "F24", "F25")) {
            $cell = $worksheetEdit.Range($cellAddress)
            $targetRow = $cell.Row
            $originalFormula = $cell.Formula
            $correctedFormula = $originalFormula -replace '(\$A)\d+', ('${1}' + $targetRow)
            $correctedFormula = $correctedFormula -replace '(\$I)\d+', ('${1}' + $targetRow)
            $cell.Formula = $correctedFormula
        }
        Write-Host "[OK] Korrekturen in D/F durchgeführt."
 
        $valF24 = "{0:P2}" -f $worksheetEdit.Range("F24").Value2
        $valF25 = "{0:P2}" -f $worksheetEdit.Range("F25").Value2
        $valF36 = "{0:P2}" -f $worksheetEdit.Range("F36").Value2
 
        $results += [PSCustomObject]@{
            Dateiname = $file.Name; F24 = $valF24; F25 = $valF25; F36 = $valF36
        }
 
        Write-Host "-> Aktiviere Schutz für '$($worksheetEdit.Name)'..."
        $worksheetEdit.Protect($password)
 
        Write-Host "-> Speichere und schließe Datei..."
        $workbook.Close($true)
 
    } catch {
        Write-Host "[FEHLER] bei Datei '$($file.Name)': $($_.Exception.Message)" -ForegroundColor Red
        if ($workbook -ne $null) { $workbook.Close($false) }
    }
}
 
}
finally {
if ($excel) { $excel.Quit() }
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
Get-Process "EXCEL" -ErrorAction SilentlyContinue | Stop-Process -Force -ErrorAction SilentlyContinue
Write-Host ("-"*80)
Write-Host "Verarbeitung abgeschlossen."
}
if ($results.Count -gt 0) { Write-Host ("-"*80) Write-Host "ERGEBNIS-ZUSAMMENFASSUNG" -ForegroundColor Green $results | Format-Table -AutoSize }
 
//Author: Stefan Agethen //Letzte Bearbeitung: 2025//