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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to pivot-table

Examples related to vba