Parsing JSON data into Excel sheet

Question

I'm trying to extract JSON data into Excel sheet as table by using the following code.

Sub test()
    Dim httpObject As Object
    Set httpObject = CreateObject("MSXML2.XMLHTTP")

    sURL = "https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json"

    sRequest = sURL
    httpObject.Open "GET", sRequest, False
    httpObject.send
    sGetResult = httpObject.responseText

    Dim oJSON As Object
    Set oJSON = JsonConverter.ParseJson(sGetResult)

    i = 2

    For Each sItem In oJSON
        dItemString = oJSON(sItem)("symbol")
        sItemValue = oJSON(sItem)("open")
        vItemValue = oJSON(sItem)("high")
        xItemValue = oJSON(sItem)("low")
        Cells(i, 1) = dItemString
        Cells(i, 2) = sItemValue
        Cells(i, 3) = vItemValue
        Cells(i, 4) = xItemValue
        i = i + 1
    Next
End Sub

However, I'm getting the below error!

Why I'm getting this error? Kindly advise

Solution 1

First of all you need to examine the structure of the JSON response, using any online JSON viewer (e. g. http://jsonviewer.stack.hu/), where you can see that your JSON object contains data array, and several properties with scalar values:

Going further there are objects within data array, each of them contains some properties that can be populated in rows on the worksheet:

Here is VBA example showing how that values could be retrieved. Import JSON.bas module into the VBA project for JSON processing.

Option Explicit

Sub Test()

    Dim sJSONString As String
    Dim vJSON
    Dim sState As String
    Dim aData()
    Dim aHeader()
    Dim vResult

    ' Retrieve JSON content
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json", True
        .send
        Do Until .readyState = 4: DoEvents: Loop
        sJSONString = .responseText
    End With
    ' Parse JSON sample
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then MsgBox "Invalid JSON": End
    ' Convert raw JSON to 2d array and output to worksheet #1
    JSON.ToArray vJSON("data"), aData, aHeader
    With ThisWorkbook.Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With
    MsgBox "Completed"

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

The output for data array for me is as follows:

BTW, the similar approach applied in other answers.

Solution 2

I pasted your code into a test module and then imported the JsonConverter as an additional module in my empty workbook. The error you're getting is likely because you need to add the "Microsoft Scripting Runtime" library to your workbook. In the VBE go to the Tools-->References... menu and then scroll down and put a check mark next to the library. After doing this, your code parsed the JSON without issue.

However it did fail in your loop.

I highly recommend that you use Option Explicit at the top of your module. The variable types you think you're using (because I see you're attempting to use Hungarian notation) are not the types of the actual data necessarily. My suggestion is to use descriptive names for the variables to avoid confusion. Additionally, you should be looping on the oJSON("data") structure (which is a Collection by the way). Here is my suggestions put into practice:

Option Explicit

Sub test()
    Dim httpObject As Object
    Set httpObject = CreateObject("MSXML2.XMLHTTP")

    Dim sURL As String
    sURL = "https://www.nseindia.com/live_market/dynaContent/" & _
           "live_watch/stock_watch/foSecStockWatch.json"

    Dim sRequest As String
    sRequest = sURL
    httpObject.Open "GET", sRequest, False
    httpObject.send

    Dim sGetResult As String
    sGetResult = httpObject.responseText

    Dim oJSON As Object
    Set oJSON = JsonConverter.ParseJson(sGetResult)

    Dim i As Long
    i = 2

    Dim dataItem As Variant
    Dim symbolName As String
    Dim openValue As Double
    Dim highValue As Double
    Dim lowValue As Variant
    For Each dataItem In oJSON("data")
        symbolName = dataItem("symbol")
        openValue = dataItem("open")
        highValue = dataItem("high")
        lowValue = dataItem("low")
        Cells(i, 1) = symbolName
        Cells(i, 2) = openValue
        Cells(i, 3) = highValue
        Cells(i, 4) = lowValue
        i = i + 1
    Next
End Sub

Comments

[drec4s]

Do you have the JsonConverter module imported to the VBA project?

[Linga]

Yes i have it still getting the same error

[Pᴇʜ]

I highly recommend to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.