VBA Code To Grab Multiple HTML Tables From Web Page

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    Strange, that worked perfectly...not sure why mine does not :( I even copied your code again and overwrote my existing copy, still nothing. I then copied your Module1 macro into my Personal.xlsb folder, where all my macros are kept, still nothing.


    Anyway, I can keep that spreadsheet of yours and use that daily :)

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    oops, just noticed a typo:
    change:

    Code
    "http://www.sportinglife.com/" & opt.Value


    to:

    Code
    "http://www.sportinglife.com" & opt.Value


    Not sure why it's not working in your workbook, it's odd all the others do, try changing all the Sheet1s to Sheets(1)

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    Yes, I remember now when you gave me the code the first time I had to change the "Sheet" name to "Sheets(1)"...works now :)


    So, if I create an Application.Run macro to run both macros, 1 after another, it should work?

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    Yep, works perfect...Your knowledge and help has been richly appreciated, I've learned a lot..thanks Kyle


    So, with 1 click I can get the urls, with another get the cards.., the next day I get the results and "vlookup" those to the existing horses :)


    I just need to work on the sorting the cards now as I keep a record of every horse, of every race. I have a crude macro to clean and sort and takes only a few seconds to duplicate all the race info for each horse...but for me, from what I was doing a month ago this is a huge step forward and saves me a lot of time and effort in updating the database of information :)

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    If you are reading this Kyle, I am back....apologies for interferring again lol


    OK, I use your hyperlink code to get all the days racecard hyperlinks. Works perfect :) I then made a simple macro to duplicate and change them to make the hyperlinks for the "results", therefore the next day I can grab all the results for the previous day. It all works perfect, however this is only "on the day" information.


    I have a database (in Excel) of 165,000 lines, of every horse that ran and all the meeting information, but I have noticed way too many errors in it (these were .csv files loaded from a website). Therefore, I would like to check back in the past and load previous racecards and the retrospective results. Again by hand this will take forever.....


    So, my grovelling question is how to copy and alter your existing code/macros to do this. I may have to be the opposite of what I am already doing, that is grab the results source code to get the racecard hyperlinks.


    As an example...


    This is the link for yesterdays list of results http://www.sportinglife.com/racing/results/14-10-2012



    I put the above address in the code instead of .Open "GET", "http://www.sportinglife.com/racing/racecards", False


    But I don't know which piece of code is for the hyperlinks....help me Obe One Kenobi, you're my only hope :)


    Paul

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    The code works perfect for today, but going back to yesterday it still gives me todays racecards (Musselburgh, Salisbury etc). The hyperlinks need to show yesterdays results (Goodwood, Ffos Las)


    In your code I typed

    Code
    .Open "GET", "[URL]http://www.sportinglife.com/racing/results/14-10-2012[/URL]", False


    But I still got todays cards, have I done something wrong?

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    Try this, it might actually work for today's as well:

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    The SL website is barried from the internet police now, so will have to try either later or tomorrow Kyle.


    I looked in the souce code and actually changed "racecard" to "rac-cards click " and it didn't do anything, at least I was looking in the right place lol


    Still, the next 2 lines of your code are way over my head, so wouldn't have got far anyway LOL


    I'm glad you understood me, as in effect I have to work in the opposite direction. At the beginning of any day you get the racecards obviously, as results are in the future. Checking back for past results you cannot simply go to the "Racecards" link on that website because it only gives you future races. So my in my mind I thought the only way to get hyperlinks for previous results & racecards was to go to the results page, grab the source code and have it collect the hyperlinks for the racecards that way...:)


    Anyway, I will try soon and let you know how it goes :)


    Thank you sooo much :)
    Paul

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    One error Kyle


    It highlights the following text, the 2nd Replace


    Code
    .Offset(lRow, 1).Value = Replace(Replace(cls.innerText, vbCrLf, " "), " result", "")


    With an error message saying

    Quote

    Compile error:
    Expected variable or procedure, not module


    Rgds
    Paul

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    I can only assume that you've got something somewhere named cls, a variable declared publicly or a module name or a sheet code name etc. I don't know without seeing your workbook

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    I'll have a look in the other macros, but thank you again...yours works, so I can always use that document to get past racecard/results etc


    Superb !


    (Hopefully I won't bug you about this again ha ha)

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    I sent another question but it seems it may not have been sent??


    I have another problem (Kyle, if you are reading this) lol...


    Just to say I can get the URL's for all past results and simply rename them to get the URL name for the racecards...all works fine except, again, there is a slight difference in past information as to day-2-day information.


    Here is an example of a past racecard http://www.sportinglife.com/ra…agement-novices-39-hurdle


    The problem I have is when I use the previous code it is grabbing the information from the "Weighed In" table, which denotes 1st, 2nd, 3rd, etc which is a table I don't need. It seems the code already provided grabs that information too and then it does not sort out the horses in numerical order.


    Again, back to your code, what would need changing to enable to import only the meeting Information, as before,i.e. Time, Title, Value of Race, etc and ignore that one table starting "Weighed In" ? is it "tbl t3", I need?


    Your code below is for importing racecards



    Hope you can help again...and this time I promise I will have finished with this lol...I may need a good copy to visible cells code too, I should post another thread :)


    Regards
    Paul

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    Although the code works to download the links of previous dates of results, the vba code to extract the data to sheet2crashes. So at 12.45 in the morning, bleary eyed i'm begging for your help and hoping that santa is smiling, for you to help.


    Adding a big wish on top is if the "number of runners" could be added onto sheet1 with the name/time of race. i don't want to ask too much, but if annotation explaining the code a bit more would be fantastic as well but i don't want to put you out too much. many many thanks in advance.

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    Are you sure you are using the right VBA code Dunc?


    This is the code to grab todays hyperlinks: -




    Then use this code to grab the race tables



    I have used the above this morning and it works.


    Once the races have been imported they import the time and runner information, which you will see at the top of each racecard.

  • Re: VBA Code To Grab Multiple HTML Tables From Web Page


    hi


    apologies for this being a long post...


    many thanks for that, strangely your code didn't work, it crashed on this line


    Sheets(1).Hyperlinks.add .Offset(lRow, 0), "http://www.sportinglife.com/"]http://www.sportinglife.com" & opt.Value


    i don't know whether its because i'm on 2007 or that i'm copying the code into a macro rather than coding a button but i don't think that would make any difference.


    however i digress, i did get todays racecards to work but not the one to check previous cards (which you also seem to have a problem with) which is the version i need.


    i also had a similar issue with code not working but when i downloaded the file kyle posted that did work (strange).


    i am now at the stage where i can get the links from any date (edited in the code) with this code


    Code
    Sub Grab_SL_Cards()            Dim C As range      Dim g      With ActiveSheet          For Each C In .range("A1", .Cells(.Rows.Count, "A").End(xlUp))              g = GetTableSportingLife(C.Hyperlinks(1).Address)              If Len(Sheets(2).Cells(1, 1).Value) = 0 Then Sheets(2).Cells(1, 1).Value = "."              With Sheets(2).Cells(1, 1).CurrentRegion                  .Offset(.Rows.Count).Resize(UBound(g), UBound(g, 2)).Value = g              End With          Next C      End With        End Sub  Function GetTableSportingLife(url As String) As Variant      Dim htm As HTMLDocument, table As Object      Dim data() As String, x As Long, y As Long      Set htm = New HTMLDocument      With CreateObject("MSXML2.XMLHTTP")          .Open "GET", url, False          .send          htm.body.innerhtml = .responseText      End With      With htm          Set table = .getElementById("racecard")          Redim data(1 To table.Rows.Length + 6, 1 To 10)          For x = 0 To table.Rows.Length - 1              For y = 0 To table.Rows(x).Cells.Length - 1                  data(x + 7, y + 1) = table.Rows(x).Cells(y).innerText              Next y          Next x                              data(1, 1) = .getElementsByClassName("header-nav")(0).NextSibling.innerText          data(2, 1) = .getElementsByClassName("content-header")(0).Children(0).innerText          Set table = .getElementsByClassName("list")(0)          For x = 1 To table.Children.Length              data(x + 2, 1) = table.Children(x - 1).innerText          Next x          GetTableSportingLife = data                End With        End Function


    when trying to extra the data from links, if i use your code it fails on this line ReDim data(1 To table.Rows.Length + 6, 1 To 10)

    is the problem with the line before of Set table = .getElementById("racecard")


    hope the code displays properly


    Again all help appreciated


    dunc

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!