powershell - fix lookups in excel files

############################
#Improved version of a previous PS script. This turns off calculations and uses link manager to look at links
#FInds broken Vlookup links in excels files
#Makes backup of files before editing
#Prompts user for correct directory - Note needs full directory path of file, ex c:\temp\tester.xlsx
#Goes through and updates all cells that are referencing the same document
#Will prompt again if detects a different source
#
#
#
#THE SCRIPT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
#FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
#WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SCRIPT OR THE USE OR OTHER DEALINGS IN THE SCRIPT.
#USE AT YOUR OWN RISK
############################




# This function creates a log file at the current location if it doesn't already exist
# and returns the path to the log file
function Create-LogFile {
    $LogFilePath = (Get-Location).Path + "\BrokenLinksLog.txt"
    if (!(Test-Path $LogFilePath)) {
        New-Item -Path $LogFilePath -ItemType "File" -Force
    }
    return $LogFilePath
}

# This function writes a message to the specified log file with a timestamp
function Write-Log {
    param (
        [string]$Message,
        [string]$LogFilePath
    )
    $Timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $Message = $Timestamp + " " + $Message
    Add-Content -Path $LogFilePath -Value $Message
}

# Create a new instance of Excel and hide it from the user
$Excel = New-Object -ComObject Excel.Application -Property @{Visible = $false; DisplayAlerts = $false}

# Prompt the user to enter the path of the directory where the Excel files are located
$DirectoryPath = Read-Host "Please enter the path of the directory where the excel files are located"

# Prompt the user to choose whether to create a backup of the original files before making changes
$CreateBackup = Read-Host "Do you want to create a backup of the original files before making changes? (y/n)"

# Create a log file
$LogFilePath = Create-LogFile

# Get a list of all Excel files in the specified directory and its subdirectories
$Files = Get-ChildItem -Path $DirectoryPath -Include *.xls, *.xlsx -Recurse


# Loop through each file and check for broken links
foreach ($File in $Files) {
    Write-Host "Processing file: $($File.FullName)"
    Write-Log -Message "Processing file: $($File.FullName)" -LogFilePath $LogFilePath
    $WorkBook = $Excel.Workbooks.Open($File.FullName)

    # Set calculation to manual to improve performance
    $Excel.Calculation = -4135 # xlCalculationManual


    # If the user chose to create a backup, save a copy of the original file
    if ($CreateBackup -eq "y" -or $CreateBackup -eq "Y") {
        $BackupName = $File.DirectoryName + "\Backup_" + $File.Name
        $WorkBook.SaveCopyAs($BackupName)
    }

    try {
        # Set calculation to manual to improve performance
        $Excel.Calculation = -4135 # xlCalculationManual

        # Replace the previous loop through each worksheet with the new code
        $LinkSources = $WorkBook.LinkSources(1) # xlExcelLinks
        if ($LinkSources -ne $null) {
            foreach ($OldFilePath in $LinkSources) {
                # Check if the file path stored in $OldFilePath is valid
                if (!(Test-Path $OldFilePath)) {
                    # If it is a new broken link, write a message to the console and the log file
                    Write-Host "Path not found: $($OldFilePath)"
                    Write-Log -Message "Path not found: $($OldFilePath)" -LogFilePath $LogFilePath

                    # Prompt the user to enter the correct path for the broken link
                    $NewPath = Read-Host "The link $($OldFilePath) in the file $($File.FullName) is broken. Please enter the correct path:"

                    # Update the broken link with the new path entered by the user
                    $WorkBook.ChangeLink($OldFilePath, $NewPath, 1) # xlLinkTypeExcelLinks
                }
            }
        }
    } catch {
        # Catch any exceptions that occur during the processing of the current cell and write them to the console and the log file
        Write-Host "Error processing $($File.FullName): $($_.Exception.Message)"
        Write-Log -Message "Error processing $($File.FullName): $($_.Exception.Message)" -LogFilePath $LogFilePath
    } finally {
        # Set calculation back to automatic after processing
        $Excel.Calculation = -4105 # xlCalculationAutomatic
        $WorkBook.Save()
        $WorkBook.Close()
    }
}

$Excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Write-Host "Script execution completed."
Write-Log -Message "Script execution completed." -LogFilePath $LogFilePath