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

omegastripes
  • 12,351
  • 4
  • 45
  • 96
Matúš Porubčan
  • 21
  • 2
  • this might help https://stackoverflow.com/questions/10311361/accessing-json-object-keys-having-spaces – Maddy Mar 21 '18 at 08:40
  • I tried adding brackets - ["Item 1"] instead of "Item 1" but it doesn't seem to help – Matúš Porubčan Mar 21 '18 at 09:51
  • where is "Item 1" in the above? Not a chance at present I would be able to reproduce this :-( – QHarr Mar 21 '18 at 09:56
  • @QHarr It's fifth line from bottom of my code. JSON's structure looks like this: [https://imgur.com/a/LKnZU](https://imgur.com/a/LKnZU) – Matúš Porubčan Mar 21 '18 at 10:58
  • my bad, can see it now – QHarr Mar 21 '18 at 10:59
  • What is the content of the `H.responsetext`? And what is the expected output? Please add it to the question by editing. Check [MCVE](https://stackoverflow.com/help/mcve). – omegastripes Apr 24 '18 at 21:13
  • You may use this [VBA JSON parser](https://github.com/omegastripes/VBA-JSON-parser), it represents JSON content as a structure of native VBA arrays and Dictionary objects, so you may just get the value like `a = vJSON("0")("3")("buckets")("Item 1")("1")("value")`. – omegastripes Aug 31 '19 at 19:59

0 Answers0