VBA + Excel Web Query - Timing Issues

[kenkozo]

I have created an excel file that web queries Yahoo Financial data
dumps the data into an excel worksheet. I created a code that goe
through a list of stock prices -
1) copies a ticker
2) pastes the ticker into a cell that triggers the web query, whic
then causes the values in the row to change
3) copies the updated row
4) pastes the row below
5) created a loop that goes back to step 1) above, which allows me t
go through my list of stocks, and fills a nice table below.

The code works when I "step through it" with the VBA editor. But th
program doesn't work when I run the macro because the macro runs faste
than the web query.

Does anyone have any suggestion as how to slow down the macro loop s
that it waits for the web query to update?
Thanks in advance, anyone who can figure this out can truly conside
themselves an excel/vba genius.

here is the code---------------------------------------------------

Sub datagrab()

'declaring variables
'Dim r As Integer
'Dim nr As Integer
'nr = ActiveSheet.Range("tickers").Rows.Count

'from the datapage, clear the contents
Range("C7:e11").Select
Selection.ClearContents

i = 0
For r = 1 To 5
i = i + 1

'in the datagrab: copy the ticker to the cell that links to th
webqueries
Sheets("datagrab").Select
Range("b6").Select
Selection.Offset(i, 0).Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

'from the webqueries page: copy data and paste back into the datapage
ActiveWorkbook.RefreshAll

Sheets("datagrab").Select
Range("C5:E5").Select
Range("c5:e5").Activate
Application.CutCopyMode = False
Selection.Copy

'from the datapage, copy the updated data into the ticker row
Range("C7:e7").Select
Range("c6").Offset(i, 0).Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlAdd
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Next r

End Su

 

[Tom Ogilvy]

is the backgroundquery parameter set to false?

 

[kenkozo]

Tom,

where can I find the backgroundquery parameter?

I don't see anything in the parameters box.

In the External Data Range Properties box, I see a checkbox for Enable
Background Refresh, is this relevant?

Thanks alot,

K

 

[Tom Ogilvy]

How do you trigger the web query? What code?