Code skips refresh all

  • Thread starter Thread starter F Jones
  • Start date Start date
F

F Jones

Hello

I have posted my issue before but a colleague has the same issue. We have
macros which run some code including a refreshall. The refeshall does not
complete before the macro goes to the next step and this causes it to crash
or not work correctly.

what step could i put in the code so that it waits until the refresh is
complete before going onto the next bit. a time break would not work because
the refresh can take different amounts of time depending on where you are
refreshing it.

my code:

Sub Refresh_all_Button_Click()
ActiveWorkbook.RefreshAll
MsgBox ("Refresh is complete")
Range("k2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
End Sub

Thanks
 
Try putting a doevents after the refresh:

Sub Refresh_all_Button_Click()
ActiveWorkbook.RefreshAll
DoEvents
MsgBox ("Refresh is complete")
Range("k2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
End Sub

This may give the worksheet time to "catch up" before the rest of the macro
executes.
 
What are you refreshing?

Pivottables or Queries?

If queries, do you have the "enable background refresh" turned on?

If yes, then maybe turn them off (all of them) would help.
 
Its a table based on OLAP cubes so queries rather than pivot tables.
How do I switch off/on "Enable background refresh" in Excel 2007?
 
Try rightclicking on the query table cell and choosing properties.

That's the way it worked in xl2003.



F said:
Its a table based on OLAP cubes so queries rather than pivot tables.
How do I switch off/on "Enable background refresh" in Excel 2007?
Click to expand...
 
Back
Top