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?

'first thing first' which means that you should be sure your query is refreshed before you update PT. depending on way you create your query you could possibly control `AfterRefresh event` like described (or similar) to [that description](http://msdn.microsoft.com/en-us/library/ff835922%28v=office.14%29.aspx)... than you could refresh you PT being sure your data is up to date. — Kazimierz Jawor, Apr 25 '13 at 18:53
I don't want to set a particular time to refresh. I want it to refresh when i click on`command button`. when i click the button. it takes `1 -2 minutes` to refresh. Instead i want to set a time like (when i click the button , within 10sec it has to refresh the data) — user42995, Apr 25 '13 at 20:04
with `Application.OnTime` you are able to set starting time as a difference to the moment of triggering, eg. `Application.OnTime Now + TimeValue("00:00:10")` which start the action after 10 sec. — Kazimierz Jawor, Apr 25 '13 at 20:27
ok, thank you so much. Now my problem is solved. I posted it in my other post. Thank you so much for you valuable time and i have learnt new `Excel-VBA methods and properties` from you — user42995, Apr 25 '13 at 20:34

score 26 · Accepted Answer · answered Apr 25 '13 at 20:46

26

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

answered Apr 25 '13 at 20:46

Alex

291
3
4

Thank you, I have set `BackgroundQuery` to `false`. I have solved my problem which i specified in my earlier post. Thank you so much for spending your valuable time. – user42995 Apr 25 '13 at 21:38
Thank you, this is a show and concise answer!! I also want to update my Pivot tables without data source refreshing, and i thought i needed a miracle. – fuadj Nov 09 '17 at 19:50

score 12 · Answer 2 · answered Sep 16 '13 at 20:02

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

score 4 · Answer 3 · answered May 27 '15 at 07:07

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

score 0 · Answer 4 · answered Feb 28 '14 at 18:14

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

:)

score 0 · Answer 5 · answered Oct 03 '19 at 16:13

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

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

5 Answers5

Linked