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?

I used them as escape character as shown here: http://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba — Ricardo de la Cruz, Apr 06 '17 at 18:40
You need to escape quotes such way if you are writing a string constant within VBA editor only. Note, there is double quoting in that answer, not quadruple. In your case the string value is already kept in the `Jsonline` variable, so no escaping is necessary. — omegastripes, Apr 06 '17 at 19:21
That was my assumption, but whenever I try to do it I get the error message. Do you know if there's any other constrain in Writeline ? as the size of the string... — Ricardo de la Cruz, Apr 06 '17 at 19:26
On which line, do you get the error prompt? I possibly went in the wrong direction with the CreateTextFile needing the extra parameters. I feel there's 2 different issues occuring — Jimmy Smith, Apr 06 '17 at 19:39
Hi Jimmy, thanks for your answers, the error happens in this part: fsoStream.WriteLine stradd — Ricardo de la Cruz, Apr 06 '17 at 19:56

score 0 · Accepted Answer · answered Apr 06 '17 at 20:40

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

Alright! That was it! :) Thanks for the detailed answer – Ricardo de la Cruz Apr 07 '17 at 15:16 — Ricardo de la Cruz, Apr 07 '17 at 15:16

Adding a string with double quotes to a file in vba

1 Answers1

Linked