timeout to powershell script

Question

I've a script in Powershell that update Excel files with connections to SQL databases. The script works fine but the problem is if one connection doesn't work the script can't continue. Is there a way to put like a timeout or something so that can continue after a wile? Here is the script that I have:

$libraryPath = "C:\temp\Excel\"  
$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()

Thanks

Answer 1

You could run the your script as a job, and if the job exceeds a given period terminate it.

$timeout_in_sec = 10
$excel_update = {
    $libraryPath = "C:\temp\Excel\"  
    $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

Change $timeout_in_sec to whatever suites your needs.