Issue

This Content is from Stack Overflow. Question asked by patpatwithhat

I want to parse a dictionary into a JSON with this logic:
https://github.com/VBA-tools/VBA-JSON
and write it to a file.

Parsing works, writing the file as well, but characters like öäü are displayed wrong.
Here is my code:

Function writeJsonFromDict(dict As Dictionary)
    Dim fsT As Object
    Set fsT = CreateObject("ADODB.Stream")
    fsT.Type = 2
    fsT.Charset = "utf-8"
    fsT.Open
    fsT.WriteText ConvertToJson(dict, Whitespace:=2)
    fsT.SaveToFile "path/to/file.json", 2
End Function

Expected output:

  "myValue": "Bühne",

Actual output:

  "myValue": "Bu00FChne",

What am I doing wrong?

Solution

Problem solved!

The writing is ok.
But ConvertToJson(dict, Whitespace:=2) can’t handle “äöü…”.
Wrote a quick string replace logic for all chars I need to handle.
Obviously there are better ways but this was the fastest for me.

Function replaceUTF8Chars(content As String)
    content = Replace(content, "\u00E4", "ä")
    content = Replace(content, "\u00C4", "Ä")
    content = Replace(content, "\u00F6", "ö")
    content = Replace(content, "\u00D6", "Ö")
    content = Replace(content, "\u00FC", "ü")
    content = Replace(content, "\u00DC", "Ü")
    content = Replace(content, "\u00DF", "ß")
    content = Replace(content, "\u00E8", "è")
    content = Replace(content, "\u00E9", "é")
    content = Replace(content, "\u00A0", "ê")
    replaceUTF8Chars = content
End Function

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. – CC BY-SA 3.0. – CC BY-SA 4.0.