0

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()
BigBen
  • 46,229
  • 7
  • 24
  • 40
Ross
  • 2,463
  • 5
  • 35
  • 91

2 Answers2

0

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.

postanote
  • 15,138
  • 2
  • 14
  • 25
0

FYI for those using this for Power Query refreshes.

$sheet.QueryTables.QueryTable.Refreshing does not get updated for Power Queries since $sheet.QueryTables returns null (except when other query types are in the file)

Therefore, the only way to have it wait is to disable the 'Enable Background Refresh' as @BigBen pointed out in his update.

Also, a sleep of one second is needed to update the Query property 'Download Complete' or it will read incorrect '..did not complete'

Procrastinator
  • 2,526
  • 30
  • 27
  • 36
Nic Johnson
  • 31
  • 2
  • You are right about the QueryTables property not getting updated. However a workaround is to use the ListObjects property on the sheet instead. See my answer for a similar question here https://stackoverflow.com/a/74228120/17028865 – Seb Oct 28 '22 at 12:41