Code skips refresh all

[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

 

[Gary''s Student]

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.

 

[Dave Peterson]

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.

 

[F Jones]

Still the same issue... the message box pops up almost straight away.

 

[F Jones]

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?

 

[Dave Peterson]

Try rightclicking on the query table cell and choosing properties.

That's the way it worked in xl2003.

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?