vba把json转数组中_在Excel VBA中解析JSON数组

I have tried to follow the method in another thread, however there are certain part in the code that I don't understand and it didn't work for me. And I could comment as I haven't reached 50 reputations. (i included the response from the other thread)

I am trying to access to Json response as below, I would like to get the array after details into excel horizontally(column A1-A6), however in the other post, I don't understand what the script control method.

And there wasn't any comment line to explain what is it. And I tried to use it, the code just failed at sc.Eval "var obj=(" & json & ")"

Also, the line json = {get your json here} failed, instead i replaced that to Json = resp , where resp is the output returned from the API.

Your help is greatly appreciated.

Sub Tester()

Dim json As String

Dim sc As Object

Dim o

Set sc = CreateObject("scriptcontrol")

sc.Language = "JScript"

json = {get your json here}

sc.Eval "var obj=(" & json & ")" 'evaluate the json response

'add some accessor functions

sc.AddCode "function getSentenceCount(){return obj.sentences.length;}"

sc.AddCode "function getSentence(i){return obj.sentences[i];}"

Debug.Print sc.Run("getSentenceCount")

Set o = sc.Run("getSentence", 0)

Debug.Print o.trans, o.orig

End Sub

JSON response from API

{"details":[

{

"trade":"Micro",

"trade_tenor":"5yr+"

},

{

"trade":"Odd",

"trade_tenor":"10yr+"

},

{

"trade":"Round",

"trade_tenor":"20yr+"

} ]}

解决方案

Only needs some minor modifications:

Sub Tester()

Dim json As String

Dim sc As Object

Dim o, i, num

Set sc = CreateObject("scriptcontrol")

sc.Language = "JScript"

json = Range("A1").Value '{get your json here}

sc.Eval "var obj=(" & json & ")" 'evaluate the json response

'add some accessor functions

sc.AddCode "function getTradeCount(){return obj.details.length;}"

sc.AddCode "function getTrade(i){return obj.details[i];}"

num = sc.Run("getTradeCount")

For i = 0 To num - 1

Set o = sc.Run("getTrade", i)

Debug.Print o.trade, o.trade_tenor

Next i

End Sub