Extracting results from an API call with a VBA function

Question

I'm just starting to work with JSON in one of my macros. I'm able to send a call to an API service and it returns results. The service has been set up to return something like 25 fields (?). I don't need all of the fields, just some of them. The macro uses VBA.

I'm able to write the results to a specific worksheet with the following code:

For Each result In jsonObject
  With Sheets("Sheet1")
    .Cells(r, 5).Value = result("firstName")
    .Cells(r, 6).Value = result("lastName")
    .Cells(r, 9).Value = result("userCodeInfo")("userCode")
    .Cells(r, 10).Value = result("userCodeInfo")("previousUserCode")
    .Cells(r, 28).Value = result("saleType")
    .Cells(r, 29).Value = result("cost")
  End With
Next

Later, in the code, a decision point needs to use to one of the fields in the results. I'm trying to use a function to return that field value:

Public Function APIUnitData_Read(MyField As String)
Dim JSONConverter As New clsJSONParser
  Set jsonObject = JSONConverter.ParseJson(UnitResp)
  For Each result In jsonObject
    APIUnitData_Read = result(MyField)
  Next
End Function

The function works well when the field is a "non-grouped" field, e.g., "firstName, "lastName", etc.

vDecPt = API.APIUnitData_Read("firstName")

It doesn't work (or at least I haven't gotten it to work with the "grouped" fields; e.g., ("userCodeInfo")("userCode") and ("userCodeInfo")("previousUserCode").

vDecPt = API.APIUnitData_Read("userCodeInfo")("PreviousUseCode")

I'm guessing I don't have the right combination of parentheses, double quotes, single quotes, etc. I've tried a number of different combinations without success. I'm sure my lack of experience is also in play here.

Any suggestions or advice how to resolve this issue would be greatly appreciated. Thanks for taking the time to review this question and for any help you can provide......

user_478884 · Answer

If you're asking about accessing nested values with a single call then maybe you can try something like this:

Sub tester()
    
    Dim json As Object
    Set json = JsonConverter.ParseJson(JsonContent)
    
    Debug.Print JSONValue(json, "name")                ' fred
    Debug.Print JSONValue(json, "addresses/1/city")    ' NYC
    Debug.Print JSONValue(json, "addresses/2/street")  ' Rue blah
    Debug.Print JSONValue(json, "values/4")            ' 40 

End Sub

'return a "leaf" value from a `json` object
Public Function JSONValue(json As Object, MyField As String)
    Dim res, arr, i, v
    Set res = json
    arr = Split(MyField, "/") 'array of keys/indexes
    For i = LBound(arr) To UBound(arr)
        v = arr(i)
        If TypeName(res) = "Collection" Then v = CLng(v) 'numeric index for collection
        If i <> UBound(arr) Then
            'not at the end yet so have either a dictionary or a collection
            Set res = res(v)
        Else
            'accessing a single non-object value
            JSONValue = res(v)
        End If
    Next i
End Function

'dummy JSON content
Function JsonContent()
    JsonContent = Replace("{'name':'fred','addresses':" & _
                         "[{'city':'NYC','street':'Easy St'}," & _
                         "{'city':'Paris','street':'Rue blah'}]," & _
                         "'values':[10,20,30,40]}", "'", """")
End Function

Very basic but should work if you just want a single non-object return value which might be nested several fields deep.

Extracting results from an API call with a VBA function

1 Answers