Ytdyklly

I'm rather new to PowerShell so I could use some help with why my script is running so slow. It starts off just fine, and even the first 30 or so iterations of the for loop are zippy. But by the time my for loop gets to around 75 iterations, it has slowed down to ~20 seconds per iteration. Do I need to do some garbage collection? Any insight is much appreciated!

Since you obviously don't have the files I'm working with, here are some details:

• my excel file is 342 lines


• my image folder contains 50 images


• an example of the matching I'm doing would be color: WEATHERED BLEND image: NovikShake_HS_WeatheredBlend.jpg

######################################################################################################################

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