Waiting for Queries (XL2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Thx Hans – I will check that out.

Reply | Quote

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.

Reply | Quote