Test Script for updating excel sheets in folder
$allFiles = Get-ChildItem -Path 'C:\Users\blair\Desktop\test' -Filter 'IPK_*.xlsx' -File $excl = New-Object -ComObject "Excel.Application" $excl.DisplayAlerts = $false foreach ($file in $allFiles) { $wrkb = $excl.Workbooks.Open($file.FullName) Start-Sleep -Seconds 5 $header= $ws.Cells.Item(1, 1).text $ws.Cells.Item(1,1).Value="New value" $wrkb.Save() $wrkb.Close() } $excl.Quit() $null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wrkb) $null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excl) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() [System.IO.Directory]::SetLastAccessTime($DestinationFolderPath, $NewDate) [System.IO.Directory]::SetLastWriteTime($DestinationFolderPath, $NewDate)
Leave a Comment