RefreshAll not updating pivot tables

Question

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?

Answer 1

this is my first contribution towards stack overflow so be gentle with me :D

I had the same issue but when I ran my code manually I realised the "RefreshAll" Method is executed in the background whilst the rest of my code continued to run.

One way to solve the issue is to set the "Refresh in Background" setting to false.

This is what the others already mentioned... (nothing new)

BUT I found another solution in a different thread, which will work for everyone trying to refresh the workbook, even if they havn't changed the settings for their Excel-Applicaiton.

"Application.CalculateUntilAsyncQueriesDone" is the Method that tells Excel to wait for the Background-Query to finish, before your code will continue to run.

Have a nice day!

Sub Refresh()

Dim sht As Worksheet
Dim pvt As PivotTable

ActiveWorkbook.RefreshAll

Application.CalculateUntilAsyncQueriesDone

For Each sht In ActiveWorkbook.Sheets
    For Each pvt In sht.PivotTables
        pvt.PivotCache.Refresh
    Next pvt
Next sht

End Sub

Answer 2

I have also assumed that RefreshAll refreshes everything that can be refreshed and was surprised it does not seem to be the case. Below is my attempt to provide a procedure that tries to refresh everything that can be refreshed. Please note that you can try to remove some parts in order to make it run faster.

Sub RefreshAllData()

    ' Declare the required variables
    Dim wb As Workbook
    Dim sht As Worksheet
    Dim qt As QueryTable
    Dim lo As ListObject
    Dim pvt As PivotTable
    Dim conn As WorkbookConnection
    Dim pq As Object
    
    ' Set the active workbook to work on
    Set wb = ActiveWorkbook
    

    With wb

        ' Loop through each connection in the workbook and refresh it
        For Each conn In .Connections
            On Error Resume Next ' Skip any errors that may occur during refreshing
            conn.Refresh
            On Error GoTo 0 ' Resume normal error handling
        Next conn
        
        ' Loop through each query in the workbook and refresh it
        For Each pq In .Queries
            On Error Resume Next ' Skip any errors that may occur during refreshing
            pq.Refresh
            On Error GoTo 0 ' Resume normal error handling
        Next pq

        ' Loop through each sheet in the workbook
        For Each sht In .Sheets
            
            ' Loop through each QueryTable in the sheet and refresh it
            For Each qt In sht.QueryTables
                qt.Refresh
            Next qt
            
            ' Loop through each ListObject in the sheet and refresh its QueryTable
            For Each lo In sht.ListObjects
                lo.QueryTable.Refresh BackgroundQuery:=False
            Next lo
            
            ' Loop through each PivotTable in the sheet and refresh its PivotCache
            For Each pvt In sht.PivotTables
                pvt.PivotCache.Refresh
            Next pvt
            
        Next sht
        
    End With

End Sub

Answer 3

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

Answer 4

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

Answer 5

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

Calculate
ActiveWorkbook.RefreshAll

Answer 6

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