vba 使用 Excel 检索网站 xml
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12559111/
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
Retrieving website xml with Excel
提问by yu_ominae
In Excel2003 I am trying to retrieve some content from Yahoo finance.
在 Excel2003 中，我试图从雅虎财经中检索一些内容。
I have a really simple sub so far:
到目前为止，我有一个非常简单的子程序：
Public Sub test(BaseRange As Range)
Dim xmlObject As New MSXML2.XMLHTTP60
Dim xmlDoc As New MSXML2.DOMDocument
With xmlObject
Call .Open("GET", "http://biz.yahoo.com/research/earncal/20110923.html", False)
Call .send
End With
With ActiveSheet.Range("F2")
.Value = xmlObject.responseXML.XML
End With
End Sub
The sub executes without errors and the xmlObject.responseText seems to contain the page html text, but xmlObject.responseXml.XML is empty. Apart from resonsetext everything is actually empty.
子执行没有错误并且 xmlObject.responseText 似乎包含页面 html 文本，但 xmlObject.responseXml.XML 是空的。除了响应文本之外，一切实际上都是空的。
What am I missing here?
我在这里缺少什么？
回答by JustinJDavies
It is worth pointing out that there is an open source project that does this already: http://excel-dna.net/2011/04/28/financial-analytics-suite-finansu-made-with-excel-dna/
值得指出的是，已经有一个开源项目这样做了：http: //excel-dna.net/2011/04/28/financial-analytics-suite-finansu-made-with-excel-dna/
I hope that this is useful to you
我希望这对你有用
回答by Tim Williams
There's only content in responseXml if the server returns an XML response. Seems like your URL returns HTML and not XML.
如果服务器返回 XML 响应，则 responseXml 中只有内容。似乎您的 URL 返回 HTML 而不是 XML。
回答by Stephan Semerad
This is what I used for the ECB FX rate.
这是我用于欧洲央行外汇汇率的。
Sub ecb_fx()
ActiveWorkbook.XMLIMPORT url:= _
"https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml", ImportMap:=Nothing, _
Overwrite:=True, Destination:=Range("$B")
End Sub
