How to write file with VBA utf-8 problem?

patpatwithhat 0
Viewed 30

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": "B\u00FChne",

What am I doing wrong?

utf-8 vba excel
1 Answers

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
patpatwithhat0
Related