refresh both the External data source and pivot tables together within a time schedule

excel pivot-table vba
user42995 picture user42995 · Apr 25, 2013 · Viewed 116.4k times · Source

In my last post Auto refresh pivottables data in excel on first run, i found that on my first execution the query from the External data source is refreshed and takes approximately 1 min to execute. and in my second run, the pivot tables are updated.

Is there a solution (VBA code) to refresh both the External data source and pivot tables together within a time schedule (If suppose we set a timer) by clicking command button?

Answer

Alex picture Alex · Apr 25, 2013

Under the connection properties, uncheck "Enable background refresh". This will make the connection refresh when told to, not in the background as other processes happen.

With background refresh disabled, your VBA procedure will wait for your external data to refresh before moving to the next line of code.

Then you just modify the following code:

ActiveWorkbook.Connections("CONNECTION_NAME").Refresh
Sheets("SHEET_NAME").PivotTables("PIVOT_TABLE_NAME").PivotCache.Refresh

You can also turn off background refresh in VBA:

ActiveWorkbook.Connections("CONNECTION_NAME").ODBCConnection.BackgroundQuery = False

Related questions

Filter Excel pivot table using VBA

I have tried copying and pasting solutions from the internet forever now to try to filter a pivot table in Excel using VBA. The code below doesn't work. Sub FilterPivotTable() Application.ScreenUpdating = False ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True ActiveSheet.PivotTables("…

Read More
How can I use VBA to add a Connection (to an External Data Source) in Excel and Save it to that Excel spreadsheet's list of Connections

I can use VBA to create a new ADODB.Connection and associated ADODB.Command and ADOBD.Parameter and then create a PivotCache and a PivotTable Sub CreatePivotTable() 'Declare variables Dim objMyConn As ADODB.Connection Dim objMyCmd As ADODB.Command Dim …

Read More
How to get min value in a group?

Example: ID Value MAX Group1 2 6 Group1 4 6 Group1 6 6 Group2 1 3 Group2 3 3 Group3 7 8 Group3 4 8 Group3 2 8 Group3 8 8 Group4 1 3 Group4 2 3 Group4 3 3 Group5 7 7 The column 'MAX' has the results I want. My two part question is: (1) How can I get the values for the 'Max' column? …

Read More