Stop Refreshing via VBA

  • Thread starter Thread starter John V
  • Start date Start date
J

John V

I have several web queries that auto-refresh every X minutes or so. I would
like to craft a macro that temporarily suspends refreshing so, for example, I
can edit the spreadsheet without interruption. And, of course, I'd need a way
to un-suspend. I didn't know if this was possible short of manually changing
the refresh parameter in each query.

Any ideas appreciated.
 
I am not an expert . better solutions may be available

if the auto refreshing is done by an EVENT code then in the immediate window you you can type

application.enableevents=false
and hit enter at the end of the line
now event code is not ooperable
After your edit y do not forget to add another line in the immediate window
application.enableevents=true
so that event code is operable.
 
If it's a QueryTable, there are a few things you can do
programmatically.

Set RefreshPeriod Property to zero (temporarily)
Set the Refresh Property BackgroundQuery parameter to False.

HTH,
JP
 
G'day there John V,

I've never needed to interrupt auto-refresh myself, but your question
raised my interest so I had a quick browse of XL's Help pages.

There are several methods & properties to control the auto-refresh
including CancelRefresh; RefreshAll; EnableRefresh; and probably of more
use is the RefreshPeriod property.

I'm sure you could construct somethnig to turn it on & off especially
with the latter. Help gives as a description:

------
Setting the period to 0 (zero) disables automatic timed refreshes and
is equivalent to setting this property to Null.

The value of the RefreshPeriod property can be an integer from 0 through 32767.
-----

I think that may be worth looking at.

See ya
Ken McLennan
Qld Australia
 
Back
Top