Hi All,

Hope you can help.

I have a number of workbooks with large numbers of sheets with MS Queries to
Access databases each also have a number of sheets with Pivot tables.

I have code to open each workbook, then refresh all the queries & then
refresh all the Pivots & then save the workbook

However when i run it i get the message "this action will cancel a pending
refresh data command" when it tries to send, obviously it has not finished
refreshing.

I have tried to add DoEvents in all over the place, it doesn't help, i
googled the error message & all i found was 'turn off the alert display'
which is great i don't get the message but it doesn't refresh half the
worksheets & pivots.

I don't want to build in a 'wait' time as some spreadsheets take seconds to
update and some take 10 minutes or more & i want the same code in each, so
each one of 30 odd spreadsheets would take 10mins * 30 (5 hours! instead of
the 30mins or so it should take.)

Please can someone help!
Emma


Sub Macro1()

Call UpdateQueryTablesInWorkBook

Call UpdatePivotTablesInWorkBook

ActiveWorkbook.Save

End Sub


'Updates all queries in the Workbook
Public Sub UpdateQueryTablesInWorkBook()
Dim s As Worksheet
Dim q As QueryTable

For Each s In ActiveWorkbook.Worksheets
For Each q In s.QueryTables
q.Refresh
Next q
Next s
End Sub

'Updates all Pivottables in the workbook
Public Sub UpdatePivotTablesInWorkBook()
Dim s As Worksheet
Dim p As PivotTable

For Each s In ActiveWorkbook.Worksheets
For Each p In s.PivotTables
With p.PivotCache
.Refresh
End With
Next p
Next s
End Sub