VBA 解析JSON文件

1. 解析Json的例子代码

Option Explicit

Public Sub ParseJsonExample()    
    Dim strFullPath As String
    Dim strJson As String
    strFullPath = Environ("USERPROFILE") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "test.json"
    strJson = vbNullString
    strJson = strJson & "{" & vbCrLf
    strJson = strJson & """Message"":""Hello, World!""," & vbCrLf
    strJson = strJson & """Number"":100," & vbCrLf
    strJson = strJson & """Array"":[1, 2, 3]," & vbCrLf
    strJson = strJson & """Object"": {""Example"":""Something""}" & vbCrLf
    strJson = strJson & "}"
    ' Generate Json File
    Call CreateJsonFile(strFullPath, strJson)
    
    Dim fso As Scripting.FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim ts As Scripting.TextStream
    Set ts = fso.OpenTextFile(strFullPath)
    
    Dim strJsonContent As String
    strJsonContent = ts.ReadAll()
    
    Dim objJsonDict As Scripting.Dictionary
    Set objJsonDict = JsonConverter.ParseJson(strJsonContent)
    
    Debug.Print objJsonDict("Message")
    Debug.Print objJsonDict("Number")
    Debug.Print objJsonDict("Array")(1), objJsonDict("Array")(2), objJsonDict("Array")(3)
    Debug.Print objJsonDict("Object")("Example")

End Sub

Function CreateJsonFile(ByVal strFile As String, ByVal strText As String)
    Open strFile For Output As #1
    Print #1, strText
    Close #1
End Function

2. 需要引入的代码和库文件

2-1. 参照的库：Microsoft Scripting Runtime

2-2. 引入的模块：JsonConverter.bas

模块下载地址：https://download.youkuaiyun.com/download/wuchunyu002/89625538

或者网上自行下载。