none
Wait for the excel refresh to complete before executing save (next) step RRS feed

  • Question

  • I am opening the excel file from Powershell and performing "refresh all", then "save" and "quit"

    I would like to wait till the "Refresh All" is completed prior to executing "Save"

    I have a brute force approach that kinda works with Sleep. Is there a programmatic way to do the wait till "Save All" is completed? 

    $workSheet = $workBook.Sheets.Item("OtherRef")
    $workSheet.Select()
    $workBook.RefreshAll()
    Start-Sleep -s 30
    $workBook.Save()

    $excelObj.Quit()

    Thanks in Advance.

    Niranjan

    I have multiple excel powerqueries that bring in the data from other files. I get the following message if I don't wait currently with sleep

    Monday, January 18, 2016 3:02 AM

Answers

  • >>>I have a brute force approach that kinda works with Sleep. Is there a programmatic way to do the wait till "Save All" is completed?

    According to your description, I suggest that you might be able to force the RefreshAll() to execute synchronously by setting BackgroundQuery = $False on all query tables in workbook:

    foreach ($Sheet in $excelworkbook.Worksheets) {
        foreach ($QTable in $Sheet.QueryTables) {
            $QTable.BackgroundQuery = $false
        }
    }


    Wednesday, January 20, 2016 9:42 AM

All replies

  • >>>I have a brute force approach that kinda works with Sleep. Is there a programmatic way to do the wait till "Save All" is completed?

    According to your description, I suggest that you might be able to force the RefreshAll() to execute synchronously by setting BackgroundQuery = $False on all query tables in workbook:

    foreach ($Sheet in $excelworkbook.Worksheets) {
        foreach ($QTable in $Sheet.QueryTables) {
            $QTable.BackgroundQuery = $false
        }
    }


    Wednesday, January 20, 2016 9:42 AM
  • Hi, 

    you need to unckeck the background option in your excel file

    Friday, September 14, 2018 2:40 PM