Trouble parsing JSON with vba

Question

I can get what appears to be a valid JSON string from a web query, however, I cannot set items correctly for the life of me. Need to confirm that I'm not losing my mind...

'Call for available reports

Dim URLReporta As String
Dim JSONa As Object
Dim var As Object  
Set myrequesta = CreateObject("winhttp.winhttprequest.5.1")
URLReporta = ("https://secure.saashr.com:443/ta/rest/v1/reports?type=Saved&company%3shortname=" & Company)
myrequesta.Open "GET", URLReporta, False
myrequesta.setRequestHeader "Accept", "application/json"
myrequesta.setRequestHeader "Authentication", "Bearer " & Token
myrequesta.setRequestHeader "Content-Type", "application/json"
myrequesta.Send
Set JSONa = JsonConverter.ParseJson(myrequesta.responseText)
Set var = JSONa("SavedName")
Debug.Print var.Count

I get an error on the line Set var = JSONa("SavedName"):

run-time error '424': object required

myrequesta.responseText value is as follows:

{"reports":[{"SavedName":"This Year","SettingId":18959322},{"SavedName":"Time Off Requests","SettingId":18960210},{"SavedName":"Calc Hours Summary","SettingId":18960209},{"SavedName":"roster","SettingId":18960211},{"SavedName":"E/D/T","SettingId":18823042},{"SavedName":"TestZDR","SettingId":18957188}]}

I cannot set items correctly...set what items? What are your errors? What do you want to parse? What is desired output? In short, what is your question? — Parfait
– Parfait, Commented Jun 19, 2016 at 0:17
It looks like you're using github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas ? — ThunderFrame
– ThunderFrame, Commented Jun 19, 2016 at 0:21
updated the code above, I get run-time error '424': object required on line "Set var = JSONa("SavedName") — Zach Rochon
– Zach Rochon, Commented Jun 19, 2016 at 14:31
If your JSON string could be converted to XML structure, you will be able to use XPath expression like //property[@name = "SavedName"] to get a collection of all nodes representing SavedName properties. — omegastripes
– omegastripes, Commented Jun 19, 2016 at 16:20
Did you know ScriptControl.Eval("("+ json + ")" ) will parse object and one can use CallByName to access elements. See my answer here stackoverflow.com/questions/37711034/… — S Meaden
– S Meaden, Commented Jul 23, 2016 at 22:03

omegastripes · Accepted Answer · 2016-06-19 15:44:45Z

3

The structure returned by JsonConverter.ParseJson function doesn't work such way. For your particular JSON it contains 3 levels: Root-level object has only one property reports, which contains second-level array, which in turn contains 6 third-level objects, having properties SavedName and SettingId. You are trying to get third-level's object property value from root-level object.

First you need to get second-level array, then loop through it's elements, containing objects, and retrieve the SavedName properties' values of that objects. Here is the example:

'Call for available reports

Dim URLReporta As String
Dim JSONa As Object
Dim var As Object
Dim rep As Variant
Set myrequesta = CreateObject("winhttp.winhttprequest.5.1")
URLReporta = ("https://secure.saashr.com:443/ta/rest/v1/reports?type=Saved&company%3shortname=" & Company)
myrequesta.Open "GET", URLReporta, False
myrequesta.setRequestHeader "Accept", "application/json"
myrequesta.setRequestHeader "Authentication", "Bearer " & Token
myrequesta.setRequestHeader "Content-Type", "application/json"
myrequesta.Send
Set JSONa = JsonConverter.ParseJson(myrequesta.responseText) ' root level object
Set var = JSONa("reports") ' second level array
For Each rep In var ' third level objects
    Debug.Print rep("SavedName") ' property "SavedName" value of current third level object
Next

Here is the output:

If you want just to get the number of reports, then get the array and the number of elements in it:

Debug.Print JSONa("reports").Count

answered Jun 19, 2016 at 15:44

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

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

3 Comments

Zach Rochon Over a year ago

Thank you! That did the trick in unwinding how that parser is intended to work.

S Meaden Over a year ago

Did you know ScriptControl.Eval("("+ json + ")" ) will parse object and one can use CallByName to access elements. See my answer here stackoverflow.com/questions/37711034/…

omegastripes Over a year ago

@SMeaden that approach makes the system vulnerable in some cases (malicious code from the web query may be executed), take a look at this answer, especially update part.

Parfait · Accepted Answer · 2016-06-19 16:02:15Z

JSON objects can be thought of as a collection of dictionaries. So you have to walk through the inner values such as SavedName to retrieve whole dictionary objects (all SavedName values) or specific string values at indexed locations (one SavedName value):

Public Sub GetJSONRequest()
    Dim jsonStr As String

    jsonStr = "{" _
         & "     ""reports"": [{" _
         & "         ""SavedName"": ""This Year""," _
         & "         ""SettingId"": 18959322" _
         & "     }, {" _
         & "         ""SavedName"": ""Time Off Requests""," _
         & "         ""SettingId"": 18960210" _
         & "     }, {" _
         & "         ""SavedName"": ""Calc Hours Summary""," _
         & "         ""SettingId"": 18960209" _
         & "     }, {" _
         & "         ""SavedName"": ""roster""," _
         & "         ""SettingId"": 18960211" _
         & "     }, {" _
         & "         ""SavedName"": ""E/D/T""," _
         & "         ""SettingId"": 18823042" _
         & "     }, {" _
         & "         ""SavedName"": ""TestZDR""," _
         & "         ""SettingId"": 18957188" _
         & "  }]" _
         & "  }"

    Dim JSONa As Object, element As Object, e As Variant, i As Variant, var As Variant

    Set JSONa = ParseJson(jsonStr)

    ' DICTIONARY OBJECT
    Set element = CreateObject("Scripting.Dictionary")
    Set element = JSONa("reports")

    ' OUTER DICTIONARY
    For Each e In element
        ' INNER COLLECTION OF DICTIONARIES
        For Each i In e
            Debug.Print i & " " & e(i)
        Next i
    Next e            

    ' STRING VALUE OF FIRST SAVEDNAME VALUE
    var = JSONa("reports")(1)("SavedName")
    Debug.Print var

    Set element = Nothing
    Set JSONa = Nothing
End Sub

Output

SavedName This Year
SettingId 18959322
SavedName Time Off Requests
SettingId 18960210
SavedName Calc Hours Summary
SettingId 18960209
SavedName roster
SettingId 18960211
SavedName E/D/T
SettingId 18823042
SavedName TestZDR
SettingId 18957188
This Year

Collectives™ on Stack Overflow

Trouble parsing JSON with vba

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related