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

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?

I think there is a simpler way to make excel wait till the refresh is done, without having to set the Background Query property to False. Why mess with people's preferences right?

Excel 2010 (and later) has this method called CalculateUntilAsyncQueriesDone and all you have to do it call it after you have called the RefreshAll method. Excel will wait till the calculation is complete.

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

I usually put these things together to do a master full calculate without interruption, before sending my models to others. Something like this:

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Application.CalculateFullRebuild
Application.CalculateUntilAsyncQueriesDone

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

Auto Refresh Workbook for example every 5 sec. Apply to module

Public Sub Refresh()
'refresh
ActiveWorkbook.RefreshAll

alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
    Application.OnTime alertTime, "Refresh"

End Sub

Apply to Workbook on Open

Private Sub Workbook_Open()
alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
Application.OnTime alertTime, "Refresh"
End Sub

:)

I found this solution online, and it addressed this pretty well. My only concern is looping through all the pivots and queries might become time consuming if there's a lot of them:

Sub RefreshTables()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim objList As ListObject
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    For Each objList In ws.ListObjects
        If objList.SourceType = 3 Then
            With objList.QueryTable
                .BackgroundQuery = False
                .Refresh
            End With
        End If
    Next objList
Next ws

Call UpdateAllPivots

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Sub UpdateAllPivots()
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt
Next ws

End Sub