本文记录一下用 VBA 接收和解析 JSON, 并把解析到的数据填入 Excel 表格的整套流程
1. 接收 JSON
Function GetJsonByUrl(ByVal url As String)
Application.DefaultWebOptions.Encoding = msoEncodingUTF8
Dim http
Set http = CreateObject("Microsoft.XMLHTTP")
http.Open "GET", url, False
'http.setRequestHeader "CONTENT-TYPE", "application/x-www-form-urlencoded"
http.send
If http.Status = 200 Then
Dim json$
json = http.responseText
GetJsonByUrl= json
End If
End Function
2. 解析 JSON
下面是一个处理 json 字符串的函数
'json = {"a":["a1","a2","a3"], "count":3}
Function ParseJSON(ByVal json As String)
'Set objSC = CreateObject("ScriptControl") '用于32位Excel
Set objSC = CreateObjectx86("MSScriptControl.ScriptControl") '用于64位Excel, 不过需要引入另一个模块