在VBA中将带双引号的字符串添加到文件

Question

我正在从中国的AQI API中获取数据，正在将某些数据放入SQL
Server数据库中，但效果很好。我还决定将API返回的所有内容添加到JSON文件中，以便以后使用。

每当我尝试将API返回的字符串附加到文件中时，都会收到“无效的过程调用或参数”错误

到目前为止，这是我所做的：

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

这就是我作为Jsonline参数传递的内容：

 {"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}}}

如您所见，我尝试在字符串上添加多余的双引号无济于事，还有其他我想念的东西吗？

Answer 1

实际上，接收到的JSON包含中文字符而不是?您发布的字符，应如下所示：

{
    "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
        }
    }
}

因此，您必须设置要明确附加为Unicode的代码片段的编码。我建议使用以下简单功能通过以下方式读取，写入和附加文本文件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

因此，您可以通过调用附加字符串：

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