this action will cancel a pending refresh data command

[Emma Hope]

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

 

[FSt1]

hi
i think you are overloading the system. try this

For Each s In ActiveWorkbook.Worksheets
For Each q In s.QueryTables
q.Refresh Backgroundquery:= False
Next q
Next s

from experiece i have found that if you are refreshing only 1 query and
doing nothing after, then a backgroud query doesn't matter.
but if you are refreshing many querys AND allowing code to run while the
refresh is in process, then this can cause some problems.
not sure what is generating your specific error but gut feeling is telling
me you've hit a wall and excel is throwing up a "related" error when the real
error is overload.
try removing the background query ie "refresh Backgroundquery:=false" and
see what happens. code will (or should) pause untill each query has been
refreshed.

regards
FSt1

 

[tylerlohman]

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

This doesn't fix your problem with VBA code but it works. Go to the data tab, click connections in the connections group, click on EVERY connection and click properties, un-check the box that says "enable background refresh".

 

[LeonK]

@Tyler,

Many thanks for taking the trouble to post this solution. I've been wrestling with this issue for a week or so now and went down the 'WAIT/SLEEP' and 'FOR EACH s/q' routes to no avail.

My Excel 2010 model has 29 worksheets, uses 7 SQL data connections and 5 fact datasource connections, 2 of the SQL scripts create additional tables from the five original facts. The other 5 SQL queries create metrics for use in the calculations within the pivots and tables and sourced by the facts.

After resetting every connection as you described, I was able to refresh the entire model with 'Activeworkbook.RefreshAll'. This one command did away with all of the workaround code to refresh specific tables.