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

    # 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)
    }

    # Loop through each worksheet in the workbook
    $BrokenLinks = @()
    $OldPath = ""

    foreach ($WorkSheet in $WorkBook.Worksheets) {
        $UsedRange = $WorkSheet.UsedRange
        $Rows = $UsedRange.Rows.Count
        $Columns = $UsedRange.Columns.Count

        # Loop through each cell in the used range of the worksheet
        for ($Row = 1; $Row -le $Rows; $Row++) {
            for ($Column = 1; $Column -le $Columns; $Column++) {
                $Cell = $WorkSheet.Cells.Item($Row, $Column)

                # Check if the current cell contains a VLOOKUP formula
                if ($Cell.HasFormula -and $Cell.Formula.Contains("VLOOKUP")) {
                    try {
                        $OldFilePath = $Cell.Formula | Select-String -Pattern "'.*?'" -AllMatches | % { $_.Matches } | % { $_.Value }
                        $OldFilePath = $OldFilePath -replace "'", ""

                        # Check if the file path stored in $OldFilePath is valid
                        if (!(Test-Path $OldFilePath)) {
                            if ($OldPath -ne $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:"

                                # Store the current broken link in $OldPath to prevent duplicate prompts for the same broken link
                                $OldPath = $OldFilePath
                            }
                            # Replace the old broken link in the VLOOKUP formula with the new path entered by the user
                            $UpdatedFormula = $Cell.Formula -replace ([regex]::Escape($OldFilePath)), $NewPath
                            $Cell.Formula = $UpdatedFormula
                        }
                    } 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
                    }
                }
            }
        }
    }
    $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