refresh both the External data source and pivot tables together within a time schedule

Question

In my last post Auto refresh pivottables data in excel on first run, i found that on my first execution the query from the External data source is refreshed and takes approximately 1 min to execute. and in my second run, the pivot tables are updated.

Is there a solution (VBA code) to refresh both the External data source and pivot tables together within a time schedule (If suppose we set a timer) by clicking command button?

Answer 1

Under the connection properties, uncheck "Enable background refresh". This will make the connection refresh when told to, not in the background as other processes happen.

With background refresh disabled, your VBA procedure will wait for your external data to refresh before moving to the next line of code.

Then you just modify the following code:

ActiveWorkbook.Connections("CONNECTION_NAME").Refresh
Sheets("SHEET_NAME").PivotTables("PIVOT_TABLE_NAME").PivotCache.Refresh

You can also turn off background refresh in VBA:

ActiveWorkbook.Connections("CONNECTION_NAME").ODBCConnection.BackgroundQuery = False

Answer 2

I used the above answer but made use of the RefreshAll method. I also changed it to allow for multiple connections without having to specify the names. I then linked this to a button on my spreadsheet.

Sub Refresh()

    Dim conn As Variant

    For Each conn In ActiveWorkbook.Connections
        conn.ODBCConnection.BackgroundQuery = False
    Next conn

    ActiveWorkbook.RefreshAll
End Sub