pausing vba for refresh

[Guest]

I setup a query on an sql db. However I need the vba to pause while the query
refreshes and brings the data into Excel.

So far I have tried using
Application.Wait Now + TimeSerial(0, 0, 10)

However if a user is daft enough to run a query on huge amount of data, i am
screwed.

is there a way of pausing until the data has finished refreshing?

Many thanks, Andrew

 

[Guest]

In the absence of seeing any code it is hard to give a definitive answer but
you could try using DoEvents

 

[Guest]

Hi,
not sure what you are using to run the query; excel's QueryTable (vba
equivalent to menu Data > Extrernal Data) or direct ADO?
If you are using the QueryTable/External Data, the Refresh method has a
Background query parameter. Just set it to False to have the code pause and
wait for the query to return:
Dim qt as QueryTable
... set qt...
qt.Refresh False

 

[Guest]

Hi Sebastien,

Sorry I am using Query Tables, but you don't want to see the code, it is
huge and i am no programmer so it won't make a lot of sense!

Unfortunately I have had to stop using the Background false method because
it caused other problems in the program (sql syntax error).

Any ways around it?

 

[Guest]

i have had a brainwave, if i looped around this code until it had finished
refreshing, would that work???
With Worksheets(1).QueryTables(1)
If .Refreshing Then
MsgBox "Query is currently refreshing: please wait"
Else
.Refresh BackgroundQuery := False
.ResultRange.Select
End If
End With

 

[Guest]

it caused other problems in the program (sql syntax error).
Method1. I remember getting errors on the refresh when using Backgroundquery
= False...
It was when i would get the code from the macro recorder in the first place.
To get rid of the error, i had to comment out some lines of auto-generated
code; i believe it was some of the bellow lines (probably the SavePassword
and the SaveData) :
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
then i could run :
.Refresh BackgroundQuery:=False
Sorry i cannot rememer more than that but it was years ago.

Method2.
there is another way, but it is much more involved. It requires crreating a
Class contining a querytable member declared with the WithEvents option so
you can capture the BeforeUpdate and AfterUpdate events of the query table.
Then in your regular code module code, use an instance of that class, run the
refresh and capture the AfterUpdate event.
.... i would personaly with the first method i first described, if possible.

Method3. Use ADO, but it probably requires major changes of your current
code... may be a good method in your case, may be not...
Still, i would method 1 first.