Problems with a sub getting ahead of itself.

[Ron]

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

 

[Harald Staff]

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

 

[Tom Ogilvy]

you need to edit your queries and change the Backgroundquery property to
false. Then your macro will wait until they are completed.

 

[Ron]

Thanks for both swift replies, but Tom, I take my hat off to you.

Superb


Eternally grateful,

Ron