0

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+" } ]}
Improve this question
3
  • "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. Commented Apr 5, 2016 at 2:10
  • hi there, that's the sample code i got. stackoverflow.com/questions/14822672/… 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. Commented Apr 5, 2016 at 3:36

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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.
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.