Web Data Query Possibilities

Topic

New Reply

My son is playing a lot of tennis at an academy. All the students at the academy check their Junior Ranking which changes at least on a weekly basis based on tournaments played by themselves and others. I have written a .php web site to display the rankings but I have to look up all the data and enter it into a web page so it will update my database. I’m thinking there might be a way to query the rankings pages with a Web Query and store the data in Excel. I could have a list of all the players and then run a query for each one and put the latest ranking into my spreadsheet. A second step would be to run a routine that would take the new data and upload it to my database. Is all of this possible or too difficult to tackle from within Excel? I contacted the people that post the rankings and they do not provide a web service or any way to make it easy for the public to access the information. All the data is there and accessible to me but it involves looking up each player individually. Also, some of them have several rankings (State, Regional, National, in several age groups (12, 14, 16, 18) so it is a lot of work. I would love to figure out how to automate the process. Thanks for any suggestions you may have.

Here is a URL to see what I’m looking at. This info can be sorted by names.
https://tennislink.usta.com/tournaments/rankings/rankinghome.aspx?Section=70&Division=G8&intloc=headernavsub2#&&s=4%5cPage_RankingList%5cListID_1526012%5cPlayerID_Vd27wk%2fgM0sSc4NBLEsYOA%3d%3d%5cYear_%5cType_viewranklist

Reply | Quote

Replies

That page seems to send all the data as a block and then decodes it using JS in your browser, so a straight scrape is tricky. What does seem to work is to save the page as text and then scrape. You can do this from your web site and save the data, then you can export it from the web site if you require.

Sample data saved as text.

Code:

Rank 	Name
	City 
State 	Section
	District
	Points 
1 	Berry, Ethan

	Alpharetta 	GA 	Southern 	Georgia 	5339
2 	Dunac, Daniel

	Douglasville 	GA 	Southern 	Georgia 	4847

cheers, Paul

Reply | Quote

A less automatic approach would be to highlight all the player information then do a simple copy/paste into a spreadsheet (see image below). Manipulating the data would be easy from there.

Could you post a sample of the format the data would need to be in to upload it to your database?

41231-tennis

Reply | Quote

I could just do an INSERT statement with the fields I need from there. The trouble is, there are several of those web pages for each age group and each region (Georgia (in my case), Southern, National). If I could automate the gathering of the data and pasting it into Excel, I could write a macro from there to create my INSERT statements. I’ll keep thinking about this. It isn’t something I have to do right now, just a long term project for my own sense of accomplishment. Thanks for your ideas.

Reply | Quote

Another way would be to visit the site, make your selections, then use the site’s Print Record button.
This will provide a list in an Explorer window. Use Explorer’s File>Save As
(The default filename comes up as Ranking List.htm)
You can then open this file directly in Excel2010 etc etc etc.

Once you have the data in Excel, you can then use Index/Match and lookup functions to drill into the data etc etc etc

zeddy
Superintendant In Charge of Big Door
.

Reply | Quote

I will give that a look. Thanks for your suggestion.

Reply | Quote

JP,

The following code will open Internet Explorer with your Tennis website, extract all the data, place it in the active sheet starting at A1, close internet Explorer, then format the sheet so it looks like the image I posted in post #3. Repeat the code for each ranking list.

HTH,
Maud

Place in a standard module:

Code:

Sub GetRankings()
Application.ScreenUpdating = False
On Error Resume Next
[COLOR=”#008000″]’———————————–
‘OPEN IE, SELECT DATA, AND COPY[/COLOR]
    Set myIE = CreateObject(“InternetExplorer.Application”)
    myIE.Navigate “https://tennislink.usta.com/tournaments/rankings/rankinghome.aspx?Section=70&Division=G8&intloc=headernavsub2#&&s=4%5cPage_RankingList%5cListID_1526012%5cPlayerID_Vd27wk%2fgM0sSc4NBLEsYOA%3d%3d%5cYear_%5cType_viewranklist”
    myIE.Visible = True
    Application.Wait (Now + TimeSerial(0, 0, 5))
    SendKeys “^a”
    SendKeys “^c”
[COLOR=”#008000″]’———————————–
‘COPY TO ACTIVE SHEET[/COLOR]
With ActiveSheet
    .Range(“A1″).Select
    Application.Wait (Now + TimeSerial(0, 0, 1))
    ActiveSheet.PasteSpecial Format:=”HTML”, Link:=False, DisplayAsIcon:=False
    ActiveSheet.DrawingObjects.Select
    Selection.Cut
[COLOR=”#008000″]’———————————–
‘REMOVE UNWANTED DATA[/COLOR]
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For I = 1 To Lastrow
    If Cells(I, 1) = “Rank” And Cells(I, 2) = “Name” Then
        .Rows(“1:” & I – 1).EntireRow.Delete
        .Rows(“102:” & Lastrow).EntireRow.Delete
    End If
Next I
[COLOR=”#008000″]’———————————–
‘REMOVE COPIED OBJECTS AND CLOSE IE[/COLOR]
    .DrawingObjects.Select
    Selection.Cut
    myIE.Quit
    Set myIE = Nothing
    Err.Clear
End With
[COLOR=”#008000″]’———————————–
‘FORMAT DATA[/COLOR]
With Selection
    .Hyperlinks.Delete
    .WrapText = False
    .Columns.AutoFit
End With
Application.ScreenUpdating = False
End Sub

Reply | Quote

The last line should be

Code:

Application.ScreenUpdating = True
End Sub

Reply | Quote