Refresh Excel connections using Powershell

Question

I have the below code that opens a spreadsheet, deletes all connections and "Saves" a new file.

$a = New-Object -COM "Excel.Application"
$a.Visible = $false
$b = $a.Workbooks.Open("F:\Scripts\All Users.xlsx")

do
{
    $b.Connections.Item(1).Delete()
    $Count = $b.Connections.Count()
} until($Count -eq 0)

$b.SaveAs("F:\Scripts\Users Home Drive Search.xlsx")
$b.Close()

I would like to know two things:

How do I get the sheet to RefreshAll connections? I've tried "$b.Connections.refreshall()" but the refreshall() doesn't exist.
How do I quit Excel application? I ran "New-Object -COM "Excel.Application" | Get-Member -MemberType Methods" and I don't see a quit or exit method.

Theo Theo · Accepted Answer · 2021-07-07T11:47:44

RefreshAll() is a method of the WorkBook object, so use that to refresh the external connections:

$b.RefreshAll()

To exit Excel and remove the used COM objects from memory, use

$a.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($b)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($a)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

BTW, I would use more descriptive variable names, so $excel instead of $a and $workbook instead of $b to avoid confusion later on.

Refresh Excel connections using Powershell

1 Answers