Importing Specific Data From Multiple Pages From The Web

[qcan]

Hi,

I am looking for some fancy code that would import data from a
specific site from mutiple pages from within the site. I recorded one
page using the macro recorder, then I went into the code and
manipulated it somewhat, but it is still too manual and to combursome
to work with from a practical point of view.

- The site is www.covers.com
- I want to downlaod all past scorer results for each NCAAB team.
- All the teams can be found at:

http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/ncb/teams/teams.html

- There are over 300 teams.
- Each team has a specific page which shows a specific year's results.
I beleive it currently goes as far back as the 1997-1998 season. So
that at least 10 pages per team.

Example of 1 team for a specific year:

http://www.covers.com/pageLoader/pa...ncb/teams/pastresults/1997-1998/team2518.html

- Ideally I would like a macro to import 50 teams at a time using
their specific team codes to identify the teams. Duke for example =
2518. So I would need an option to enter in the team # first, or if
it's too much code, I can go into the code itself and change it
manually. Output would be the page data + the last cell of each row
would contain the name of the team.

Thanks you.

 

[ryguy7272]

Just looked at your URL. The first team I see is the UK Wildcats. When I
click on that the URL is
http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/ncb/teams/team2228.html

Notice the 2228.

Then next link is for SU (my alma mater, well one of several), and the URL is
http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/ncb/teams/team2186.html

Notice the 2186

The next link is for OK and the URL is
http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/ncb/teams/team2243.html

Notice the 2243…see the pattern here. I can’t tell where those 4-digit
numbers are coming from, but if you can find out and put them in a list, in
let’s say Column A, you can easily create a sub that loops through each item
in the list as it does the imports of all the data that corresponds to those
4-digit numbers.

Notice my macro for doing something similar to what you are doing (except I
am importing historical stock data):

Sub HistData()

Application.ScreenUpdating = False

Dim str1 As String
Dim c As Range
Dim Stocks As Range
Dim bFound As Boolean
Dim ws As Worksheet

Set Stocks = Application.InputBox( _
"Type 'Symbols' in the input box below", Type:=8)

For Each c In Sheets("Firms, Import").Range("Symbols") ‘< -- the data list
is here, my stock symbol list, your 4-digit codes, etc.

bFound = False
For Each ws In Worksheets
If ws.Name = c.Value Then
bFound = True
Exit For
End If
Next ws

If bFound = False Then
Worksheets.Add.Name = c.Value ‘< -- This creates a new worksheet for
each variable and names the sheet the name of the variable

End If

Sheets(c.Value).Select
Cells.Select
Range("A1:IV65536").ClearContents

str1 = "URL;http://finance.yahoo.com/q/ks?s=" & _
c.Value ‘< -- this c.Value is the variable that changes, i.e. stock
symbol list, your 4-digit codes, etc.

With ActiveSheet.QueryTables.Add(Connection:=str1 _
, Destination:=Range("A1"))

.Name = str1

.Name = "ks?s=c.Value" < -- again, notice the variable; when you
record your macro this part will be totally different…you have to choose the
appropriate web tables to import…

.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

End Sub

Just record a macro as you import the data from the web. This will get you
most of what you need. Then, use the first part of this code:
From - Sub HistData()
To - Range("A1:IV65536").ClearContents

Then your recorded code comes in here.

Then this:

Next c
End Sub

Regards,
Ryan--