for next with web query

[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!

 

[Don Guillett]

You could try this. Untested but this is the idea

i=1
http://screen.yahoo.com/a?cc=1;&s=nm&db=funds&vw=0&b=i
get your data to a data page>copy data to the main
i=i+20
loop

If you need a custom design, let me know privately.

 

[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