vba 从网页中的脚本创建的表格中导入 Excel 中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21039677/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import Data in Excel from a table created by a script in a WebPage
提问by MeSS83
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:
我正在尝试创建一个自动连接到网页的宏，并在 excel 中导入表中的数据。我的问题是 Excel 查询工具无法识别表格，我认为是因为它是由页面中的脚本创建的，所以我无法使用标准方式。目前，我正在使用这种方法：
- Copy the data into the clipboard
- Run a vba macro than gets the data from the clipboard and imports it in Excel
- 将数据复制到剪贴板
- 运行 vba 宏，然后从剪贴板中获取数据并将其导入 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.
但是，我每次要导入 20 多个网页，我想要一个“独立”宏，给定页面的 url，可以在 excel 中导入数据。
The webpage I am interested in is: http://www.investing.com/indices/us-30-historical-dataI am using excel 2010
我感兴趣的网页是：http: //www.investing.com/indices/us-30-historical-data我正在使用 excel 2010
Can anyone help me?
谁能帮我？
回答by Santosh
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
回答by Günter Z?chbauer
Another approach would be to make an HTTP request like
另一种方法是发出 HTTP 请求，如
// 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.
并解析结果。不过我自己没试过。
