Question

120

Import Data in Excel from a table created by a script in a WebPage

rated 0 times [ 121] [ 1] / answers: 1 / hits: 17986 / 11 Years ago, fri, january 10, 2014, 12:00:00

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:

Copy the data into the clipboard

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.com/indices/us-30-historical-data
I am using excel 2010

Can anyone help me?

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

reesel

Add To Favorites

Follow

Total Points: 345

Total Questions: 124

Total Answers: 119

Location: Trinidad and Tobago

Member since Thu, Dec 1, 2022

2 Years ago

answered 11 Years ago kaitlyn · Accepted Answer

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.com/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