for next with web query

S

Spiro

Hello,
I'm new to vba and here is my problem. I would like to pull lots of
mutual fund information from yahoo. Using the fund screener only
brings up 20 funds per web page. After the first 20, subsequent pages
all have url's of the following format with only the last two
characters changing (eg 21, 41, 61, etc).

http://screen.yahoo.com/a?cc=1;&s=nm&db=funds&vw=0&b=21

How can I write a routine that will grab the tables on those pages
without doing it manually. I have tried, "For i=21 to (max number)
step 20" and "Next i", which loops the correct number of times, but
does not advance after each loop.

Thanks for your help!
 
D

Dick Kusleika

Spiro

Try something like this

Sub GetMutFunds()

Dim i As Long
Dim Destrng As Range
Dim qt As QueryTable
Const UrlStart As String =
"URL;http://screen.yahoo.com/a?cc=1;&s=nm&db=funds&vw=0&b="

Set Destrng = Sheet1.Range("A1")

For i = 21 To 101 Step 20
Set qt = Sheet1.QueryTables.Add(UrlStart & i, Destrng)
With qt
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

With Intersect(qt.ResultRange, Sheet1.Columns(1))
Set Destrng = .Cells(.Cells.Count).Offset(1, 0)
End With
Next i

End Sub
 
Top