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 — Automate This
– Automate This, Commented Nov 18, 2013 at 21:01
The json is not valid at line 17. Please post valid JSON to be an minimal reproducible example — QHarr
– QHarr, Commented Jun 27, 2018 at 10:11

Community · Accepted Answer · 2017-05-23 11:52:29Z

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)

Collectives™ on Stack Overflow

Iterating through nested objects with VBJSON

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related