CODEWITHSUNDEEP
xmlexcelvba
lukehawk
lukehawk

Reputation: 1493

How do I convert special characters in XML to a readable string in VBA?

I have the following code, which pings Yahoo Finance, and downloads the profile for a particular security/stock:

Public Function getETFDetails(theSymbol As String)
    Dim xmlhttp As Object
    Dim strURL As String
    Dim x As String
    Dim sSearch As String, myDIV As String, theDeets As String
    'theSymbol = "SPY"

    strURL = "http://finance.yahoo.com/q/pr?s=" & theSymbol & "+Profile"
    DeleteUrlCacheEntry (strURL)
    Set xmlhttp = CreateObject("msxml2.xmlhttp")
    With xmlhttp
        .Open "get", strURL, False
        .send
        x = .responsetext
    End With
    Set xmlhttp = Nothing

    sSearch = "Fund Summary"
    myDIV = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
    sSearch = "<td>"
    myDIV = Mid(myDIV, InStr(1, myDIV, sSearch) + Len(sSearch))
    theDeets = Left(myDIV, InStr(1, myDIV, "</td>") - 1)

    getETFDetails = theDeets

End Function

This works great, and I get nice results. However, if the result includes special characters (For example, the S&P500 ETF, 'SPY'), I get the following:

The investment seeks to provide investment results that, before expenses, 
generally correspond to the price and yield performance of the S&amp;P 500® Index.

The handling of the ampersand and the trademark are my problem. How do I get them out of the result? I am fine with removing them entirely and just having it say 'SP 500', but I am hoping for something more robust then manually removing these via some list (string) of characters. Any thoughts? (I have tried using the StrConv function, but this returns only the first character of the string, for whatever reason.)

Upvotes: 2

Views: 2464

Answers (1)

Bond
Bond

Reputation: 16311

There's no built-in "HTML/XML Decode" function in VBA but you can write the string to an XML document and read back the text. For example:

Set doc = CreateObject("Msxml2.DOMDocument.6.0")
strXML = "S&amp;P 500"
doc.LoadXML "<root>" & strXML & "</root>"
strDecoded = doc.Text

Upvotes: 2

Related Questions