Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pause till query executes (2003)

    Hi, part of my code runs the following command:
    ActiveWorkbook.RefreshAll
    which refreshes a link to a database. Then the code does other stuff, however the code rushes past this command, and executes the other stuff before the refresh has completed, hence the code is being performed on non-updated data.
    How can I pause the execution of the code until the refresh is complete?
    Thanks for your help.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: pause till query executes (2003)

    Hi there

    You could use:

    Application.Wait Now + TimeValue("00:00:10")

    Application.StatusBar = "Please wait while performing Link update..."

    Which would pause the code for 10 seconds and also place a message in the status bar
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pause till query executes (2003)

    If you have a single link to a database, you can use

    Worksheets("SheetName").QueryTables(1).Refresh BackgroundQuery:=False

    If you have several, you can use

    Dim wsh As Worksheet
    Dim qtb As QueryTable
    For Each wsh In ActiveWorkbook.Worksheets
    For Each qtb In wsh.QueryTables
    qtb.Refresh BackgroundQuery:=False
    Next qtb
    Next wsh

    Specifying BackgroundQuery:=False makes the code wait until the data have been updated. Unfortunately, RefreshAll doesn't have a BackgroundQuery parameter, it uses the setting of each querytable.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pause till query executes (2003)

    If you do it in that order, You won't get the message in the Status bar until after the 10 seconds are up.
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: pause till query executes (2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Now you say that it is pretty obvious <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

Posting Permissions

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