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
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:
You can also turn off background refresh in VBA:
ActiveWorkbook.Connections("CONNECTION_NAME").ODBCConnection.BackgroundQuery = False
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
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.
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
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