VBA Code To Grab Multiple HTML Tables From Web Page

[paspuggie48]

Hi Guys

I obtained some VBA code from a very nice guy on another forum (For some reason I cannot access it at work anymore, but here is the link http://www.excelforum.com/excel-prog...-new-post.html

The code aoutomatically finds my list of "hyperlinks" on Sheet1 and opens behind the scenes the webpages and grabs the table information from the respective URL page and puts that information on Sheet2, all in line and beautiful.

I then wanted to alter this code to suit another website, the Sporting Life, namely the racecards. However, the problems I'm having, being a novice at this, is how to change the code to grab more than just one bit of information.

Here is an example, which is a recent race

Racecard 14:10 Pontefract | Oct 8 2012 | British Stallion Studs Supporting British Racing E.B.F. Maiden Stakes | Sporting Life - Horse Racing News | Live Racing Results, Racecards, Live Betting Shows

The code below, again finds the hyperlink in my list, opens the webpage and copies the table but it only copies list from Horse No.1 to Horse No.15, which is good but it is missing the Time "14:40 Pontefract" and the table below that, which is the race "Title" and "Going" etc.

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 Object, table As Object 
    Dim data() As String, x As Long, y As Long 
    Set htm = CreateObject("HTMLfile") 
    With CreateObject("MSXML2.XMLHTTP") 
        .Open "GET", url, False 
        .send 
        htm.body.innerhtml = .responsetext 
    End With 
    With htm 
        Set table = .getelementsbytagname("table")(0) 
        Redim data(1 To table.Rows.Length, 1 To 10) 
        For x = 0 To table.Rows.Length - 1 
            For y = 0 To table.Rows(x).Cells.Length - 1 
                data(x + 1, y + 1) = table.Rows(x).Cells(y).InnerText 
            Next y 
        Next x 
         
        GetTableSportingLife = data 
         
    End With 
End Function

<!-- END TEMPLATE: bbcode_code -->So my question is, does anyone know what needs changing in the code above to collect all the tables/information from Race Time to the bottom of the racecard?

Regards
Paul

 

[Kyle123]

You need to include a link to your post on ozgrid as per the forum rules also, put a link on ozgrid to here.

The other data on the page is a bit different since it's not in a table. Which headings is it you are after in particular?

Do you have ie9 installed?

 

[paspuggie48]

You need to include a link to your post on ozgrid as per the forum rules also, put a link on ozgrid to here.

The other data on the page is a bit different since it's not in a table. Which headings is it you are after in particular?

Do you have ie9 installed?

Apologies for the duplication Kyle, (as noted I cannot access ExcelForum site) The Link to Ozgrid is VBA Code To Grab Multiple HTML Tables From Web Page

I have Ie9 yes. From your code which was greatly appreciated, I adjusted your code simply to copy the race card, that is the number of horses in the race, from No.1 to whatever it is. However above this is the most important bit which is the race meeting, race time, going, value etc, it is the table (information) directly above the list of horses in the race, does that help?

Regards
Paul

 

[Kyle123]

no, not really

The data below isn't in a table. Specifically, which headings are you after?

 

[Kyle123]

no, not really :wink:

The data below isn't in a table. Specifically, which headings are you after?