Parsing JSON array in Excel VBA

Question

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+"
      }   ]}

"I have tried to follow the method in another thread" -- what thread is that? Your code doesn't compile -- you declared json as a string but then tried to assign something which isn't a string to it. — John Coleman
– John Coleman, Commented Apr 5, 2016 at 2:10
hi there, that's the sample code i got. stackoverflow.com/questions/14822672/… — Vinnie
– Vinnie, Commented Apr 5, 2016 at 3:34
i have done something similar, i used http.responsetext method in vba, and I processed the response as string and it worked. however, in this case the response turn out to be an array, so my previous code wouldn't apply here, as I used a json parser to process keyname. — Vinnie
– Vinnie, Commented Apr 5, 2016 at 3:36

Tim Williams · Accepted Answer · 2016-04-05 05:45:09Z

2

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

answered Apr 5, 2016 at 5:45

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vinnie Over a year ago

hi tim, thanks for the reply. I used the code and made some modification. but i am getting syntax error on sc.Eval "var obj=(" & json & ")" , i tried to remove double quotation before and after & as suggested in other thread to resolve syntax error. but it still didn't work. also, i modified the code a little in order to return my response from API as such, Dim json As String: json = Http.ResponseText on the first line.

Tim Williams Over a year ago

Works exactly as posted for me: I took the json from your question and pasted it into A1 for testing. If you can update your question to show your current code, and double-check the json is correct I can take a look.

Vinnie Over a year ago

hi tim, i managed to get it work now. Thanks so much. Now I need to find a way to place them back to the spreadsheet horizontally, from A2-A7. need to change the debug.print. thanks so much for your help.

Collectives™ on Stack Overflow

Parsing JSON array in Excel VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related