Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Web Data Query Possibilities

    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/tourname...e_viewranklist

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,205
    Thanks
    49
    Thanked 989 Times in 919 Posts
    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 <javascript:DoSort('Rank')>	Name
    <javascript:DoSort('Player_Name')>	City <javascript:DoSort('City')>
    State <javascript:DoSort('State_Id')>	Section
    <javascript:DoSort('Section_Name')>	District
    <javascript:DoSort('District_Name')>	Points <javascript:DoSort('Points')>
    1 	Berry, Ethan
    <javascript:__doPostBack('ctl00_mainContent_UpdatePanel_RankingHome',
    'Sender=PlayerRecords&id=1526012&p=4392&PlayerID=zQ8%2fyOnMkLSQjW1nmAg4tA%3d%3d&Type=viewranklist')>
    	Alpharetta 	GA 	Southern 	Georgia 	5339
    2 	Dunac, Daniel
    <javascript:__doPostBack('ctl00_mainContent_UpdatePanel_RankingHome',
    'Sender=PlayerRecords&id=1526012&p=3476&PlayerID=3alQOwvT3Fy6Dmtg1ObDag%3d%3d&Type=viewranklist')>
    	Douglasville 	GA 	Southern 	Georgia 	4847
    cheers, Paul

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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?

    tennis.png

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    .
    Last edited by zeddy; 2015-07-06 at 05:55.

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I will give that a look. Thanks for your suggestion.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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
    '-----------------------------------
    'OPEN IE, SELECT DATA, AND COPY
        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"
    '-----------------------------------
    'COPY TO ACTIVE SHEET
    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
    '-----------------------------------
    'REMOVE UNWANTED DATA
    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
    '-----------------------------------
    'REMOVE COPIED OBJECTS AND CLOSE IE
        .DrawingObjects.Select
        Selection.Cut
        myIE.Quit
        Set myIE = Nothing
        Err.Clear
    End With
    '-----------------------------------
    'FORMAT DATA
    With Selection
        .Hyperlinks.Delete
        .WrapText = False
        .Columns.AutoFit
    End With
    Application.ScreenUpdating = False
    End Sub

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    The last line should be

    Code:
    Application.ScreenUpdating = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •