Mass update Excel file connection string

user_1564499 0
Viewed 2164

We have recently changed our SQL Database server and i was wondering if there would be a script or an easier method of updating all the Excel file connection strings?

It would of been alot easier if they used a connection file but unfortunately they were all set manually and we have about 600 reports...

Any help is much appreciated.

Thanks

Nick

connection excel sql string
4 Answers

Based on Archlight solution, the macro is the following :

Sub UpdateConnectionsString_Click()
   For Each wks In ActiveWorkbook.Worksheets
      For Each qt In wks.QueryTables
          With qt
           'Debug.Print .Connection
           .Connection = Replace(.Connection, "bla.com", "localhost")
          End With
      Next qt
    Next wks
End Sub
user_21350450

The following PowerShell script loops through all xlsx files in the current directory. Extracts them, and then does a find and replace in the connection strings file. Adds the files back into a new archive(xlsx file) with the same name. Suffixed with '_new'

Copy paste into a *.ps1 file and run.

echo `nStarting....`n
$Zips = Get-ChildItem -filter "*.xlsx" -Recurse
$Destination = 'Extracted/'
$WinRar = "C:\Program Files (x86)\WinRAR\winrar.exe"

foreach ($zip in $Zips)
{
$des = $Destination + $zip.BaseName + "/"
&$Winrar x -Y -ibck $zip.FullName $des
Get-Process winrar | Wait-Process

}

$configFiles = Get-ChildItem Extracted\*\xl\connections.xml -recurse 
$configFiles | Set-ItemProperty -Name IsReadOnly -Value $false

foreach ($file in $configFiles)
{
    (Get-Content $file.PSPath) |
    Foreach-Object { $_ -replace "SERVER=123456;", "SERVER=7891011;" } |
    Set-Content $file.PSPath

}

$configFiles = Get-ChildItem Extracted | ? {$_.PSIsContainer}

foreach ($file in $configFiles)
{
    $FileDestination = './' + $file.Name + '_new.xlsx'
    $files = 'Extracted/' + $file.Name + '/*.*'
    &$Winrar a -r -afzip -ep1 $FileDestination $files
}

echo `nDone!`n
user_42683400
Related