Excel VBA - RefreshAll not updating pivot tables

10
votes

I have a template workbook, which has several data tables connected to sql connections, as well as some pivot tables who's sources are the data brought through.

I was under the impression that ActiveWorkbook.RefreshAll would update all connections, then update the pivots. That in fact is what happens when I run the refresh all manually. However, when I run the VBA (which is actually in Access, but is correctly referenced etc) it updates the connections but NOT the pivot tables?

I've tried DoEvents after the RefreshAll which had no effect.

Is my only option now to run a For each through all the worksheets, data sources, pivot caches and refresh them that way?

4
excelms-accessvba
sometimes when you refresh pivot tables it doesnt change the "data source" ranges. If the data range increases you may have to manually change it, or have your vba code know to offset till blank. – Doug Coats
the troublesome issue is that ActiveWorkbook.RefreshAll is what's written in code by macro recorder when "refresh all" is initiated in MS Excel GUI. – Alexei Martianov

4 Answers

16
votes

ActiveWorkbook.RefreshAll does as in matter of fact RefreshAll connections and pivots. However, in your scenario the pivots are probably based on the data you have to refresh first. The pivot will refresh while the data is not loaded yet, hence the unexpected behavior.

There are multiple solutions for this:

  • Either have the data returned through the connection as a pivotcache so the pivot table will automatically refresh when the data is returned. This way you will not have the data itself stored in a seperate sheet in your workbook either.

  • Set the "Refresh in Background" property to false for all connections, either in the code or through the UI, then execute as normally. Twice. The second time the pivotcaches will have the updated data and thus refresh as expected. - Edit: I do not recommend this, since you will open the db connection twice, load the data twice, etc. Highly inefficient!

  • Set the "Refresh in Background"- property to false as mentioned above. After refreshing using Refresh all, loop through your worksheets' pivottable collection to refresh those manually after the data has been loaded as shown below.

Code:

Sub test()

Dim ws as Worksheet
Dim pt as PivotTable

ActiveWorkbook.RefreshAll 'make sure the refresh in bg property is false for all connections

For each ws in ActiveWorkbook.Worksheets
    For each pt in ws.pivottables
        pt.RefreshTable
    Next pt
Next ws

End Sub

Or simply refresh only the pivotcaches (more efficient, especially if multiple tables use the same cache):

Sub test()
Dim pc as PivotCache

ActiveWorkbook.RefreshAll 'make sure the refresh in bg property is false for all connections

For each pc in ActiveWorkbook.PivotCaches
    pc.Refresh
Next pc

End Sub
5
votes

I have solved the issue by using the following

    For Each sht In .Sheets
        For Each qt In sht.QueryTables
            qt.Refresh
        Next qt
        For Each lo In sht.ListObjects
            lo.QueryTable.Refresh BackgroundQuery:=False
        Next lo
        For Each pvt In sht.PivotTables
            pvt.PivotCache.Refresh
        Next pvt
    Next sht
0
votes

I have solved the issue by adding a simple 'Calculate' to the code.

Calculate
ActiveWorkbook.RefreshAll
0
votes

I ran into this issue while copying and saving workbooks through macros and had no luck with different pivot table/cache refreshes, but found luck with changing the SaveData property for each pivot table. Just in case someone reads this like I did and is looking for something else to try.

For Each pt in ws.PivotTables
   pt.SaveData = True
Next pt