Pause code for data return
 

I currently have several worbooks that run queries against a database and
create reports. I want to open and loop through the workbooks using another
workbook to open and run a grouped reporting macro. Data is brought into
Excel by the statement " With
ActiveSheet.QueryTables.Add(Connection:=ConnectStr ing,
Destination:=Range(DataTarget), Sql:=mySQL)
.Refresh (BackgroundQuery = False)
End With"
This is a part of a three step grouped code (GetData, CreateReports,
PublishReports). The CreateReports code does not wait for the data to return.
How can I force the code to pause during this data return?

Pause code for data return
 

QueryTables have a Refreshing property to let you know if they are still
refreshing, so a loop like the one below can pause until results are returned:

While MyQueryTable.Refreshing
DoEvents
Wend

This is bare-bones; I usually add a time-out period and you can also add
checks of the system status to prevent getting stuck in an endless loop. The
DoEvents ensures that your code is not hogging the processor!

"Candyman" wrote:

I currently have several worbooks that run queries against a database and
create reports. I want to open and loop through the workbooks using another
workbook to open and run a grouped reporting macro. Data is brought into
Excel by the statement " With
ActiveSheet.QueryTables.Add(Connection:=ConnectStr ing,
Destination:=Range(DataTarget), Sql:=mySQL)
.Refresh (BackgroundQuery = False)
End With"
This is a part of a three step grouped code (GetData, CreateReports,
PublishReports). The CreateReports code does not wait for the data to return.
How can I force the code to pause during this data return?

Pause code for data return
 

BackgroundQuery is the parameter which tells the querytable to wait while
refreshing.

You've specified it, but you have a syntax error.
Try:
..Refresh BackgroundQuery:=False

To avoid this type of bug in the futu from VBA Tools menu, select
Options. Tick the "Require Variable Declaration" box. It adds Option
Explicit to the top of code modules.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Candyman" wrote in message
...
I currently have several worbooks that run queries against a database and
create reports. I want to open and loop through the workbooks using
another
workbook to open and run a grouped reporting macro. Data is brought into
Excel by the statement " With
ActiveSheet.QueryTables.Add(Connection:=ConnectStr ing,
Destination:=Range(DataTarget), Sql:=mySQL)
.Refresh (BackgroundQuery = False)
End With"
This is a part of a three step grouped code (GetData, CreateReports,
PublishReports). The CreateReports code does not wait for the data to
return.
How can I force the code to pause during this data return?