Refreshing tables

  • I have a workbook that contains linked tables for data in other Excel files, and pivot tables built off these linked tables.. It appears when using ActiveWorkbook.RefreshAll that some pivot tables are being refreshed before the linked tables they're built off.


    Is there a method for refreshing all the linked tables first, then the pivot tables?

  • Re: Refreshing tables


    When you say linked tables are you saying that you have created pivot tables in one file based on data in another file?


    In any case, you can always just refresh the pivot tables with something like below. So however you are refreshing...use this after that.


  • Re: Refreshing tables


    Thanks Bryce,
    I've added linked tables in my workbook, on which the pivottables are based.


    This is the code I've been using (same as yours, basically):

    Code
    ActiveWorkbook.RefreshAll    
    
    
    For Each WS In ThisWorkbook.Worksheets
            For Each PT In WS.PivotTables
              PT.RefreshTable
            Next PT
        Next WS


    For some reason, the pivot tables still need to be manually refreshed after this procedure to display the updated data. I would have thought ActiveWorkbook.RefreshAll should update all the linked tables, and the for each loop should take care of the pivot tables. Any idea what's going wrong?

  • Re: Refreshing tables


    I still have the problem with pivot tables failing to refresh after the tables refresh. I'm calling 2 separate macros, separated by a DoEvents function, as follows:


    Code
    Sub RefreshConnections()
    Dim con As WorkbookConnection
    
    
        For Each con In ThisWorkbook.Connections
         con.Refresh
         Next con
    
    
    End Sub


    DoEvents



    The only real solution I've found is to use the ActiveWorkbook.RefreshAll function twice, which isn't speedy or elegant. Whilst I can get by, my I can't help but try and find the proper solution.

  • Re: Refreshing tables


    Quote

    The only real solution I've found is to use the ActiveWorkbook.RefreshAll function twice, which isn't speedy or elegant. Whilst I can get by, my I can't help but try and find the proper solution.


    Try the below code:




    Regards


    Maqbool

  • Re: Refreshing tables


    Thanks Maqbool,
    Your code does a good job of refreshing all the pivot tables, but unfortunately the same issue still exists. If I try to run the pivot table refresh macro immediately after the data connection macro (all within the one macro), if appears that it doesn't wait for all the connections to refresh before refreshing the pivot tables.


    I'm tempted to put a time lapse in between, but surely there must be a way within a single macro to refresh all data connections, and wait until they are finished refreshing before refreshing all pivot tables (other than DoEvents, which doesn't seem to work)?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!