Mass changing pivot table connection strings in Excel using PowerShell

Excel / Powershell

The Problem

As Business Intelligence consultants after a migration to a brand new server inevitably we get asked by the users: “Do why have to redo all my pivot tables in Excel again? All my workbooks stopped working…”

Well, they can but they don’t need to. Where’s a simple code to replace the part that matters. This will iterate all the Excel files in the folder where the Powershell Script is located and and apply the substitution to all the OLEDBConnections that exist in each file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

param (
    $find = "OLD",
    $replace = "NEW"
)

Set-Location (split-path -parent $MyInvocation.MyCommand.Definition)

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
$excelApp = New-Object Microsoft.Office.Interop.Excel.ApplicationClass

# Enum all Excel Files
Get-ChildItem "*.xlsx" | ForEach-Object {
    $workbook = $excelApp.Workbooks.Open($_.FullName)

    # fix all connections in workbook
    foreach ($con in $connections.Connections) {
        if ($con.OLEDBConnection.Connection -match $find) {
            Write-Host "Match found on $($_.FullName) - $($con.OLEDBConnection.Connection)"
            $con.OLEDBConnection.Connection = $con.OLEDBConnection.Connection -replace "$find", "$replace"
        }
    }

    # Refresh all pivots
    $workbook.RefreshAll()

    # clean objects
    $workbook.Save()
    $workbook.Close()
    [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)
}
# close excel
$excelApp.Quit()
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($excelApp)

Conclusion

Once again Powershell came to the rescue and saved my clients a few hours of work replacing manually the connections to the data source.

Have fun!

 Share!