Parsing a JSON Object in VBA [duplicate]

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: Issues("total"). — Florent B.
– Florent B., Commented Apr 18, 2018 at 8:17

omegastripes · Accepted Answer · 2018-04-18 11:49:11Z

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, 2018 at 11:49

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

answered Apr 18, 2018 at 4:57

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pmeeks Over a year ago

Thanks for your reply. This worked! Does exactly what I need.

Tim Williams Over a year ago

@omegastripes - thanks for the fix

Collectives™ on Stack Overflow

Parsing a JSON Object in VBA [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related