从WebPage中的脚本创建的表中导入Excel中的数据 [英] Import Data in Excel from a table created by a script in a WebPage

问题描述

我正在尝试创建一个自动连接到网页并从excel导入表中的数据的宏。我的问题是Excel查询工具无法识别表，我认为是因为它是由页面中的脚本创建的，所以我不能使用标准的方式。
现在，我使用这种方法：

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. 将数据复制到剪贴板


2. 运行vba宏而不是从剪贴板获取数据并将其导入Excel

但是，我有超过20个网页每次导入时，我想要一个独立宏，由于页面的URL可以导入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.

我感兴趣的网页是：
http://www.investing.com/indices/us -30历史数据
我正在使用excel 2010

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?

推荐答案

尝试这个

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

这篇关于从WebPage中的脚本创建的表中导入Excel中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！