Import Data In Excel From A Table Created By A Script In A Webpage

I am trying to create a macro that automatically connect to a web page and import in excel the data from a table. My problem is that Excel Query tool does not recognize the table,

Solution 1:

Try this

Sub Dow_HistoricalData()

    Dim xmlHttp AsObjectDim TR_col AsObject, TR AsObjectDim TD_col AsObject, TD AsObjectDim row AsLong, col AsLongSet xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    xmlHttp.Open "GET", "http://www.investing.com/indices/us-30-historical-data", False
    xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.send

    Dim html AsObjectSet html = CreateObject("htmlfile")
    html.body.innerHTML = xmlHttp.ResponseText

    Dim tbl AsObjectSet tbl = html.getElementById("curr_table")

    row = 1
    col = 1Set TR_col = html.getelementsbytagname("TR")
    ForEach TR In TR_col
        Set TD_col = TR.getelementsbytagname("TD")
        ForEach TD In TD_col
            Cells(row, col) = TD.innerText
            col = col + 1Next
        col = 1
        row = row + 1NextEndSub

Solution 2:

Another approach would be to make an HTTP request like

// source http://tkang.blogspot.co.at/2010/09/sending-http-post-request-with-vba.html
Dim result As String
Dim myURL As String
Dim winHttpReq As Object
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

myURL = "http://192.168.10.101:80/your_web_service?parameter=hello¶meter2=hi"

winHttpReq.Open "GET", myURL, False
winHttpReq.Send

result = winHttpReq.responseText

and parse the result. I haven't tried it myself though.