Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Pivot tables will not refresh on first try

  1. #1
    Join Date
    26th May 2012
    Posts
    1

    Pivot tables will not refresh on first try

    I have a macro code that runs a MSQuery returning a table of data to a specified worksheet. I have a pivot table (on a separate worksheet) that uses that table as source data. The same macro that runs the MSQuery also refreshes the pivot table. For some reason the macro must be run twice to get the pivot table to refresh.

    The table of data is imported just fine on the first run. Not sure what the issue is. I have tried multiple variations of code to refresh the pivot tables. I even put the code in twice.

    Thanks for any help.
    dgott

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th May 2012
    Posts
    8

    Re: Pivot tables will not refresh on first try

    I have two macros that I use often, one to refresh pivot tables, another to refresh queries, you could try to call them at the end of your code

    VB:
    Public Sub RefreshQueryTables() 
        Dim w As Worksheet 
        Dim q As QueryTable 
        On Error Resume Next 
        For Each w In ActiveWorkbook.Worksheets 
            Application.StatusBar = "Refreshing Query Table: " & w.Name 
            For Each q In w.QueryTables 
                q.Refresh 
            Next 
        Next 
    End Sub 
     
    Public Sub RefreshPivotTables() 
        Dim w As Worksheet 
        Dim p As PivotTable 
        Dim pf As PivotField 
        Dim pi As PivotItem 
        On Error Resume Next 
        For Each w In ActiveWorkbook.Worksheets 
            Application.StatusBar = "Refreshing Pivot Table: " & w.Name 
            For Each p In w.PivotTables 
                For Each pf In p.PivotFields 
                    For Each pi In pf.PivotItems 
                        pi.Delete 
                    Next 
                Next 
                p.RefreshTable 
                p.Update 
            Next 
        Next 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th July 2012
    Posts
    1

    Re: Pivot tables will not refresh on first try

    I'm having the same issue. I tried splitting out the pivot table refreshing code into a separate Sub and calling it. I even tried calling it twice (from within another subroutine). But, still, not all of my pivot tables refresh. I put a MsgBox in the pivot table refresh routine to tell me what pivot table it was refreshing, and it says it's refreshing the ones that aren't updating. But, if I manually refresh the pivot table after the macro runs...it then updates.

    If I run the macro after it runs the first time (and doesn't update), it then updates.

    The data that the pivot table refers to is in a table that is refreshed earlier in the macro from a database connection. That's refreshing fine.

    I'm stumped.


    Quote Originally Posted by CorvetteLover View Post
    I have two macros that I use often, one to refresh pivot tables, another to refresh queries, you could try to call them at the end of your code

    VB:
    Public Sub RefreshQueryTables() 
        Dim w As Worksheet 
        Dim q As QueryTable 
        On Error Resume Next 
        For Each w In ActiveWorkbook.Worksheets 
            Application.StatusBar = "Refreshing Query Table: " & w.Name 
            For Each q In w.QueryTables 
                q.Refresh 
            Next 
        Next 
    End Sub 
     
    Public Sub RefreshPivotTables() 
        Dim w As Worksheet 
        Dim p As PivotTable 
        Dim pf As PivotField 
        Dim pi As PivotItem 
        On Error Resume Next 
        For Each w In ActiveWorkbook.Worksheets 
            Application.StatusBar = "Refreshing Pivot Table: " & w.Name 
            For Each p In w.PivotTables 
                For Each pf In p.PivotFields 
                    For Each pi In pf.PivotItems 
                        pi.Delete 
                    Next 
                Next 
                p.RefreshTable 
                p.Update 
            Next 
        Next 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    13th November 2012
    Posts
    1

    Re: Pivot tables will not refresh on first try

    I found this after considerable looking and it solved my problem of the refresh not working all the time.
    From Foxden: I had this problem too and what you need to do is change the setting of your data connections. 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. If using Excel 2007, bring up the "Connection Properties" and uncheck the box that enables the background refresh. You can get there by clicking on the table that gets refreshed and where you would click to actually refresh the data you should see an arrow for more options.

    Your solutions only worked because they by chance, gave excel enough time to refresh before moving on. Doing what I have suggested is a far more definitive approach and will reduce bugs moving forward with your coding. I hope this helps, from one programmer to another.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Refresh All Pivot Tables In Workbook
    By HerronShane in forum EXCEL HELP
    Replies: 4
    Last Post: January 20th, 2012, 11:26
  2. Pivot tables Refresh within onClick()
    By rookie_user in forum Excel and/or Access Help
    Replies: 6
    Last Post: August 4th, 2006, 00:04
  3. refresh pivot tables linked to pivot table
    By macrocyte in forum EXCEL HELP
    Replies: 6
    Last Post: July 28th, 2006, 13:54
  4. Periodic refresh of pivot tables
    By itbitty in forum EXCEL HELP
    Replies: 2
    Last Post: July 22nd, 2006, 02:44
  5. Replies: 11
    Last Post: February 18th, 2004, 07:59

Bookmarks

Posting Permissions

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