for next with web query
 

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%3B&s=...unds&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!

for next with web query
 

You could try this. Untested but this is the idea

i=1
http://screen.yahoo.com/a?cc=1%3B&s=...funds&vw=0&b=i
get your data to a data pagecopy data to the main
i=i+20
loop

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

--
Don Guillett
SalesAid Software

"Spiro" wrote in message
om...
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%3B&s=...unds&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!

for next with web query
 

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%3B&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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Spiro" wrote in message
om...
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%3B&s=...unds&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!