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.

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

Cordially
Pascal

"John V" a écrit dans le message de news:
...
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.

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

On Sep 23, 5:11*pm, John V wrote:
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.

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