VBA Get JSON Response Text

VBA to get JSON response text from an api call. Vba will extract the data as a JSON string which can then be parsed to extract the node values.  This code only creates the JSON string but does not parse the string.


The following reference libraries need to be enabled.  Forms 2.0 library can be added by adding a user form in the vba editor.


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"

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

Dim divData As Object
Set divData = html.getElementById("responseDiv")

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) & "}"
MsgBox strDiv

End Sub