Refresh one connection at a time

In some cases you might find that the RefreshAll command isn’t working as you’d hoped. For example, if you have a pivot table looking up data from a SQL database connection you need the pivot table to refresh after the connection but RefreshAll won’t necessarily do that.

 

To ensure one connection is refreshed at a time you should make your VB loop through each connection one by one and in the order you want them to. You can name each individual pivot cache, or even each pivot table, but then you have to keep the code maintained when there are changes. The following module is reusable because it will first look forĀ every connection in the file and refresh them individually before looking forĀ every pivot table and refreshing those individually too.

 

 

Sub RefreshAll()

Dim wc As WorkbookConnection
Dim pc As PivotCache
For Each wc In ThisWorkbook.Connections
wc.ODBCConnection.BackgroundQuery = False
wc.Refresh
Next wc
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *