excel vba pulling data from webpage which is in script



Im trying to pull data out of this website with excel macro. but the data which is displayed on the website is not in html. I think its in java-script and i have to login in order to get to this data table too.


iv been gettin "Access denied" error message.


im not really too sure what im doing here. so please help me out here and thanks in advance.



Dim xmlHttp As Object
Dim TR_col As Object, TR As Object
Dim TD_col As Object, TD As Object
Dim row As Long, col As Long

Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
xmlHttp.Open "GET", "http://ift.tt/1F4pVrZ", False
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send "userid=USERID&password=PASSWORD"

xmlHttp.Open "GET", "http://THEWEBSITE_DATATABLEPAGE", False
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send

Dim html As Object
Set html = CreateObject("htmlfile")
html.body.innerHTML = xmlHttp.responseText

Dim tbl As Object
Set tbl = html.getElementById("curr_table")

row = 1
col = 1

Set TR_col = html.getElementsByTagName("TR")
For Each TR In TR_col
Set TD_col = TR.getElementsByTagName("TD")
For Each TD In TD_col
Cells(row, col) = TD.innerText
col = col + 1
Next
col = 1
row = row + 1
Next

No comments:

Post a Comment