VBA to refresh all queries and pivot tables

Topic

New Reply

For years I’ve relied on a chunk of code (from Ozgrid.com) to loop through each worksheet in a file to refresh all queries, and then loop through again to refresh all pivot tables. It worked great for Excel 2000, but it fails in Excel 2010. I’ve since discovered that 2010 has a ‘.RefreshAll’ command that supposedly works for both queries and pivot tables, but I’m finding that it only refreshes my queries. I need to debug and fix this process before I can go fully operational on 2010. If I step through it it sometimes works, but never fully if it is just run. Any ideas?

This is the code:

Code:

Sub macUpdateAllQueryAndPivot()

Dim qt As QueryTable
Dim pt As PivotTable
Dim ws As Worksheet
Dim ActiveFile

ActiveFile = ActiveWorkbook.Name

'On Error Resume Next      ----------    Remarked out to help debug

'Application.ScreenUpdating = False      ----------    Remarked out to help debug

'Update all query tables
    
    Workbooks(ActiveFile).RefreshAll

'    For Each ws In ActiveWorkbook.Worksheets      ----------    Original code that worked in Excel 2000
'        For Each qt In ws.QueryTables
'            qt.Refresh BackgroundQuery:=False
'        Next qt
'    Next ws
 
'Update all pivot tables      ----------    Since the RefreshAll didn't update Pivots I left this in place, but it still fails
    For Each ws In Workbooks(ActiveFile).Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

Application.ScreenUpdating = True

Beep

End Sub

Reply | Quote

Replies

For years I’ve relied on a chunk of code (from Ozgrid.com) to loop through each worksheet in a file to refresh all queries, and then loop through again to refresh all pivot tables. It worked great for Excel 2000, but it fails in Excel 2010. I’ve since discovered that 2010 has a ‘.RefreshAll’ command that supposedly works for both queries and pivot tables, but I’m finding that it only refreshes my queries. I need to debug and fix this process before I can go fully operational on 2010. If I step through it it sometimes works, but never fully if it is just run. Any ideas?

This is the code:

Code:

Sub macUpdateAllQueryAndPivot()

Dim qt As QueryTable
Dim pt As PivotTable
Dim ws As Worksheet
Dim ActiveFile

ActiveFile = ActiveWorkbook.Name

'On Error Resume Next      ----------    Remarked out to help debug

'Application.ScreenUpdating = False      ----------    Remarked out to help debug

'Update all query tables
    
    Workbooks(ActiveFile).RefreshAll

'    For Each ws In ActiveWorkbook.Worksheets      ----------    Original code that worked in Excel 2000
'        For Each qt In ws.QueryTables
'            qt.Refresh BackgroundQuery:=False
'        Next qt
'    Next ws
 
'Update all pivot tables      ----------    Since the RefreshAll didn't update Pivots I left this in place, but it still fails
    For Each ws In Workbooks(ActiveFile).Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

Application.ScreenUpdating = True

Beep

End Sub

I don’t have any live query tables to practise on, but PT.RefreshTable is still valid syntax for refreshing a PivotTable. As an alternative, you could try the syntax PT.PivotCache.Refresh.

Reply | Quote

If your pivot tables are pointing at your queries, you need to change the query (or connection) properties so that the queries do not refresh in the background – otherwise the pivot tables can refresh before the query data has been updated.

Reply | Quote