Refresh Pivot Table

Question

In Excel 2013 - what is the VBA Syntax to refresh a Pivot Table? I have tried both of the options below and neither of them refresh my pivot table.

    'Does Not Work
Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
'Does Not Work
For Each Sheet In ThisWorkbook.Worksheets
    For Each Pivot In Sheet.PivotTables
        Pivot.RefreshTable
        Pivot.Update
    Next
Next

EDIT
To Add to it---I am actually needing to refresh a embedded MySQL Query 1st THEN refresh the pivot table.

Have you tried using the macro recorder to see what syntax it uses? Looks like `activeworkbook.refreshall` should replace those loops. — gtwebb, May 11 '16 at 17:30
Which version of Excel? When I use the macro recorder I get this: ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh — Habib Inman, May 11 '16 at 17:33
What's the pivot table source exactly? Is the source refreshed? — Mathieu Guindon, May 11 '16 at 17:34
@Mat'sMug - the pivot table source is an embedded mysql query that needs to be refreshed first...I wonder if the pivot refresh fire is occuring before the pivot table is fully refreshed. — Habib Inman, May 11 '16 at 17:35
Excel 2013 and I was using the refresh all button from the drop down. Using the normal refresh button I get the ..pivotcashe.refresh which also works running it through vba for me. — gtwebb, May 11 '16 at 17:53
@gtwebb - is there a way to set the way refreshall() works in? Meaning, can I have the pivottable be the very last to refresh and ONLY begin after the mysql query is completed? — Habib Inman, May 11 '16 at 18:25
You wouldn't be able to set it within the refreshall command itself. Not sure how you refresh the SQL data but you could either set up a pause of a certain duration in the code or have another loop that runs until the rest of the updating is done. Also note refreshall won't resize the pivottable source data if more data is added. — gtwebb, May 11 '16 at 18:39
@gtwebb - I'll play with the wait function,thank you for that heads up, I have the pivot table set to a named range so it grows/shrinks based on the dataset returned. — Habib Inman, May 11 '16 at 19:40
I've also got this issue - pivot table with data source as a data table in another sheet. I change the query, refresh the data table connection (`connection.Refresh`) which successfully updates the data table, then call `PivotTable.PivotCache.Refresh` *and* `PivotTable.RefreshTable` after a long delay, but the pivot table stays on the previous data. Manually refreshing from the UI works fine. Single-stepping through the code also refreshes the pivot table. I've tried activating the worksheet first and setting `ScreenUpdating = True` first, but no help. — aucuparia, Oct 26 '16 at 11:23

score 0 · Answer 1 · edited May 23 '17 at 12:00

My problem with this was solved by setting BackgroundQuery to False in the related connection before refreshing it:

With ThisWorkbook.Connections(1).OLEDBConnection
    .BackgroundQuery = False
    .Refresh
End With
Pivot.RefreshTable

(replace OLEDBConnection with the appropriate connection object)

It appears that with BackgroundQuery set to True, the connection will not finish retrieving the data until the code has exited. You can see this by doing:

With ThisWorkbook.Connections(1).OLEDBConnection
    .BackgroundQuery = True
    .Refresh
    Do While .Refreshing
        DoEvents
    Loop
End With

This loop never terminates, and the status bar in Excel shows "RUNNING BACKGROUND QUERY (CLICK HERE TO CANCEL)"; clicking brings up a dialogue showing "All rows fetched.", but with the query still apparently running.

See also this answer - https://stackoverflow.com/a/25054870/3590073

Refresh Pivot Table

1 Answers1