query refresh question

[Gary Keramidas]

i have some code that loops to do multiple queries.
i just use ActiveWorkbook.RefreshAll to refresh with the current parameters.
just have trouble because the code continues running before the query is
finished.

what's the best way to determine when the query is done so i can manipulate
the data before the next query runs?

 

[FSt1]

hi
the freshall command allows for a background querry and it has been my
experience never to allow a background query perticularly if you are going to
be manipulating the query data with more code. the code could start
manipulated data before it is refreshed ie processing old data.
what i always do is refresh each query one at a time by referencing the
query data range.
Application.StatusBar = "refreshing query1"
sheets("sheet1"). range("A1").querytable.refresh Backgroundquery:=False
Application.StatusBar = "refreshing query2"
sheets("sheet2"). range("A1").querytable.refresh Backgroundquery:=False
Application.StatusBar = "refreshing query3"
sheets("sheet3"). range("A1").querytable.refresh Backgroundquery:=False
Application.StatusBar = ""

msgbox "I'm done refreshing now"

more code here.

regards
FSt1

 

[FSt1]

hi
forgot to mention. not allowing a background query will add so time to the
refresh process but we are not talking hours or even minutes. more like
seconds. the exact time varies depending on how much data you are bringing,
network traffice, other, etc. but it's not a great deal.

Regards
FSt1

 

[Gary Keramidas]

thanks, i changed activeworkbook.refreshall to a cell in the query's range
and added the background refresh code and it seems to work.
ws.Range("A6").QueryTable.Refresh BackgroundQuery:=False