This action will cancel a pending refresh data command

Question

While I am trying this code I am getting the error "This action will cancel a pending refresh data command.continue?"

ActiveWorkbook.RefreshAll

Even if I put Application.Wait now() + TimeValue("00:01:20") Excel is not refreshing properly.

Answer 1

Solution: "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"." ([email protected] , pcreview, 2013).

This error may be caused by Excel being overloaded but only capable of reporting in this way. After trying all of the suggestions about 'DoEvents', 'WAIT'/SLEEP', 'FOR/NEXT' looping and opening each page to refresh singularly, I found, from PCReview website, that the solution is in the Data Connection properties and not VBA.

Once all background refresh connections have been disabled, the simple 'Activeworkbook.RefreshAll' command will suffice to update the entire workbook; mine was 29 pages large with 5 data connections and 7 SQL data connections.

NB: It was only the SQL data connections which allow the refresh to be disabled.

Hope that this helps with anyone having a headache over this issue as I did.