CODEWITHSUNDEEP
jsonvbaexcelparsing
Zach Rochon
Zach Rochon

Reputation: 95

Trouble parsing JSON with vba

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}]}

Upvotes: 3

Views: 15856

Answers (2)

Parfait
Parfait

Reputation: 107577

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

Upvotes: 1

omegastripes
omegastripes

Reputation: 12602

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:

immediate

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

Upvotes: 3

Related Questions