json - Adding a string with double quotes to a file in vba

Question

I'm getting data from a AQI API from China, I'm putting some of the data into a SQL server database and that works just dandy. I also decided to add everything returned from the API into a JSON file so I can access it later.

Whenever I try to append the string returned by the API into the file I get a "Invalid procedure call or argument" error

This is what I have done so far:

Public Sub AddToJson(Jsonline As String)
     Dim strfile As String
     Dim fso As New FileSystemObject
     Dim fsoStream As TextStream
     Dim iexist As String
     Dim stradd As String

     strfile = "c:JSON_AQI.json"
     stradd = Replace(Jsonline, Chr(34), Chr(34) & Chr(34) & Chr(34) & Chr(34))
     Debug.Print stradd
     iexist = Dir(strfile)

     'check if the file exists
     If iexist = "" Then
         'if it exists, open it and add the line
         Set fsoStream = fso.CreateTextFile(strfile)
     Else
         'if it doesn't exist, create it and add the line
         Set fsoStream = fso.OpenTextFile(strfile, ForAppending)
     End If

     fsoStream.WriteLine stradd

     fsoStream.Close

     Set fsoStream = Nothing
     Set fso = Nothing


 End Sub

This is what I pass as the Jsonline parameter:

 {"status":"ok","data":{"aqi":164,"idx":7130,"attributions":[{"name":"Hunan Environmental Protection Agency (????????)"},{"name":"China National Urban air quality real-time publishing platform (??????????????)"}],"city":{"geo":[33.8561,115.7831],"name":"sanguó lanshèng gong, Bozhou"},"dominentpol":"pm25","iaqi":{"co":{"v":14.8},"no2":{"v":24.7},"o3":{"v":45.9},"pm10":{"v":97},"pm25":{"v":164},"so2":{"v":5.1}},"time":{"s":"2017-04-06 04:00:00","tz":"+08:00","v":1491451200}}}

as you can see I tried adding extra double quotes to the string to no avail, is there anything else that I'm missing?

Answer 1

Actually the received JSON contains Chinese characters instead of ? you posted and should look like:

{
    "status": "ok",
    "data": {
        "aqi": 164,
        "idx": 7130,
        "attributions": [
            {
                "name": "Hunan Environmental Protection Agency (湖南省环境保护厅)"
            },
            {
                "name": "China National Urban air quality real-time publishing platform (全国城市空气质量实时发布平台)"
            }
        ],
        "city": {
            "geo": [
                33.8561,
                115.7831
            ],
            "name": "sanguó lanshèng gong, Bozhou"
        },
        "dominentpol": "pm25",
        "iaqi": {
            "co": {
                "v": 14.8
            },
            "no2": {
                "v": 24.7
            },
            "o3": {
                "v": 45.9
            },
            "pm10": {
                "v": 97
            },
            "pm25": {
                "v": 164
            },
            "so2": {
                "v": 5.1
            }
        },
        "time": {
            "s": "2017-04-06 04:00:00",
            "tz": "+08:00",
            "v": 1491451200
        }
    }
}

Therefore you have to set encoding of the snippet being appended as Unicode explicitly. I suggest to use the below simple functions to read, write and append text files via Scripting.FileSystemObject:

Function ReadTextFile(sPath As String, lFormat As Long) As String
    ' lFormat -2 - System default, -1 - Unicode, 0 - ASCII
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath, 1, False, lFormat)
        ReadTextFile = ""
        If Not .AtEndOfStream Then ReadTextFile = .ReadAll
        .Close
    End With
End Function

Sub WriteTextFile(sContent As String, sPath As String, lFormat As Long)
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath, 2, True, lFormat)
        .Write sContent
        .Close
    End With
End Sub

Sub AppendTextFile(sContent As String, sPath As String, lFormat As Long)
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath, 8, True, lFormat)
        .Write sContent
        .Close
    End With
End Sub

So you can append the string by the calling:

AppendTextFile Jsonline & vbCrLf, "C:JSON_AQI.json", -1