######################################################################################################################
    # manually enter these values:
$vendorName = 'CompanyX'
$saveAsName = "CompanyX Items $(get-date -f yyyy-MM-dd).xlsx"
$sheetName = 'Items'
$colorColumn = 'G'
$colorSwatchImageHyperlinkColumn = 'Y'
######################################################################################################################






    #progress bar function
$progressBar = @{
    Activity = "Processing $vendorName file"
    Status = 'Gathering images'
    CurrentOperation = ''
    PercentComplete = 0
    Id = 1
    ParentId = -1
}
Write-Progress @progressBar


    ##get images
$imageFolder = 'S:NSC914INS Vendor Data ProjectEcommerce FTP Images' + $vendorName + 'Images Consolidated'
$images = Get-ChildItem -Path $imageFolder
$progressBar.PercentComplete = 100; Write-Progress @progressBar
    ##get vendor's excel file
$excelFolder = 'S:SharedProduct Enrichment DataEmergency Image Sprint'
$vendorFile = Get-ChildItem -Path $excelFolder | Where-Object {$_.Name -like $vendorName + '*'} | Select-Object -ExpandProperty FullName


    ##start excel and open vendor file
$progressBar.Status = 'Working with Excel'; $progressBar.CurrentOperation = 'Opening Excel'; $progressBar.PercentComplete = 0; Write-Progress @progressBar
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($vendorFile)
$worksheet = $workbook.Worksheets.Item($sheetName)
$excel.Visible = $true
$rowCount = $worksheet.UsedRange.Rows.Count
$progressBar.PercentComplete = 50; Write-Progress @progressBar


$hyperlinkchunk1 = "=HYPERLINK("""
$hyperlinkchunk2 = """, """
$hyperlinkchunk3 = """)"


    ##stores column number based on user's input of column letter
$x = $worksheet.Range("${abcColorColumn}1").Column
$y = $worksheet.Range("${colorSwatchImageHyperlinkColumn}1").Column
$progressBar.PercentComplete = 100; Write-Progress @progressBar

    ##check if color matches any of the image file names
    ##if a match is found, creates a hyperlink to that image in the Colorswatchimagehyperlink column
for ($i=2; $i -le $rowCount; $i++) {
    $progressBar.CurrentOperation = "Checking line $i of $rowCount"; $progressBar.PercentComplete = ($i / $rowCount) * 100; Write-Progress @progressBar
    $color = $worksheet.Cells.Item($i,$x).Text -replace 's',''  ##Item() = (row,column)
    foreach ($image in $images) {
        if (($image.BaseName -replace 'W','') -match $color) {  ##needs to be longerString -match shorterString
            $worksheet.Cells.Item($i,$y).Value = $hyperlinkchunk1 + $image.FullName + $hyperlinkchunk2 + $image.Name + $hyperlinkchunk3
            $worksheet.Cells.Item($i,$y).Font.Underline = $false
            $worksheet.Cells.Item($i,$y).Font.ColorIndex = $xlAutomatic
        }
    }
}


    ##format, save, and quit excel; release comObjects
$progressBar.CurrentOperation = 'Saving and closing'; $progressBar.PercentComplete = 50; Write-Progress @progressBar
$worksheet.Columns($colorSwatchImageHyperlinkColumn).AutoFit()
$workbook.SaveAs($excelFolder+$saveAsName)
$workbook.Close()
$excel.Quit()
Get-Process excel | Stop-Process -Force
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)