• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Updating Web Queries and setting Calculation to Manual

Alan_42

New Member
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:
>>> use code - tags <<<
Code:
  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?
 
Last edited by a moderator:
Hi,

try this one

Code:
 Sub QuickRefreshAll()

    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False

    ActiveWorkbook.RefreshAll

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = True

 End Sub

Regards
 
Hi,

try this one

Code:
Sub QuickRefreshAll()

    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False

    ActiveWorkbook.RefreshAll

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = True

End Sub

Regards
Thanks for that idea Frank. Didn't work though.
 
Back
Top