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

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

.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

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

.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

Typical, yours works perfect Kyle, so why does not mine?

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

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

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

Scrap that last request, I think I can use what you gave me

Thank you for all you've done

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: -

Sub Grab_SL_HTML_Cards()
Dim htm As Object: Set htm = CreateObject("htmlfile")
Dim optGrp As Object
Dim opt As Object
Dim lRow As Long
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "[URL]http://www.sportinglife.com/racing/racecards[/URL]", False
        .send
        htm.body.innerhtml = .responseText
    End With
    
    With htm.getElementById("select-racecard")
        For Each optGrp In .Children
            For Each opt In optGrp.Children
                With Sheets(1).range("a1")
                    Sheets(1).Hyperlinks.add .Offset(lRow, 0), "http://www.sportinglife.com/"]http://www.sportinglife.com" & opt.Value
                    .Offset(lRow, 1).Value = opt.innerText
                End With
                lRow = lRow + 1
            Next opt
        Next optGrp
    End With
    
    End Sub

Then use this code to grab the race tables

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

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.