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, I think because it's create by a script in the page, and so I cannot use the standard way. For now, I am using this method:

1. Copy the data into the clipboard
2. Run a vba macro than gets the data from the clipboard and imports it in Excel

However, I have more than 20 web pages to import every time and I would like a "standalone" macro which, given the url of the page, can import the data in excel.

The webpage I am interested in is: I am using excel 2010

Can anyone help me?

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, I think because it's create by a script in the page, and so I cannot use the standard way. For now, I am using this method:

1. Copy the data into the clipboard
2. Run a vba macro than gets the data from the clipboard and imports it in Excel

However, I have more than 20 web pages to import every time and I would like a "standalone" macro which, given the url of the page, can import the data in excel.

The webpage I am interested in is: http://www.investing./indices/us-30-historical-data I am using excel 2010

Can anyone help me?

• javascript
• html
• excel
• vba
• import

• "import in excel the data from a table" - can you explain the context of table... do you mean a HTML table, containing the data? – robnick Commented Jan 10, 2014 at 8:53
• Further to my ment- looking at the web page I am guessing that you want to do web page "scrapping", extracting data from a web page and doing something clever clogs with it. No way is Excel Query going to do that for you. What you want is some sort of intermediate tool that does the screen scrape, puts it into a form that Excel Query can access, e.g. a CSV file. – robnick Commented Jan 10, 2014 at 8:56
• What I would like to do is the equivalent of copying the table in the clipboard using a macro – MeSS83 Commented Jan 10, 2014 at 9:00
• Which table? The HTML table on those web sites? I believe I've already covered off your issues. You want to screen-scrape - it might be possible to do this in VBA but its non-trivial... using the clipboard is just plicates things. Once its in the clip board - then what? – robnick Commented Jan 10, 2014 at 9:04
• The table with the dates and prices is the one I am interested in. If I can put the data in the clipboard then I can use the macro I wrote to import data from the clipboard. However what I am interested in is only to import the data from that table into excel using a macro, if I can skip the clipboard it is ok for me. – MeSS83 Commented Jan 10, 2014 at 9:09

2 Answers 2

Try this

Sub Dow_HistoricalData()

    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://www.investing./indices/us-30-historical-data", 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
End Sub

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.