Stream write XML file with VBA in correct encoding

Question

I have an API response in an xml format which I request via vba and want to store in an xml file.

Here is the relevant part of my code:

Dim objFSO As Scripting.FileSystemObject
Dim OUTPUT_file As Object
Dim objXMLHTTP As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")

xml_file_path = ThisWorkbook.path & "\XML-Files\" & job_id & ".xml"
Set OUTPUT_file = objFSO.CreateTextFile(xml_file_path, True, True)

'API Request with objXMLHTTP Object

If objXMLHTTP.readyState = 4 Then
    OUTPUT_file.Write objXMLHTTP.responseText
    OUTPUT_file.Close
End If

At a later point I want to load the xml files back into the application. I do this with the following code:

Set objXML = New MSXML2.DOMDocument60
objXML.Load (xml_file_path)

MsgBox objXML.parseError.reason
'returns: "Switch from current encoding to specified encoding not supported."

Unfortunately the xml file's content is not recognized. (The objXML object stays empty).

The file itself looks good. When viewing it with a text editor, I can see the content.

Loading the xml files from other sources works fine. I therefore expect the problem to be somewhere where I create and write the file.

What am I missing?

Check whether you're getting an error by adding `MsgBox objXML.parseError.reason` after you've loaded your xml document. Does it give an error? If so, which one? — Domenic, Aug 12 '19 at 22:58
I get this error reason: 'Switch from current encoding to specified encoding not supported.' — Axel, Aug 13 '19 at 06:41
When FileSystemObject is used to create a text file as Unicode, as you're doing in your code, it probably uses UTF-16 encoding. Whereas your XML file is probably marked as using a different encoding, such as UTF-8, hence the error. — Domenic, Aug 13 '19 at 18:42
You can open your XML file, and check at the very top for the specified encoding. For example, if it in fact is UTF-8, you'll see `encoding="UTF-8"`. Which encoding does it specify? — Domenic, Aug 13 '19 at 18:43
Files created in vba as well as files from other sources that don't lead to errors have the following header: However, I think this string is just part of the textresponse from the API. — Axel, Aug 14 '19 at 05:07

score 0 · Answer 1 · answered Aug 14 '19 at 05:44

The helpful comments of Domenic as well as the post (save-text-file-utf-8-encoded-with-vba) let me to a working solution.

Storing text in a FileSystemObject file results in an Unicode UTF-16 encoding. This seems to be independent of the encoding tag written into the xml file as part of the API response.

?xml version="1.0" encoding="UTF-8"?>

The MSXML2.DOMDocument60 expects the encoding to be UTF-8 (as stated in the encoding tag). An error occurs:

"Switch from current encoding to specified encoding not supported."

Storing the API response in a txt file with UTF-8 encoding solves the problem.

Here is the updated code:

Dim objXMLHTTP As Object
Dim OUTPUT_file As Object
Set OUTPUT_file = CreateObject("ADODB.Stream")
OUTPUT_file.Type = 2
OUTPUT_file.Charset = "utf-8"

xml_file_path = ThisWorkbook.path & "\XML-Files\" & job_id & ".xml"

'API Request with objXMLHTTP Object

If objXMLHTTP.readyState = 4 Then
    OUTPUT_file.Open
    OUTPUT_file.WriteText objXMLHTTP.responseText
    OUTPUT_file.SaveToFile xml_file_path, 2
End If

Stream write XML file with VBA in correct encoding

1 Answers1