Stop Refreshing via VBA

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

 

[venkat1926]

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.

 

[papou]

Hi John
Is your web query linked to query tables in your workbook?

Cordially
Pascal

 

[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

 

[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