2

I managed to put together VBA code that parses specific JSON:

Set S = CreateObject("ScriptControl")
S.Language = "JScript"
S.AddCode "function keys(O) { var k = new Array(); for (var x in O) { k.push(x); } return k; } "
Set JSON = S.Eval("(" & H.responsetext & ")")

Set JSON = CallByName(JSON, "responses", VbGet)
Set JSON = CallByName(JSON, "0", VbGet)
Set JSON = CallByName(JSON, "aggregations", VbGet)
Set JSON = CallByName(JSON, "2", VbGet)
Set JSON = CallByName(JSON, "buckets", VbGet)
Set Keys = S.Run("keys", JSON)

Dim jsonWS As String, jsonAA As String, jsonTS As String

  For Each Key In Keys
    Set TableRow = CallByName(JSON, Key, VbGet)
        jsonWS = CallByName(TableRow, "key", VbGet)
    Set TableRow = CallByName(TableRow, "3", VbGet)
    Set TableRow = CallByName(TableRow, "buckets", VbGet)
    Set TableRow = CallByName(TableRow, "Item 1", VbGet)
        jsonAA = CallByName(TableRow, "key", VbGet)
    Set TableRow = CallByName(TableRow, "1", VbGet)
        jsonTS = CallByName(TableRow, "value_as_string", VbGet)
  Next Key

However I ran into a problem. When I run this code it stops at line referencing "Item 1" Set TableRow = CallByName(TableRow, "Item 1", VbGet) and throws below error:

"Object doesn't support this property or method"

I guess there's a problem when working with names containing spaces. I've tried multiple brackets, quotes, it didn't work. I can't solve this problem as my javascript knowledge is non-existent.

JSON structure looks like:

JSON structure

Improve this question
7
  • this might help stackoverflow.com/questions/10311361/… Commented Mar 21, 2018 at 8:40
  • I tried adding brackets - ["Item 1"] instead of "Item 1" but it doesn't seem to help Commented Mar 21, 2018 at 9:51
  • where is "Item 1" in the above? Not a chance at present I would be able to reproduce this :-( Commented Mar 21, 2018 at 9:56
  • @QHarr It's fifth line from bottom of my code. JSON's structure looks like this: https://imgur.com/a/LKnZU Commented Mar 21, 2018 at 10:58
  • my bad, can see it now Commented Mar 21, 2018 at 10:59
Related questions
87
Parsing JSON in Excel VBA
6
Parsing a JSON object array in Excel VBA
0
How do I preserve spaces in retrieving json keys?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.