Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with a sub getting ahead of itself.
Hello all,
I have a sub called 'refreshQuery' which is supposed to refresh all the web queries in the workbook and then after the refresh has imported fresh data, it's supposed to copy a range and insert the copied cells by shifting the existing cells right. This copying and shifting is to capture a timeline of financial data, with the idea of creating graphs later in the day. Now I'm happy with the component parts of this sub. The copying and pasting of data is fine and works well. The problem being is that if I incorporate the refresh code ActiveWorkbook.RefreshAll into the full sub, the code always gets ahead of itself and copies/pastes before the web query has refreshed the actual data. I have the situation now where I can hit the command button in the sheet and I get the desired copy/paste and then the web query sort of limps along a second or so behind. Here's the simple code that I have. Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) (to use this, simply insert sleep 500 or whatever in miliseconds) Sub refreshQuery() Range("B1").Select 'ActiveWorkbook.RefreshAll this has been commented as _ 'its not functioning correctly Sleep 1000 ' this is what I tried to create a pause, got this _ ' code from this newsgroup, thanks to the anon creator timeStamp 'this sub puts a timestamp on every refresh. insertValues 'this sub is the one that copy & pastes the data _ 'preferably after the web query refresh Range("B1").Select End Sub Even using the Sleep Sub, after the desired pause the code still got ahead of the refresh. Is there a simple method to actually make my code wait until the refreshed values are in place? At the moment I'm manually clicking the refresh all button on the excel toolbar, then clicking the command button to run the above code. As I'm also trying to monitor prices 'live' I could do without the hassle of hitting the second button after watching for the refresh to complete. Thanks in advance, Ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with a sub getting ahead of itself.
Hi Ron
Usually DoEvents will allow initialized processes to finish before continuing. (But not always, like while you wait for a database to respond.) Sleep may afaik sometimes ensure than nothing at all happens. That may not be helpful. There is also a Wait in Excel VBA, it freezes almost everything, which may or may not be what you want: Sub test() MsgBox "Go" Application.Wait Now + TimeSerial(0, 0, 3) MsgBox "ok" End Sub HTH. best wishes Harald "Ron" skrev i melding 10.205... Hello all, I have a sub called 'refreshQuery' which is supposed to refresh all the web queries in the workbook and then after the refresh has imported fresh data, it's supposed to copy a range and insert the copied cells by shifting the existing cells right. This copying and shifting is to capture a timeline of financial data, with the idea of creating graphs later in the day. Now I'm happy with the component parts of this sub. The copying and pasting of data is fine and works well. The problem being is that if I incorporate the refresh code ActiveWorkbook.RefreshAll into the full sub, the code always gets ahead of itself and copies/pastes before the web query has refreshed the actual data. I have the situation now where I can hit the command button in the sheet and I get the desired copy/paste and then the web query sort of limps along a second or so behind. Here's the simple code that I have. Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) (to use this, simply insert sleep 500 or whatever in miliseconds) Sub refreshQuery() Range("B1").Select 'ActiveWorkbook.RefreshAll this has been commented as _ 'its not functioning correctly Sleep 1000 ' this is what I tried to create a pause, got this _ ' code from this newsgroup, thanks to the anon creator timeStamp 'this sub puts a timestamp on every refresh. insertValues 'this sub is the one that copy & pastes the data _ 'preferably after the web query refresh Range("B1").Select End Sub Even using the Sleep Sub, after the desired pause the code still got ahead of the refresh. Is there a simple method to actually make my code wait until the refreshed values are in place? At the moment I'm manually clicking the refresh all button on the excel toolbar, then clicking the command button to run the above code. As I'm also trying to monitor prices 'live' I could do without the hassle of hitting the second button after watching for the refresh to complete. Thanks in advance, Ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with a sub getting ahead of itself.
you need to edit your queries and change the Backgroundquery property to
false. Then your macro will wait until they are completed. -- Regards, Tom Ogilvy "Ron" wrote in message 10.205... Hello all, I have a sub called 'refreshQuery' which is supposed to refresh all the web queries in the workbook and then after the refresh has imported fresh data, it's supposed to copy a range and insert the copied cells by shifting the existing cells right. This copying and shifting is to capture a timeline of financial data, with the idea of creating graphs later in the day. Now I'm happy with the component parts of this sub. The copying and pasting of data is fine and works well. The problem being is that if I incorporate the refresh code ActiveWorkbook.RefreshAll into the full sub, the code always gets ahead of itself and copies/pastes before the web query has refreshed the actual data. I have the situation now where I can hit the command button in the sheet and I get the desired copy/paste and then the web query sort of limps along a second or so behind. Here's the simple code that I have. Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) (to use this, simply insert sleep 500 or whatever in miliseconds) Sub refreshQuery() Range("B1").Select 'ActiveWorkbook.RefreshAll this has been commented as _ 'its not functioning correctly Sleep 1000 ' this is what I tried to create a pause, got this _ ' code from this newsgroup, thanks to the anon creator timeStamp 'this sub puts a timestamp on every refresh. insertValues 'this sub is the one that copy & pastes the data _ 'preferably after the web query refresh Range("B1").Select End Sub Even using the Sleep Sub, after the desired pause the code still got ahead of the refresh. Is there a simple method to actually make my code wait until the refreshed values are in place? At the moment I'm manually clicking the refresh all button on the excel toolbar, then clicking the command button to run the above code. As I'm also trying to monitor prices 'live' I could do without the hassle of hitting the second button after watching for the refresh to complete. Thanks in advance, Ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with a sub getting ahead of itself.
Thanks for both swift replies, but Tom, I take my hat off to you.
Superb Eternally grateful, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ahead or behind the average | Excel Discussion (Misc queries) | |||
Type Ahead within an excel dropdown | Excel Discussion (Misc queries) | |||
Can a cell's text order be ahead of a drawn item? | Excel Discussion (Misc queries) | |||
No drop down list type-ahead? | Excel Discussion (Misc queries) | |||
Calculate due date 1 or 2 years ahead to the first of the followi. | New Users to Excel |