Iterating through nested objects with VBJSON

Question

I am attempting to connect to the SmartSheet API through VBA to pull the contents into an Excel sheet. I found the VBJSON library which has helped me a bit but I am struggling with iterating through the objects and pulling specific values.

I want to access the contents of the "Value" attribute for each row then do the same for subsequent rows. My biggest problem is that I do not know how this VBJSON library works since I cannot find any documentation on it and there are only a few examples and they deal with relatively straightforward JSON examples.

Desired Output

Row 1 Column 1 Content | Row 1 Column 2 Content
Row 2 Column 1 Content | Row 2 Column 2 Content

JSON

{
"id": 1,
"name": "Sheet Name",
"columns": [
    {
        "id": 1,
        "index": 0,
        "title": "Title of Column",
        "type": "TEXT_NUMBER",
        "primary": true
    },
    {
        "id": 2,
        "index": 1,
        "title": "Title of Second Column",
        "type": "TEXT_NUMBER"
    },

],
"rows": [
    {
        "id": 1,
        "rowNumber": 1,
        "cells": [
            {
                "type": "TEXT_NUMBER",
                "value": "Row 1 Column 1 Content",
                "columnId": 1,
            },
            {
                "type": "TEXT_NUMBER",
                "value": "Row 1 Column 2 Content",
                "columnId": 2,
            },

        ],
        "locked": true,
        "lockedForUser": true,
        "expanded": true,
        "createdAt": "2013-10-11T13:43:24-05:00",
        "modifiedAt": "2013-11-12T15:13:54-06:00"
    },
    {
        "id": 2276445193037700,
        "rowNumber": 2,
        "cells": [
            {
                "type": "TEXT_NUMBER",
                "value": "row 2 column 1 content",
                "columnId": 1,
            },
            {
                "type": "TEXT_NUMBER",
                "value": "row 2 column 2 content",
                "columnId": 2,
            }
        ]
}

VBJSON library http://www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html

Below is code I've pieced together from what I could find online and right now it pulls the values associated with each attribute in the row. But I only need to pull the contents of the "Value" portion and I can't seem to figure out how to do that. I think I really just need help with my for loop because I have the JSON, I have a library that appears to work, I am just struggling to figure out how to combine it all.

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

    Dim strDiv As String, startVal As Long, endVal As Long
    strDiv = xmlHttp.ResponseText
    startVal = InStr(1, strDiv, "rows", 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("rows")(1)("cells")(1)
        Cells(2, i) = p("rows")(1)("cells")(1)(Item)
        i = i + 1
    Next

Try parsing out only the values you want instead of all attributes. See this [link](http://stackoverflow.com/a/8939576/2521004) — Automate This, Nov 18 '13 at 21:01
Use a better JSON library: http://www.vbforums.com/showthread.php?738845-VB6-JsonBag-Another-JSON-Parser-Generator — wqw, Nov 18 '13 at 21:09
Checkout this [**link**](http://stackoverflow.com/questions/16817545/handle-json-object-in-xmlhttp-response-in-excel-vba-code/16851758#16851758) — Santosh, Dec 06 '13 at 10:31
The json is not valid at line 17. Please post valid JSON to be an [MCVE] — QHarr, Jun 27 '18 at 10:11

score 2 · Answer 1 · edited May 23 '17 at 11:52

Ran into a similar problem, see my answer here: https://stackoverflow.com/a/16825736/1240745

This library has been a life-saver for me: https://github.com/VBA-tools/VBA-JSON (previously https://code.google.com/p/vba-json/)

I use it in a library I wrote for accessing Salesforce, Trello, and a few others. (Shameless plug): https://github.com/VBA-tools/VBA-Web

Using the VBA-JSON library, it would involve something like the following:

Dim Parsed As Dictionary
Set Parsed = JsonConverter.ParseJson(xmlHttp.ResponseText)

' Object -> Dictionary, so Row and Cell: Dictionary
Dim Row As Dictionary
Dim Cell As Dictionary

' Array -> Collection, so Parsed("rows"): Collection
For Each Row In Parsed("rows")
    For Each Cell In Row("cells")
        ' Access Dictionary items by key
        Cells(Row("rowNumber"), Cell("columnId")) = Cell("value")
    Next Cell
Next Row

(or something similar)

Iterating through nested objects with VBJSON

1 Answers1