Convert Html String into HTMLDocument VBA

Question

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.

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?

score 4 · Accepted Answer · edited May 23 '17 at 10:33

I am making use of method from 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

I'm a little disappointed that it's not possible to turn a html string into a htmldocument but your solution works perfectly. Thank you! — doovers, Sep 02 '13 at 09:43
The original question is answered in this other thread. https://stackoverflow.com/q/9995257/1337544 — charles ross, May 06 '19 at 17:40

score 2 · Answer 2 · answered Sep 02 '13 at 11:18

2

You're almost there, you just have the id wrong:

id = "yfs_l10_" & currency1 & currency2 & "=X"

Debug.Print doc.getElementById(id).innerText

answered Sep 02 '13 at 11:18

SWa

4,343
23
40

Convert Html String into HTMLDocument VBA

2 Answers2