ashleyuk1984
02-23-2017, 03:13 PM
Hi,
I'm struggingly to get the table of information from this website onto a spreadsheet.
http://www.marinetraffic.com/en/ais/index/eta/all/page:1/port:199/ (http://www.marinetraffic.com/en/ais/index/eta/all/page:1/port:199/portname:FELIXSTOWE)
https://i.imgbox.com/bFUBoEOf.png (http://imgbox.com/bFUBoEOf)
This is the code that I currently have.
Sub GetTable()
Dim oHtml As HTMLDocument
Dim oElement As Object
Set oHtml = New HTMLDocument
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "http://www.marinetraffic.com/en/ais/index/eta/all/page:1/port:199/", False
.send
oHtml.body.innerHTML = .responseText
End With
Set GetInfo = oHtml.getElementsByClassName("table table-hover text-left")(0).getElementsByTagName("tr")
i = 0
For Each oElement In GetInfo
Sheets("Sheet1").Range("A" & i + 1) = GetInfo(i).innerText
i = i + 1
Next oElement
End Sub
I get close with this code, but it doesn't split the text into it's own cells.
I would basically just like to drop the raw data into Excel - just like how it's shown in the table. I can do all the formatting etc.
Bonus help: This table has two pages (It's effectively just two URLs) ... But sometimes it will be 3 or 4 pages long, so if the code could dynamically search for this and loop through the URL's that would be great.
Thanks very much.
I'm struggingly to get the table of information from this website onto a spreadsheet.
http://www.marinetraffic.com/en/ais/index/eta/all/page:1/port:199/ (http://www.marinetraffic.com/en/ais/index/eta/all/page:1/port:199/portname:FELIXSTOWE)
https://i.imgbox.com/bFUBoEOf.png (http://imgbox.com/bFUBoEOf)
This is the code that I currently have.
Sub GetTable()
Dim oHtml As HTMLDocument
Dim oElement As Object
Set oHtml = New HTMLDocument
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "http://www.marinetraffic.com/en/ais/index/eta/all/page:1/port:199/", False
.send
oHtml.body.innerHTML = .responseText
End With
Set GetInfo = oHtml.getElementsByClassName("table table-hover text-left")(0).getElementsByTagName("tr")
i = 0
For Each oElement In GetInfo
Sheets("Sheet1").Range("A" & i + 1) = GetInfo(i).innerText
i = i + 1
Next oElement
End Sub
I get close with this code, but it doesn't split the text into it's own cells.
I would basically just like to drop the raw data into Excel - just like how it's shown in the table. I can do all the formatting etc.
Bonus help: This table has two pages (It's effectively just two URLs) ... But sometimes it will be 3 or 4 pages long, so if the code could dynamically search for this and loop through the URL's that would be great.
Thanks very much.