将 Html 字符串转换为 HTMLDocument VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18567287/
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
Convert Html String into HTMLDocument VBA
提问by doovers
I'm writing a macro to grab the current exchange rate from yahoo but I'm having trouble converting a html string into a HTMLDocument to allow me to search for the required element by id. Here is my code so far but it fails on the debug.print line.
我正在编写一个宏来从雅虎获取当前汇率，但是我在将 html 字符串转换为 HTMLDocument 以允许我通过 id 搜索所需元素时遇到问题。到目前为止，这是我的代码，但它在 debug.print 行上失败了。
Public Sub Forex(currency1 As String, currency2 As String)
Dim oXHTTP As Object
Dim doc As HTMLDocument
Dim url As String
Dim html As String
Dim id As String
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
url = "http://finance.yahoo.com/q?s=" & currency1 & currency2 & "=X"
oXHTTP.Open "GET", url, False
oXHTTP.send
html = oXHTTP.responseText
Set oXHTTP = Nothing
Set doc = New HTMLDocument
doc.body.innerHTML = html
id = "yfs_l10_" & currency1 & currency2
Debug.Print doc.getElementById("id").innerText
End Sub
What am I missing here?
我在这里缺少什么？
回答by PatricK
I am making use of method from excel-vba-http-request-download-data-from-yahoo-finance:
我正在使用来自excel-vba-http-request-download-data-from-yahoo-finance 的方法：
Sub Forex(currency1 As String, currency2 As String)
Dim url As String, html As String, id As String
Dim oResult As Variant, oLine As Variant, sRate As String
url = "http://finance.yahoo.com/q?s=" & currency1 & currency2 & "=X"
id = "<span id=""yfs_l10_" & currency1 & currency2 & "=x"">"
html = GetHTTPResult(url)
oResult = Split(html, vbLf)
For Each oLine In oResult
If InStr(1, oLine, id, vbTextCompare) Then
sRate = Split(Split(oLine, "<span id=""yfs_l10_audusd=x"">")(1), "</span>")(0)
Exit For
End If
Next
End Sub
Function GetHTTPResult(sURL As String) As String
Dim XMLHTTP As Variant, sResult As String
Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
XMLHTTP.Open "GET", sURL, False
XMLHTTP.send
sResult = XMLHTTP.responseText
Set XMLHTTP = Nothing
GetHTTPResult = sResult
End Function
回答by SWa
You're almost there, you just have the id wrong:
你快到了，只是你的 id 错了：
id = "yfs_l10_" & currency1 & currency2 & "=X"
Debug.Print doc.getElementById(id).innerText
