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?
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?
