Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pausing vba for refresh
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pausing vba for refresh
In the absence of seeing any code it is hard to give a definitive answer but
you could try using DoEvents -- HTH... Jim Thomlinson "westg" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pausing vba for refresh
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 -- Regards, Sébastien <http://www.ondemandanalysis.com "westg" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pausing vba for refresh
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? "sebastienm" wrote: 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 -- Regards, Sébastien <http://www.ondemandanalysis.com "westg" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
pausing vba for refresh
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 "westg" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
pausing vba for refresh
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. -- Regards, Sébastien <http://www.ondemandanalysis.com "westg" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA macro pausing | Excel Worksheet Functions | |||
Pausing Macro | New Users to Excel | |||
Is there a way of pausing a macro | Excel Discussion (Misc queries) | |||
Pausing a Macro | Excel Discussion (Misc queries) | |||
pausing a macro | Excel Programming |