Refresh Pivot Tables after updating external data 1

[DrSmyth]

I've got an Excel Workbook that has an external data source and a few pivot tables based on the external data. Both the pivot tables and external data source are set to refresh when the workbook is opened.

The problem I have is that the pivot tables are refreshing before the external data source, so they are always out of date.

Is there a way of refreshing the pivot tables after the external data has been refreshed?

DrS

 

[xlbo]

Only through code I would think

Make sure that the BACKGROUNDQUERY property of the external data query is set to FALSE and the following should work in the workbook OPEN event

for each sht in thisworkbook.worksheets
 for each qt in sht.querytables
   qt.refresh
 next
next

for each sht in thisworkbook.worksheets
for each pt in sht.pivottables
   pt.refreshtable
 next
next

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DrSmyth]

Cheers Geoff, I'll give it a go.

Andy

 

[DrSmyth]

Once again Geoff, you have saved the day.... Works like a dream.... Think you should consider changing your handle to SuperXL...

Anyway a star is winging it's way to you...

Just one thing that puzzled me though, you've used terms such as sht and qt and pt.... couldn't find any reference to them in the object browser, how does this work????

 

[xlbo]

Andy - they are just variable names - defined by the context that they are in

eg
for each sht in thisworkbook.worksheets

the "in thisworkbook.worksheets" tells excel that the collection to be iterated through is the worksheets collection.

Sht is then set as the placeholder for each item in that collection

If you turn "Option Explicit" on, the previous code I posted won't compile as it uses VBA "smarts" to determine the variable type (identified by the collection)

A more proper version of the code would be:

Option Explicit
Sub test()
Dim sht As Worksheet, qt As QueryTable, pt As PivotTable
For Each sht In ThisWorkbook.Worksheets
 For Each qt In sht.QueryTables
   qt.Refresh
 Next
Next

For Each sht In ThisWorkbook.Worksheets
For Each pt In sht.PivotTables
   pt.RefreshTable
 Next
Next
End Sub

HTH

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DrSmyth]

Cheers, Geoff, you learn something new everyday...

Dr S