小编典典

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

json

我正在从中国的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}}}

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


阅读 132

收藏
2020-07-27

共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
2020-07-27