Mass changing power query connection strings in Excel using PowerShell

PowerShell / Excel

A while ago I’ve received an email from Jason Alvarez asking me if I knew a way to change Power Query connections inside an Excel file. The problem is similar to my previous post on Mass changing pivot table connection strings in Excel using PowerShell. Turns out you can and he was able to find this solution:

 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

#root path to the collection of excel docs:
$rootPath = "C:\Temp\PowerQueryDemo\"

#Current query file path:
$currentPath = "C:\Temp\PowerQueryDemo\"
#Updated query file path:
$newPath = "C:\Temp\PowerQueryDemo\PowerQueryNewSource"

Get-ChildItem -Path $rootPath -Recurse -Include *.xlsx | ForEach-Object{
    $docPath = $_.FullName

    ## Create an instance of excel:
    $ExcelAppInstance = New-Object -ComObject "Excel.Application"
    $ExcelAppInstance.Visible = $false

    ## Open the workbook object within our Excel instance:
    $workbook = $ExcelAppInstance.workbooks.Open($docPath)

    ##iterate the list of queries embedded in the workbook,
    ##updating the file path to our new one:
    $workbook.Queries | ForEach-Object{
        Write-Output $_.Formula
        Write-output $_.Formula.replace($xPath,$tPath)
        $_.Formula = $_.Formula.replace($xPath,$tPath)
    }

    $workbook.Save()
    $workbook.Close()
    $ExcelAppInstance.Quit()
}

The key here is the Queries property inside the Workbook object.

Quite an interesting finding. Thanks Jason for your question and solution.

Have fun!

 Share!