8

Is there a particular order in which tables and connections are refreshed in Excel when using the Refresh All on the data tab?

For example, suppose I have a table that is sourced via an ODBC or SQL Server connection. From this table I create a subset of data that then feeds various Pivot Tables. When I use Refresh All, will Excel understand that the ODBC data source needs to be refreshed first?

Is there any default logic or order to using Refresh All?

Improve this question
2
  • Have up voted as having same problem using PowerQuery & Pivot Tables. Seems only way is to update individual connections through VBA. Had a look on UserVoice and there doesn't seem to be much interest in getting a non-VBA method implemented.
    – Darren Bartrup-Cook
    Jul 20, 2020 at 10:36
  • 1
    @DarrenBartrup-Cook I found when using VBA to specify the order, its best to turn background refresh off, so that a query will fully run before the next action is taken. Its odd that there isn't a way you can specify a refresh order built in, surely others must have had this issue before
    – PeterH
    Jul 20, 2020 at 11:32

3 Answers 3

Reset to default
1

Yes, there is a logic and order.

Microsoft will first process any external data connections prior to refreshing the data in the workbook.

The first time you import external data, Excel creates connection information, sometimes saved to an Office Data Connection (ODC) file, which describes how to locate, log in, query, and access the external data source.

When you are connected to an external data source, you can perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

You may find details here.

However, due to latency and race condition concerns, I would recommend setting your workbook to refresh external connections first, and then separately refresh your workbook data.

Improve this answer
0

When I use Refresh All, will Excel understand that the odbc needs to be refreshed first ?

Absolutely. Excel always updates non-dependent "source" data before re-calculating anything that depends on that source. To do it any other way would result in incorrect results.

You can see this in action by observing how Excel calculates a formula by using the Evaluate Formula feature. Here's a formula where the first step involves getting the value in cell G15. That cell in turn contains a formula, which requires getting the value of E15. That cell in turn contains a formula, the first step of resolving which requires evaluating the IF function...and so on.

enter image description here

This ordered process of "working backward" through volatile values until the root data is identified is inherent in how Excel calculates and evaluates data. Rest assured Microsoft didn't leave this important consideration to mere chance.

Improve this answer
1
  • 2
    This is true for formula, but not for external dependencies. Namely, a Pivot Table based on data retrieved from an external source will need multiple refreshes to be updated.
    – Uriel
    Dec 24, 2019 at 9:50
0

You may ensure your Pivot tables are refreshed after the external datasource by calling this type of sub:

Sub AllPT_Update2()

    Dim pTable As PivotTable
    For Each pTable In ActiveSheet.PivotTables
        pTable.PivotCache.Refresh
    Next pTable

End Sub
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .