Stop Refreshing via VBA

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.
 
J

JP

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
 
K

ken

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top