Fix XML encoding in VBA

Question

I have this code in VBA

Sub MySub ()

Dim body As String

body= "<?xml version=""1.0"" encoding="utf-8""?>" & _
           "<GetOrdersRequest xmlns=""urn:ebay:apis:eBLBaseComponents"">" & 
            "<ErrorLanguage>en_US</ErrorLanguage>"

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1)
URL = "https://api.testurl.com/ws/api.dll"

objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "X-API-SITEDID", "0"
objHTTP.setRequestHeader "X-API-REQUEST-Encoding", "XML"
objHTTP.setRequestHeader "X-API-COMPATIBILITY-LEVEL", "967"

objHTTP.send (body)

Set objXML = New MSXML2.DOMDocument
objXML.async = False
objXML.LoadXML (objHTTP.ResponseText)
objXML.Save "C:\Users\Dan\Desktop\MySubOutput.xhtml"

End Sub

here is output:

1) how do I fix those characters in the xml response? (before parsing and pulling data from it)

the actual output for this node should be "32ième"

2) once that is done, how do I remove all accents from characters?

I mean à,ḗ,ḯ,ǿ,ǘ,ḉ,ǹ >> o a,e,i,o,u,c,n

See [Save text file UTF-8 encoded with VBA](http://stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba). — Comintern, Aug 27 '16 at 13:21
hmm unsure what it does? I ran that code and what I got was a txt file that contains "special characters: äöüß"; my code continues after the file save, it needs to parse the xml nodes and then extract values into sheet — idan, Aug 27 '16 at 15:43

score 2 · Accepted Answer · answered Aug 28 '16 at 02:36

Consider using XSLT, the transformation language used to manipulate XML documents. XSLT maintains the translate() function allowing you to replace characters (not words). And VBA's MSXML library which you are already using can run XSLT 1.0 scripts. The below script will run your replacement anywhere the accented characters show up. Example conversions:

32iÃme avenue --> 32ième avenue OR 32iÃàḗḯǿǘḉǹme avenue --> 32ièaeiounme avenue

XSLT script (save as a separate .xsl file and save content in a UTF-8 encoding format, not ANSI)

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output version="1.0" encoding="utf-8" indent="yes" />
<xsl:strip-space elements="*"/>

  <!-- Identity Transform to copy all of doc as is -->
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <!-- replace accented character -->
  <xsl:template match="text()">
     <xsl:value-of select="translate(., 'Ãàḗḯǿǘḉǹ', 'èaeioun')"/>
  </xsl:template>

</xsl:transform>

VBA (where latter references should point to newXML and not objXML)

Set objXML = New MSXML2.DOMDocument
objXML.async = False
objXML.LoadXML (objHTTP.ResponseText)
objXML.Save "C:\Users\Dan\Desktop\MySubOutput.xhtml"

Set objXSL = New MSXML2.DOMDocument
objXSL.async = False
objXSL.Load "C:\Path\To\XSLT\Script.xsl"

' TRANSFORMING objXML to newXML
Set newXML = New MSXML2.DOMDocument
objXML.transformNodeToObject objXSL, newXML

' NOTICE newXML REFERENCES
XmlNamespaces = "xmlns:doc='urn:ebay:apis:eBLBaseComponents'"
newXML.setProperty "SelectionNamespaces", XmlNamespaces
newXML.setProperty "SelectionLanguage", "XPath"

Dim xItemList As IXMLDOMNodeList
Set xItemList = newXML.DocumentElement.SelectNodes("//doc:Transaction")
...

XSLT can even be embedded as a VBA string and not in a file. Be sure to escape double quotes and instead of objXSL.Load you would use objXSL.LoadXML (just as you did with http response). And the reason it is similar, is that XSLT files are well-formed XML files which carry scripting instructions!

Hi! I think it resolved the question # 2 (replace characters with accents to regular characters) but I wonder, before I do this, how do I first fix the encoding of the xml? (because there's some weird characters like ¾ or ¡ (symbols) like here https://s10.postimg.org/q1a6gk3o9/Untitled.png I think the xml response needs to be first converted to ascii encoding? if that's possible? — idan, Aug 29 '16 at 06:06
I just found this: https://ebaydts.com/eBayKBDetails?KBid=280 but unsure how I can apply this method that he explained in this vba instance — idan, Aug 29 '16 at 06:27
Try the function in [this answer](http://stackoverflow.com/questions/916118/classic-asp-how-to-convert-a-utf-8-string-to-ucs-2/920405#920405) (ignore that it is for ASP as it will work in VBA) where you pass the raw .xml file as `sIn` variable. However, do you know the correct encoding? See this [w3c list](https://www.w3.org/TR/2015/CR-encoding-20151020/). Often the content's language (German, French, etc.) can decide for you. — Parfait, Aug 29 '16 at 16:05
You are amazing!!! This resolved it completely! I searched on eBay forums and realized the actual output their server is sending is in ISO-8859-1, so I used objXML.LoadXML (ConvertFromUTF8(objHTTP.ResponseText)) and it solved it! And combined with your solution for replacing the accented characters with regular characters, the problem is now completely solved! Again, can't thank you enough you are amazingly helpful! — idan, Aug 30 '16 at 20:24
Haha! Awesome. Glad it all worked out and I got to help a second time. Happy coding! — Parfait, Aug 31 '16 at 03:04

Fix XML encoding in VBA

1 Answers1