PowerShell to update and refresh Excel data connections

Scenario:
You have multiple Excel files that applies the same template and formatting, but uses different data sources.
*** My requirement was to replace oData connections. I needed a simple script that would open an Excel file, loop through the data connections, and replace the data connection string.

Tested on Excel 2013, run PowerShell 64bit using Windows 7 Pro.

 $libraryPath = "D:\PowerShell\"  
 # To view Excel file open need to set below property to True  
 #$excel.visible = $true  
 $excel = new-object -comobject Excel.Application  
 # Give delay to open  
 Start-Sleep -s 5  
 $allExcelfiles = Get-ChildItem $libraryPath -recurse -include “*.xls*”  
 $newString = "Test ABC"  
 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."  
                }  
           }  
           # This will Refresh All the pivot tables data.  
           $excelworkbook.RefreshAll()  
           # The following script lines will Save the file.  
           $excelworkbook.Save()  
           $excelworkbook.Close()  
      }  
 }  
 $excel.quit()