powershell脚本超时

Question

我在Powershell中有一个脚本，可以通过连接SQL数据库来更新Excel文件。脚本运行良好，但问题是如果一个连接不起作用，脚本就无法继续。有没有办法放一个暂停之类的东西，这样在一个诡计之后可以继续？这是我的脚本：

$libraryPath = "C:tempExcel"  
$excel = new-object -comobject Excel.Application  
# Give delay to open  
Start-Sleep -s 5  
$allExcelfiles = Get-ChildItem $libraryPath -recurse -include “*.xlsx”  
foreach ($file in $allExcelfiles)  
{  
$workbookpath = $file.fullname  
Write-Host "Updating " $workbookpath  
# Open the Excel file  
$excelworkbook = $excel.workbooks.Open($workbookpath)  
$connections = $excelworkbook.Connections  
foreach ($c in $connections)  
{  
if ($c.DataFeedConnection -ne $null)  
{  
$conn = $c.DataFeedConnection.Connection  
# Use regex to search and replace part of connection string  
$new = $conn -replace 'ProjectName eq ''(.*)''', "ProjectName eq '$title'"  
$c.DataFeedConnection.Connection = $new  
Write-Host "Connection replaced."  
}  
} 
Start-Sleep -s 5 
# This will Refresh All the pivot tables data.  
$excelworkbook.RefreshAll()  
# The following script lines will Save the file. 
Start-Sleep -s 50 
$excelworkbook.Save()  
$excelworkbook.Close()  
}  
$excel.quit()

感谢

Answer 1

您可以将脚本作为作业运行，如果作业超过给定期限，则终止它。

$timeout_in_sec = 10
$excel_update = {
$libraryPath = "C:tempExcel"  
$excel = new-object -comobject Excel.Application  
# Give delay to open  
Start-Sleep -s 5  
$allExcelfiles = Get-ChildItem $libraryPath -recurse -include “*.xlsx”  
foreach ($file in $allExcelfiles)  
{  
$workbookpath = $file.fullname  
Write-Host "Updating " $workbookpath  
# Open the Excel file  
$excelworkbook = $excel.workbooks.Open($workbookpath)  
$connections = $excelworkbook.Connections  
foreach ($c in $connections)  
{  
if ($c.DataFeedConnection -ne $null)  
{  
$conn = $c.DataFeedConnection.Connection  
# Use regex to search and replace part of connection string  
$new = $conn -replace 'ProjectName eq ''(.*)''', "ProjectName eq '$title'"  
$c.DataFeedConnection.Connection = $new  
Write-Host "Connection replaced."  
}  
} 
Start-Sleep -s 5 
# This will Refresh All the pivot tables data.  
$excelworkbook.RefreshAll()  
# The following script lines will Save the file. 
Start-Sleep -s 50 
$excelworkbook.Save()  
$excelworkbook.Close()  
}  
$excel.quit()
}
$job = Start-Job -Name 'thing' -ScriptBlock $excel_update
Wait-Job -Timeout $timeout_in_sec -Job $job
if ($job.State -eq 'Running') { Stop-Job -Job $job }
Remove-Job -Job $job

将$timeout_in_sec更改为您需要的任何套件。