StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The code gets the data from nseindia site which comes as a JSON string in <code>responseDiv</code> element. </p> <p><strong>Required References</strong></p> <p><img src="https://i.stack.imgur.com/Qoxhn.png" alt="enter image description here"></p> <p><strong>3 Class Module i have used</strong></p> <ul> <li>cJSONScript</li> <li>cStringBuilder</li> <li>JSON</li> </ul> <p>(I have picked these class modules from <a href="http://www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html" rel="nofollow noreferrer">here</a>)</p> <p>You may download the file from this <a href="https://docs.google.com/file/d/0B1jQqyFZSZO4cExzVnRJRXFid1E/edit?usp=sharing" rel="nofollow noreferrer">link</a></p> <p><strong>Standard Module</strong></p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload