Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pausing a Macro (2000)

    The very first thing my macro does is refresh all of the MS Queries using RefreshAll but while it is refreshing the macro continues on. How do I get it to wait until the refreshing is finished before continuing? I tried the "Wait" method but that pauses the refreshing as well.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing a Macro (2000)

    Insert this function in your module and call it any time it's needed. It pauses continuation of the code for the number of seconds you stipulate. I originally took it from am old Winfax code in Word and now use it quite a bit.
    Function Pause(ByVal nSecond As Single)
    Dim t0 As Single
    t0 = Timer
    Do While Timer - t0 < nSecond
    Dim dummy As Integer
    dummy = DoEvents()
    If Timer < t0 Then t0 = t0 - 24 * 60 * 60 ' if we cross midnight, back up one day
    Loop
    End Function

    Peter

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing a Macro (2000)

    Thank you very much, it works great!!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing a Macro (2000)

    It may cause less overhead to set the BackgroundQuery property to false for those queries.
    AFAIK you then do not need the timer (not tested).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •