Handle JSON Object in XMLHttp response in Excel VBA Code

by

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

More Related Contents:

  1. Parsing JSON in Excel VBA
  2. Returning Null JSON Throwing Type Mismatch Error in VBA
  3. Excel VBA: Parsed JSON Object Loop
  4. In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?
  5. In Excel VBA on Windows, how to get stringified JSON respresentation instead of “[object Object]” for parsed JSON variables?
  6. Post JSON to web in excel vba
  7. AngularJS $http-post – convert binary to excel file and download
  8. How to parse JSON with VBA without external libraries?
  9. Parsing JSON feed automatically into MS Access
  10. How can I automate Save as dialog box in IE11 using VBA?
  11. Using VBA and VBA-JSON to access JSON data from WordPress API
  12. VBA code required for Msg Box when two value equal in two cells
  13. How to get MS Excel to list files in a folder with their metadata (in particular, the tags created for the files)?
  14. Excel – opening file stuck on 100%, until VBA code finishes
  15. Are multi-line strings allowed in JSON?
  16. JSON and dealing with unexported fields
  17. How to call python script on excel vba?
  18. How to decode a nested JSON struct with Swift Decodable protocol?
  19. How To Speed Up VBA Code
  20. How to fetch JSON file in Angular 2
  21. Getting around the Max String size in a vba function?
  22. Refresh Excel VBA Function Results
  23. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  24. Deserializing JSON using JSon.NET with dynamic data
  25. Concatenate column headers if value in rows below is non-blank
  26. Update excel sheet based on outlook mail [closed]
  27. Flutter FormatException: Unexpected character (at character 1)
  28. How to exclude properties from Swift Codable?
  29. How can I use JSON on the web page from a source with neither CORS nor JSONP? [closed]
  30. Grails JSONBuilder

Leave a Comment