replace_connection_source_in_excel_files.ps1

$Excel = New-Object -comobject Excel.Application

# Parameter for old and new datasource
$OldDatasource = "."
$NewDatasource = "Someserver"

# Get all Excel files in current working directory and subfolders
$Dir = Get-Location
$List = Get-ChildItem -Path $Dir -Include *.xlsx -Recurse

# Loop through each Excel files
Foreach ($Workbook in $List)
{
    # Open workbook and get connections
    $ExcelWorkbook = $Excel.workbooks.open($Workbook)
    $Connections =  $ExcelWorkbook.Connections

    # Loop through each connection
    foreach ($C in $Connections)
    {
        if ($C.OLEDBConnection -ne $null)
        {
            # Get connection and replace old datasource with new
            $Conn = $C.OLEDBConnection.Connection
            $New = $Conn -replace "Data Source=$OldDatasource", "Data Source=$NewDatasource"
            $C.OLEDBConnection.Connection = $New
        }
    }

    # Clean up
    $ExcelWorkbook.Save()
    $ExcelWorkbook.Close()
}