PowerShell Script to turn off "Enable background refresh" in an Excel
I have a PowerShell script that dynamically creates, updates, and deletes excel workbooks based of a set number of of conditions. One of the processes involved in creating the excel is to run a power query. However it seems to default the setting "Enable background refresh" as enabled.
This causes issues when running the update section of the code as the excel opens, saves, and closes before the power query can refresh.
I this script is constantly creating and deleting new excels so turning off the setting manually is not viable. The only solution I can find is to add a sleep function, however when there are a lot of excels this causes performance delays.
$File = Get-Item("$Path\$Set\$Set-main-$ReleaseDate.xlsx") $Excel = New-Object -ComObject excel.application $Excel.DisplayAlerts = $False $Excel.visible = $False $WorkBook = $Excel.Workbooks.Open($File.FullName) $WorkBook.RefreshAll() $WorkBook.SaveAs($File, 51) $WorkBook.Close($True) $Excel.Quit() [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkSheet) [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkBook) [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) [System.GC]::Collect()
Maybe these can provide you guidance.
You could just add a macro (load an event) to you sheets that does this natively, for example, this
Dim lCnt As Long 'The following code loops through all connections 'in the active workbook. Change the property to 'True to Enable, False to Disable background refresh. With ActiveWorkbook For lCnt = 1 To .Connections.Count 'Excludes PowerPivot and other connections If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then .Connections(lCnt).OLEDBConnection.BackgroundQuery = False End If Next lCnt End With End Sub
Yet your question could also bee seen as a duplicate of this SQ Q&A Refreshing Excel Sheets using PowerShell
$sheet.Calculate() $null = $sheet.QueryTables.QueryTable.Refreshing Excel has Application.Ready property. It should indicate when Excel is ready for automation communication, but details are unclear. Try something like this:
As well as another discussion here, relevant to your use case. Refreshing Excel connections with Powershell
The only other option is to use a process wait prior to each vs the Sleep approach you are using.