paspuggie48
New Member
- Joined
- Jun 21, 2011
- Messages
- 35
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.
<!-- 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
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.
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 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
Last edited: