Parsing a JSON Object in VBA

Question

I am parsing a JSON object in VBA. I found a code example on this forum that works great. However, one of the fields in the object is "total". in my code, I am trying to get the total, but "total" is being switched to "Total". I get an error 438 at runtime. Here is the code snipit:

Dim Issues As Object
Dim scriptControl As Object

Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
scriptControl.Language = "JScript"

Set ObjHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

URL = "http://<url>.com:10005/rest/api/2/search"

ObjHTTP.Open "POST", URL, False
ObjHTTP.SetRequestHeader "Authorization", "Basic <userid / password>"
ObjHTTP.SetRequestHeader "Content-Type", "application/json"

'Get the new work intake for the month.
ObjHTTP.send ("{""jql"":""<my query here>"":[""id""],""maxResults"":1}")
strX = ObjHTTP.responsetext
Set Issues = scriptControl.Eval("(" + strX + ")")

With Sheets("Test")
    .Cells(1, 1) = Issues.expand  --this works
    .Cells(1, 2) = Issues.startAt --this works
    .Cells(1, 3) = Issues.maxResults -- this works
    .Cells(1, 4) = Issues.Total  -- JSON object is "total"  editor keeps switching to "Total"
End With

Does anyone know why I cannot use Issues.total?

VBA is a case insensitive language and the JScript object `Issues` is case sensitive. Either use `CallByName(Issues, "total", vbGet)` or use a [JSON parser](https://gist.github.com/florentbr/12c90342af901b39350eaee6a72bed3d#file-1-jsonre-bas): `Issues("total")`. — Florent B., Apr 18 '18 at 08:17

score 1 · Accepted Answer · edited Apr 18 '18 at 11:49

1

Borrowing from the linked question, something like this should work:

'.... 
strX = ObjHTTP.responsetext
scriptControl.Eval("var o = (" + strX + ");")
scriptControl.AddCode "function getProp(propName) { return o[propName]; } "

With Sheets("Test")
    .Cells(1, 1) = scriptControl.Run("getProp", "expand")  
    .Cells(1, 2) = scriptControl.Run("getProp", "startAt")
    .Cells(1, 3) = scriptControl.Run("getProp", "maxResults")
    .Cells(1, 4) = scriptControl.Run("getProp", "total")
End With

Having said that though, you should instead use this code: https://github.com/VBA-tools/VBA-JSON

edited Apr 18 '18 at 11:49

omegastripes

12,351
4
45
96

answered Apr 18 '18 at 04:57

Tim Williams

154,628
8
97
125

Thanks for your reply. This worked! Does exactly what I need. – pmeeks Apr 18 '18 at 15:53
@omegastripes - thanks for the fix – Tim Williams Apr 18 '18 at 16:21

Parsing a JSON Object in VBA

1 Answers1