VBA + Excel Web Query - Timing Issues
 

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

--
Message posted from http://www.ExcelForum.com

VBA + Excel Web Query - Timing Issues
 

is the backgroundquery parameter set to false?

--
Regards,
Tom Ogilvy


kenkozo wrote in message
...
I have created an excel file that web queries Yahoo Financial data,
dumps the data into an excel worksheet. I created a code that goes
through a list of stock prices -
1) copies a ticker
2) pastes the ticker into a cell that triggers the web query, which
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 to
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 the
program doesn't work when I run the macro because the macro runs faster
than the web query.

Does anyone have any suggestion as how to slow down the macro loop so
that it waits for the web query to update?
Thanks in advance, anyone who can figure this out can truly consider
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 the
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 Sub


---
Message posted from http://www.ExcelForum.com/

VBA + Excel Web Query - Timing Issues
 

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


---
Message posted from http://www.ExcelForum.com/

VBA + Excel Web Query - Timing Issues
 

How do you trigger the web query? What code?

--
Regards,
Tom Ogilvy

"kenkozo " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/