Refresh pivot tables but not external data source

Question

I have a spreadsheet with multiple tables, where the data is pulled from an external data source (SQL database). The connections/tables refresh by changing an option in a drop down box and then pressing a button to run a VBA.

Attached to each of these tables is a pivot table. The pivot tables don't refresh with the tables. If I try pressing refresh all I get the error;

'Data Source name not found and no default driver specified'

However if I go through the spreadsheet and hit refresh on each individual pivot table they update without the error.

So either I need some way to get the pivot tables to refresh with the tables or have a button that refreshes only the pivot tables and not the external data connections.

Any ideas appreciated, I don't know where to begin with this one!

Answer 1

You can refresh a given PivotTable on Sheet1 like this:

Sheet1.PivotTables(1).RefreshTable

That will refresh the first PivotTable on Sheet1. Change the index number for a different one.

Or...

You can refresh all of the PivotTables on a give sheet by calling this routine:

Sub RefreshPivotTables(ws As Worksheet)
    Dim pt As PivotTable
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next
End Sub

You would call the above routine from the same code associated with the button mentioned in your question that updates the tables.

Or...

If you'd like to update all of the PivotTables in a workbook, you can use this version of the routine:

Sub RefreshPivotTables(wb As Workbook)
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next
    Next
End Sub

You would call this version like so:

RefreshPivotTables ThisWorkbook

Answer 2

Becky: Any reason you don't populate those PivotTables directly from the SQL query? Unless you need those tables there for some reason I'd suggest just ditch 'em, and simply turn the data directly into PivotTables. Otherwise you're effectively saving the same data in the file twice. (Or three times, if you haven't unchecked "Save source data with file" under PivotTable>Data>Options.

If you do need to refresh them - and if there are multiple PivotTables connected to each Table - then it is more efficient to iterate through the underlying PivotCaches and refresh any where the sourcetype is an Excel Range. In VBA speak that's where pc.SourceType = xlDatabase

Sub Refresh_PivotCaches()
Dim pc As PivotCache
For Each pc In ActiveWorkbook.PivotCaches
    If pc.SourceType = xlDatabase Then pc.Refresh
Next pc
End Sub

If you instead iterate through each and every PivotTable, then if multiple PivotTables are connected to one PivotCache you end up doing more refreshes than you need. For instance, if you have 10 PivotTables that all point to the same table, you do not need to refresh those 10 PivotTables individually. Rather, you just need to refresh the one PivotCache that they all share. If you were to refresh those 10 PivotTables individually, then in effect you are refreshing each of those 10 PivotTables 10 times.

Granted, if your PivotTables are small you won't notice any difference between my code and Excel Heros.

Answer 3

I added the following at the end of my code which seemed to work fine.

Dim PT As PivotTable
Dim WSH As Worksheet

For Each WSH In ThisWorkbook.Worksheets

    For Each PT In WSH.PivotTables
      PT.RefreshTable
    Next PT

Next WSH

Answer 4

I know this is an old post, but sharing in case this helps anyone researching this like I was -- This may be new to Excel 2016 (I don't have access to older versions currently to test), however I found that you can disable the "Refresh with Refresh All" setting on your external data sources. This will then allow you to use the "Refresh All" from the Data Tab to update all your pivot tables at once, without updating the external data source query again:

1. Data Tab > Connections Section > Open Connections
2. Select a connection in the Workbook Connections window and click Properties...
3. In the properties window, uncheck the box for "Refresh this connection on Refresh All"
4. Repeat for any additional External Connections you don't want to update automatically

I use this in a reporting macro by calling the manual query updates once: (selecting a cell in each table created by a query)

ActiveWorkbook.Sheets("Completed").Select
Range("A2").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

And then calling refresh all as needed to update my pivot tables:

ActiveWorkbook.RefreshAll