Refresh All - External Data and Pivot Tabels

[Ziggy12]

I have a worksheet linked to external data that refreshes periodicaly throughout the day. Linked to the worksheet is a number of pivot tables. I haven't linked the pivots to the external data as the data needs a number of formulas to get other pivot values.

On running the code below the worksheet is update and it looks like the pivots are refreshed but it seems the pivots are refreshed before the external data is fully updated. If I manually refresh the pivots after the code refresh the pivots change.

I tried adding a 2 min wait before running the cache refresh but doesn't make any difference.

I can see the world icon rotating on the status bar - the pivot refresh needs to happen after that as stopped. The refresh all method is meant to refresh all external data ranges and pivots but doesn't seem to.

Any ideas?

Sub Refresher()
Dim WrkSheet As Worksheet
Dim Pvttable As PivotTable

Set WrkSheet = Worksheets("Last Month Pivots")

Application.StatusBar = "Getting Data Please Wait"
ThisWorkbook.RefreshAll

For Each Pvttable In WrkSheet.PivotTables
Pvttable.PivotCache.Refresh
Next

Application.StatusBar = False
Worksheets("Last 30 Days Dashboard").Range("O3") = "Last Data Refresh " & Format(Now(), "d-mmm-yy hh:mm")
End Sub

 

[Derek Brown]

The VBA 'RefreshAll' will use the setting, in native Excel, to determine whether the refresh is to take place in the background ..... or not.
I suspect that you have the 'Background Refresh' option ticked. If you untick that, the VBA code will then wait until the refresh is complete before moving to the next statement.
In 2007/2010 you will find the option on the Data TAB, Connections, Properties.

 

[Ziggy12]

Thanks for that. I tried the background query = false still didn't work then discovered I needed to refer to the MS Query worksheets separatly from the pivot tables. This works OK.
I'd recorded a the refresh and thought the refresh all referrd to the data sheets as well but it doesn't.

Sub Refresher()
Dim WrkSheet As Worksheet
Dim Pvttable As PivotTable

Set WrkSheet = Worksheets("Last Month Pivots")

Application.StatusBar = "Getting Data Please Wait"
Worksheets("Data 1").Range("A3").ListObject.QueryTable.Refresh BackgroundQuery:=False
Worksheets("Data 2").Range("A3").ListObject.QueryTable.Refresh BackgroundQuery:=False
For Each Pvttable In WrkSheet.PivotTables
Pvttable.RefreshTable
Next

Application.StatusBar = False
Worksheets("Last 30 Days Dashboard").Range("O3") = "Last Data Refresh " & Format(Now(), "d-mmm-yy hh:mm")
End Sub