JSONToEXCEL

'获取的JSON输出到Excel单元格

Function JSONToRng(data As String)

    Dim strJSON As String

    'data = [a1]

    'Debug.Print strJSON

    Dim strJSCode As String

    b = InStr(1, data, ":")

    e = InStr(1, data, "}")

    strJSON = Mid(data, b + 1, e - b)

    strJSCode = "var json,key,col=row=1,d={};for(json in js){row++;for(key in js[json]){if(!d[key]){d[key]=col++;rng(1,d[key])=key;}rng(row,d[key])=js[json][key];}}"

    strJSCode = "js=[" & strJSON & "];" & strJSCode

  ' Debug.Print strJSCode

    With CreateObject("ScriptControl")

        .Language = "JScript"

        .AddObject "rng", Cells(3, 2)

        .Eval (strJSCode)

    End With

End Function

定位字符所在位置

b = InStr(1, data, ":")    ‘从data字符串的第一个字符开始扫描，返回“:”所在下标

e = InStr(1, data, "}")

strJSON = Mid(data, b + 1, e - b)    ’截取data字符串下标为b+1至e-b的内容

在VBA中使用JS语句

  With CreateObject("ScriptControl")