在Excel VBA中解析JSON数组 [英] Parsing JSON array in Excel VBA
问题描述
我试图在另一个线程中遵循该方法,但是代码中的某些部分我不理解,因此对我不起作用.我可以发表评论,因为我还没有达到50个声誉. (我包括了其他线程的响应)
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)
我正在尝试访问Json响应,如下所示,我想将详细信息后的数组水平放入excel(A1-A6列),但是在另一篇文章中,我不了解脚本控制方法是什么.
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.
并且没有任何注释行来解释它是什么.我尝试使用它,代码只是在sc.Eval"var obj =("& json&)"
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 & ")"
另外,行json = {在此处获取您的json}失败,而是将其替换为Json = resp,其中resp是API返回的输出.
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.
非常感谢您的帮助.
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
API的JSON响应
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
这篇关于在Excel VBA中解析JSON数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!