none
Excel VBA: Macro Error Says "Wait until Microsoft Excel has finished refreshing the Pivot Table Report........... RRS feed

  • Question

  • Version:  Excel 2010 Pro

    Problem:  My macro stops execution after refreshing OLEB pivot table data source and displays a Run-time error '1004':  Please wait until Microsoft Excel has finished refreshing the Pivot Table report, and then try the command again." (see screen shot below)

    Facts:  See code below.  After executing the code line "ActiveWorkbook.Connections(strConnectionName).Refresh" I get this error.  

    The connection type is OLEB and therefore this code is executing the ActiveWorkbook.Connections(strConnectionName).Refresh statement and not the Pivot.Cache.Refresh statement. 

    Interestingly, if I set a breakpoint at the line of code performing the connection refresh and step through the code (using F8) I don't get the error.  Apparently, using F8 gives Excel enough time to refresh before trying to execute the next line of code.  Now it doesn't matter which line of code follows the connection refresh line whatever the next line is it stops and gives me the error, unless I step through the macro with the F8.

    Don't really know what's going on here.  Hope one of you out there does.

    Thanks,

    ...bob

    Error message:

    SECTION OF CODE WHERE ERROR OCCURS:

    'CYCLES THROUGH EACH PIVOT TABLE IN SELECTED SHEET to find pivot table and refresh:
                For Each pvt In sht.PivotTables
                    If Not strPivotName = pvt.Name Then GoTo 50

                    ' IF USING TYPE 2 (OLEB) CONNECTION REFRESH CONNECTION AND BYPASS PIVOT REFRESH
                    If avPivotRefreshList(i, 15) = 2 Or avPivotRefreshList(i, 15) = 3 Then
                        ActiveWorkbook.Connections(strConnectionName).Refresh
                   
                        Else: sht.PivotTables(strPivotName).PivotCache.Refresh
                    End If

                    sht.PivotTables(strPivotName).ClearAllFilters  ' This is where error occurs, if I comment out this line the error then occurs on the following line and so on.


                    On Error Resume Next
                        If PageFilter1 = "" Then GoTo 10
                        sht.PivotTables(strPivotName).PivotFields(PageFilter1).CurrentPage = DropdownValue1
    10                  If PageFilter2 = "" Then GoTo 20
                        sht.PivotTables(strPivotName).PivotFields(PageFilter2).CurrentPage = DropdownValue2
    20                  If PageFilter3 = "" Then GoTo 30
                        sht.PivotTables(strPivotName).PivotFields(PageFilter3).CurrentPage = DropdownValue3
    30                  If PageFilter4 = "" Then GoTo 40
                        sht.PivotTables(strPivotName).PivotFields(PageFilter4).CurrentPage = DropdownValue4

    40                On Error GoTo ErrorHandler1

    50
                  Next pvt


    Bob Sutor



    • Edited by ConstPM Friday, March 28, 2014 2:41 AM
    Friday, March 28, 2014 2:39 AM

Answers

  • SOLUTION (please post if you have a better solution):  I found a solution to this problem on the Web at http://www.mrexcel.com/forum/excel-questions/643150-1004-please-wait-until-microsoft-excel-has-finished-refreshing-pivottable-report-then-try-command-again.html

    Thanks to AD_Taylor for the code to fix this.

    Code used to loop program flow to an errWait handler which delays execution of following code by one second per loop.

    Sub RefreshConection()
        ThisWorkbook.Connections("MainDbData").Refresh
       
    resumeWait:

        On Error GoTo errWait
            'run this lines below only after refreshing data from database
            ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").ClearAllFilters
            ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").CurrentPage = "20"
        On Error GoTo 0
       
        Exit Sub
       
    errWait:
        Application.Wait (Now + TimeValue("00:00:01")) 'Ask Excel to wait for 1 second
        Resume resumeWait 'Resume the code to try again
    End Sub


    Bob Sutor


    • Marked as answer by ConstPM Friday, March 28, 2014 3:08 AM
    • Unmarked as answer by ConstPM Friday, March 28, 2014 3:28 AM
    • Edited by ConstPM Friday, March 28, 2014 3:29 AM
    • Marked as answer by ConstPM Sunday, March 30, 2014 7:26 PM
    Friday, March 28, 2014 3:08 AM

All replies

  • SOLUTION (please post if you have a better solution):  I found a solution to this problem on the Web at http://www.mrexcel.com/forum/excel-questions/643150-1004-please-wait-until-microsoft-excel-has-finished-refreshing-pivottable-report-then-try-command-again.html

    Thanks to AD_Taylor for the code to fix this.

    Code used to loop program flow to an errWait handler which delays execution of following code by one second per loop.

    Sub RefreshConection()
        ThisWorkbook.Connections("MainDbData").Refresh
       
    resumeWait:

        On Error GoTo errWait
            'run this lines below only after refreshing data from database
            ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").ClearAllFilters
            ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").CurrentPage = "20"
        On Error GoTo 0
       
        Exit Sub
       
    errWait:
        Application.Wait (Now + TimeValue("00:00:01")) 'Ask Excel to wait for 1 second
        Resume resumeWait 'Resume the code to try again
    End Sub


    Bob Sutor


    • Marked as answer by ConstPM Friday, March 28, 2014 3:08 AM
    • Unmarked as answer by ConstPM Friday, March 28, 2014 3:28 AM
    • Edited by ConstPM Friday, March 28, 2014 3:29 AM
    • Marked as answer by ConstPM Sunday, March 30, 2014 7:26 PM
    Friday, March 28, 2014 3:08 AM
  • Hi Bob,

    Thanks for sharing the solution.

    It would be helpful for other community members.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Iris Y. Huang Wednesday, March 15, 2017 2:33 PM
    • Unproposed as answer by Iris Y. Huang Wednesday, March 15, 2017 2:33 PM
    • Proposed as answer by Iris Y. Huang Wednesday, March 15, 2017 2:34 PM
    • Unproposed as answer by Iris Y. Huang Wednesday, March 15, 2017 2:34 PM
    Monday, March 31, 2014 5:22 AM
    Moderator
  • I found the following code worked best:

        ActiveWorkbook.RefreshAll 'will refresh connections, pivot tables, calculations, etc.
        ActiveWorkbook.Save 'Save will wait for refreshing is done

    'Then move on to others

    Wednesday, March 15, 2017 2:39 PM
  • I found the following code worked best:

        ActiveWorkbook.RefreshAll 'will refresh connections, pivot tables, calculations, etc.
        ActiveWorkbook.Save 'Save will wait for refreshing is done

    'Then move on to others

    Wednesday, March 15, 2017 2:40 PM