Refreshing External Data and Pivot Tables

[dotman]

Hi,

I have a excel report template which contains approx 9 pivot tables and 4 external data feeds from more than one access db. I am looking to automate production of this report so there is little or no input from the user.

So... I am looking to write some code which will refresh each of the pivots and external data ranges individually (not all at the same time as excel won't like that), in order and only once the previous has been succesfully refreshed. A vb message box would also be good to indicate to the user what is currently being refreshed.

Anyone have any ideas/suggestions or experience of doing anything similar?

 

[RoryA]

Assuming it doesn't matter what order the external data ranges are refreshed in:

Sub RefreshTheData()
    RefreshAllQueries
    RefreshAllPivots
End Sub
 
Sub RefreshAllQueries()
   Dim wks As Worksheet
   Dim qt As QueryTable
   For Each wks In ActiveWorkbook.Worksheets
       For Each qt In wks.QueryTables
           qt.Refresh False
       Next qt
   Next wks
End Sub

Sub RefreshAllPivots()
   Dim pc As PivotCache
   For Each pc In ActiveWorkbook.PivotCaches
      pc.Refresh
   Next pc
End Sub

Just run the RefreshTheData sub.

 

[dotman]

Thank you, seems to do the job nicley.