none
Query Refresh Order for independent Queries in Excel RRS feed

  • Question

  • Context

    • Excel sheet with several tabs (let's say sheet1, sheet2 and sheet3 in this order), each tab is loading a table from a Query (let's say respectively Query1, Query2 and Query3). 
    • There is no load to the data model.
    • All the queries are independant (=no cross reference)
    • All queries proprieties are set to Not unable backgroung refresh.


    Question

    It looks like when clicking to REFRESH ALL, the order of the refresh follows the exact same order of the sheets, namely:

    1. first Query1 in sheet1,
    2. then Query2 in sheet2
    3. then Query3 in sheet2
    4. and so on ...


    Could someone who knows please confirm?

    • Edited by anthony34 Friday, March 15, 2019 9:47 AM
    Friday, March 15, 2019 9:29 AM

Answers

  • Hi,

    We do not commit to a certain order during the refresh operation. Roughly saying, Excel applies the following logic when trying to determine the refresh order for queries:

    1. Query Tables are always refreshed before PivotTables.
    2. Non-model Query Tables refreshed before model ones.
    3. There is a logic for model Query Tables and model worksheet connections that tries to figure out simple cases and do proper ordering of Query Tables refresh and model connections refresh.
    4. PivotTables are always last.

    Hope it helps.


    Guy Hunkin - Excel Team

    Sunday, March 31, 2019 3:08 PM

All replies

  • From what I remember, the order RefreshAll uses is alphabetical. It's going through the list of Connection objects in the workbook, and as far as I know Excel always sorts these alphabetically in it's internal list.
    Friday, March 15, 2019 3:57 PM
  • Hi,

    We do not commit to a certain order during the refresh operation. Roughly saying, Excel applies the following logic when trying to determine the refresh order for queries:

    1. Query Tables are always refreshed before PivotTables.
    2. Non-model Query Tables refreshed before model ones.
    3. There is a logic for model Query Tables and model worksheet connections that tries to figure out simple cases and do proper ordering of Query Tables refresh and model connections refresh.
    4. PivotTables are always last.

    Hope it helps.


    Guy Hunkin - Excel Team

    Sunday, March 31, 2019 3:08 PM