Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VBA to refresh all queries and pivot tables

    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
    Last edited by RetiredGeek; 2013-02-08 at 09:12. Reason: Added Code Tags

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by d_rasley View Post
    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.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •