Updating Web Queries and setting Calculation to Manual

[Alan_42]

Using Excel 2003.

I have a workbook that uses a number of remote references by means of "Web Queries".

When I want to update the data I click the "Refresh All" button on the "External Data" toolbar.

This works fine except that the workbook recalculates after each data set refresh, so it all takes quite a while.

So - I have to set calculation to manual before updating and then back to automatic once updating has completed. This way recalculation only happens once. This works well.

It occurred to me that I could use a macro to do this and then replace the "Refresh All" button with a button to call my macro. So I tried the following:
Sub QuickRefreshAll()
Application.Calculation = xlCalculationManual
ActiveWorkbook.RefreshAll
Application.Calculation = xlCalculationAutomatic
End Sub

This didn't work as expected. Excel still recalculated after each data set refreshed.

Where have I gone wrong?

[Artik]

In the properties of all queries, disable background refreshing (this option is enabled by default). At the moment it's like this: you issue a command to switch the recalculation to manual, refresh the queries and before they have time to refresh you turn on automatic recalculation. When you turn off background refreshing for all queries, the execution of the macro will wait until the queries refresh and only then will automatic recalculation be turned on.

Artik

[Alan_42]

In the properties of all queries, disable background refreshing (this option is enabled by default). At the moment it's like this: you issue a command to switch the recalculation to manual, refresh the queries and before they have time to refresh you turn on automatic recalculation. When you turn off background refreshing for all queries, the execution of the macro will wait until the queries refresh and only then will automatic recalculation be turned on.

Artik

Brilliant! Many thanks Artik. That now works exactly as I wanted.
Alan