Update multiple connection strings in Excel using PowerShell

Question

I currently have a PS script that refreshes Excel files that have 1 data connection and it works perfectly. The issue is that I've built other Excel files that have 3 data connections. When I try to use the below code for the files with 3 data connection strings, the data gets processed, but only one of the data connections gets updated. Can anyone tell me what I need to do to get all data connections updated? I tried repeating the "refresh all"/"Save" part of the code, but that gave me error messages. Any help would be appreciated.

$excel = new-object -comobject excel.application
$excel.DisplayAlerts = $false
$excelFiles = Get-ChildItem -Path "File Folder Location (ex. C:\Documents)" -Include *.xls, *.xlsm,*.xlsx, *.lnk -Recurse
Foreach($file in $excelFiles)
{
$workbook = $excel.workbooks.open($file.fullname)
$worksheet = $workbook.worksheets.item(1)
$workBook.RefreshAll()
$workbook.save()
$workbook.close()
}
$excel.quit()

Answer 1

Depending on your connections, one the methods might help you (untested)

$excel = new-object -comobject excel.application
$excel.DisplayAlerts = $false
$excelFiles = Get-ChildItem -Path "$($env:userprofile)\Documents)" -Include *.xls, *.xlsm,*.xlsx, *.lnk -Recurse
Foreach($file in $excelFiles) {
    $workbook = $excel.workbooks.open($file.fullname)
    # ---- this method ----
    foreach ($Conn in $workbook.Connections){
        $Conn.OLEDBConnection.BackgroundQuery = $false
        $Conn.refresh()     
    }
    # ---- and/or this method ----
    foreach ($Sheet in $workbook.Worksheets) {
        foreach ($QTable in $Sheet.QueryTables) {
            $QTable.BackgroundQuery = $false
        }
    }
    # ----- might get you further, depneding on your connections ----
    $workBook.RefreshAll()
    $workbook.save()
    $workbook.close()
}
$excel.quit()