current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 6 down vote favorite
2

I am need of handling JSON Object which is the response of XMLHTTPRequest in Excel VBA. I wrote below code but not succeeded. Please guide me. 

  Dim sc As Object
  Set sc = CreateObject("ScriptControl")
  sc.Language = "JScript"

  Dim strURL As String: strURL = "blah blah"

  Dim strRequest
  Dim XMLhttp: Set XMLhttp = CreateObject("msxml2.xmlhttp")
  Dim response As String

  XMLhttp.Open "POST", strURL, False
  XMLhttp.setrequestheader "Content-Type", "application/x-www-form-urlencoded"
  XMLhttp.send strRequest
  response = XMLhttp.responseText
  sc.Eval ("JSON.parse('" + response + "')")

I am getting the error Run-time error '429' ActiveX component can't create object in the line Set sc = CreateObject("ScriptControl")

And, Once we parsed the JOSN Object, how to access the values of JSON Object?

P.S. My JSON Object sample: {"Success":true,"Message":"Blah blah"}

share|improve this question
    
Can you provide the link and id of data to be pulled. –  Santosh May 29 '13 at 15:33
    
Perhaps try Set sc = CreateObject("MSScriptControl.ScriptControl") –  barrowc May 29 '13 at 16:27
    
@Santosh, it is not an online link... localhost now. I don't have any online links to ping and get the result. –  Santhosh May 30 '13 at 4:08
    
@barrowc tried. No luck :( :( –  Santhosh May 30 '13 at 5:38
    
To access items eg array.item(0) see this post stackoverflow.com/questions/5773683/… –  ozmike Oct 14 '13 at 12:51
show 1 more comment

2 Answers

up vote 6 down vote accepted

The code gets the data from nseindia site which comes as a JSON string in responseDiv element.

Required References

enter image description here

3 Class Module i have used

  • cJSONScript
  • cStringBuilder
  • JSON

(I have picked these class modules from here)

You may download the file from this link

Standard Module

Const URl As String = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=ICICIBANK"
Sub xmlHttp()

    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    xmlHttp.Open "GET", URl & "&rnd=" & WorksheetFunction.RandBetween(1, 99), False
    xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.send

    Dim html As MSHTML.HTMLDocument
    Set html = New MSHTML.HTMLDocument
    html.body.innerHTML = xmlHttp.ResponseText

    Dim divData As Object
    Set divData = html.getElementById("responseDiv")
    '?divData.innerHTML
    ' Here you will get a string which is a JSON data

    Dim strDiv As String, startVal As Long, endVal As Long
    strDiv = divData.innerHTML
    startVal = InStr(1, strDiv, "data", vbTextCompare)
    endVal = InStr(startVal, strDiv, "]", vbTextCompare)
    strDiv = "{" & Mid(strDiv, startVal - 1, (endVal - startVal) + 2) & "}"


    Dim JSON As New JSON

    Dim p As Object
    Set p = JSON.parse(strDiv)

    i = 1
    For Each item In p("data")(1)
       Cells(i, 1) = item
       Cells(i, 2) = p("data")(1)(item)
        i = i + 1
    Next

 End Sub
share|improve this answer
    
@Santhosh Did you try this ? –  Santosh Jun 3 '13 at 18:27
    
Sorry for the late reply.. I have already added the required references using my code... No luck :( ... I did not try your code... I will try and let you know. –  Santhosh Jun 27 '13 at 7:12
    
THANK YOU for this! You have helped me tremendously! –  ONDEV Jul 15 '13 at 11:03
2  
@ONDEV Glad the post helped you! Cheers :) –  Santosh Jul 15 '13 at 11:38
add comment
up vote 2 down vote

I've had a lot of success with the following library:

https://code.google.com/p/vba-json/

The library uses Scripting.Dictionary for Objects and Collection for Arrays and I haven't had any issues with parsing pretty complex json files.

As for more info on parsing json yourself, check out this question for some background on issues surrounding the JScriptTypeInfo object returned from the sc.Eval call:

Excel VBA: Parsed JSON Object Loop

Finally, for some helpful classes for working with XMLHTTPRequest, a little plug for my project, Excel-REST:

https://github.com/timhall/Excel-REST

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.