Test Script for updating excel sheets in folder

 avatar
unknown
powershell
a year ago
786 B
4
Indexable
$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