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.

Excel Setting:

enter image description here

Update Code:

    $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()

1 answer

  • answered 2020-07-29 18:03 postanote

    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
    

    More VBA examples here.

    Yet your question could also bee seen as a duplicate of this SQ Q&A Refreshing Excel Sheets using PowerShell

    And here: Make PowerShell Wait for Excel to Finish Refreshing Pivot Table

    $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.