Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save bayees/46698756693535781db1c30fa295efbc to your computer and use it in GitHub Desktop.
Save bayees/46698756693535781db1c30fa295efbc to your computer and use it in GitHub Desktop.
$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()
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment