Refresh Pivot Cache and Pivot Tables issue

[Vivek D]

I have a report that picks up data from another excel file using Power Query. Within the report I have several pivot tables and calculations done etc.

When the source data changes, if I run Data -> Refresh All only data refreshes, the pivot tables don't.
If I click on a pivot table and then click Pivot Table -> Options -> Refresh All, all pivot tables refresh.

So I assume both steps are required to ensure the report is fully updated.

Now, I tried to put these steps into a VBA macro as shown below:-

Sub RefreshData()

    Application.StatusBar = "Processing..."
   
    'Refresh Data connection
    ThisWorkbook.RefreshAll
       
    'Refresh Pivot Cache
    ThisWorkbook.Worksheets("Analysis View").PivotTables("Stage_Filter").PivotCache.Refresh
   
    Application.StatusBar = False
       
End Sub

However, only the data refreshes but the pivot tables don't refresh. If I run it again though everything refreshes.

Basically, if I run the above macro twice everything refreshes as expected.

One more thing that I noticed was that, if I step through the code instead of running it, it refreshes everything as expected in one execution.

What exactly could be causing the macro to not produce the expected results when running in one execution without stepping through the code?

 

[Luke M]

It sounds like your data refresh is taking a few seconds to process. So, essentially, the code is running too fast (not waiting for data to finish updating before going on to next line).

You could test this by putting in a delay like so:

Sub RefreshData()

Application.StatusBar = "Processing..."

'Refresh Data connection
ThisWorkbook.RefreshAll

'Pause for 2 seconds to make sure data updated
'You can play with the time interval as needed to test things
Application.Wait TimeSerial(0, 0, 5)

'Refresh Pivot Cache
ThisWorkbook.Worksheets("Analysis View").PivotTables("Stage_Filter").PivotCache.Refresh

Application.StatusBar = False
      
End Sub

 

[Vivek D]

Tried that.. by the way, I had to change the TimeSerial portion to get it to work properly. However, even on trying 25 secs (the data usually takes 4-5 sec max to refresh) it didn't work. In fact it appears as if the RefreshAll appears to be running after the Wait for whatever reason because I put a message box after the wait, I get that and then I get a message in the status bar saying Refreshing Query...

Sub RefreshData()

    Application.StatusBar = "Processing..."
   
    'Refresh Data connection
    ThisWorkbook.RefreshAll
   
   
    'Pause for a few seconds to make sure data is refreshed
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 25
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
   
    'Refresh Pivot Cache
    ThisWorkbook.Worksheets("Analysis View").PivotTables("Stage_Filter").PivotCache.Refresh
   
    Application.StatusBar = False

End Sub

 

[NARAYANK991]

Hi Vivek ,

The Excel help on the RefreshAll method says that it refreshes both data connections and pivot tables.

Try using the Refresh method.

Narayan

 

[Vivek D]

FYI...
Based on a response here... Disabling "Enable Background Refresh" Option for the Data Connection solved it for me.

The comment said...
"By default, Excel will "Enable background refresh". This must be turned off and it will force the macro to complete the refresh task before it moves on."