Results 1 to 4 of 4
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Waiting for Queries (XL2003)

    I've got an Excel file that uses MS Query (4 queries) to get some data from an Access database. After the queries get refreshed the Excel file takes about 1 minute to recalculate. Now, this is done for many policies so I have a macro that loops through the policy list. Once a policy # is entered the MS Queries are refreshed and then the Excel file needs to calculate before I paste-value a score into the policy list.

    What I'm having trouble with is having the macro wait until the Excel file has refreshed and recalculated before I paste the value. I'm trying to use calculation status but no luck yet. I couldn't find an AfterRefresh or Refresh status property for the MS queries either.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

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

    Re: Waiting for Queries (XL2003)

    The QueryTable object has an AfterRefresh event. You could use it to set a public variable or the value of a cell. Your code could look periodically whether the value of the variable or cell has changed.
    In order to use AfterRefresh, you must create a class module and define a variable of type QueryTable with the WithEvents keyword. See the help for AfterRefresh and click See Also.

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

    Re: Waiting for Queries (XL2003)

    You may also be able to use the Refreshing property of the QueryTable object - it is True as long as the underlying query is being executed.

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Waiting for Queries (XL2003)

    Thx Hans - I will check that out.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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