Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Pause during Dialog Box data entry | Excel Discussion (Misc queries) | |||
getting VBA to pause while external data loads from web | Excel Programming | |||
Pause Macro to enter data in cell | Excel Programming | |||
Pause Macro to enter data in cell | Excel Programming | |||
Code to return the data a chart is based on | Excel Programming |