Search code examples
jsonexcelvba

Get specific json value

I am creating a quick VBA script to decipher a json response. This is a work laptop so can't install any other extensions.

Via API I get a JSON responds. So far all works. But how do I only list the responds for src id 1 (or other):. Desired return would be: "CHEMBL544428"

[{"src_id": "7", "src_compound_id": "31843"}, {"src_id": "1", "src_compound_id": "CHEMBL544428"}, {"src_id": "10", "src_compound_id": "591737"}, {"src_id": "39", "src_compound_id": "CB4389789"}, {"src_id": "22", "src_compound_id": "68551"}, {"src_id": "22", "src_compound_id": "657192"}, {"src_id": "49", "src_compound_id": "PD000053"}, {"src_id": "21", "src_compound_id": "14776141"}, {"src_id": "15", "src_compound_id": "SCHEMBL340474"}, {"src_id": "20", "src_compound_id": "Mianserin-hydrochloride"}, {"src_id": "26", "src_compound_id": "21535-47-7"}, {"src_id": "28", "src_compound_id": "MolPort-003-666-778"}, {"src_id": "31", "src_compound_id": "31005"}, {"src_id": "48", "src_compound_id": "HY-B0188A"}, {"src_id": "14", "src_compound_id": "2X03TN217S"}]

My current code to return the full json:

Function GetCmpd(inchi_key As String) As String

'    inchi_Key = "YNPFMWCWRVTGKJ-UHFFFAOYSA-N"

    Dim url as String 'define the API call
    url = "https://www.ebi.ac.uk/unichem/rest/inchikey/" & inchi_key
    On Error GoTo ErrorHandler

'   Dim funtionAccess As Object
    functionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")

    'return the JSON data
    Cmpd_data = functionAccess.callFunction("WEBSERVICE",Array(url))

    GetCmpd = Cmpd_data
    Exit Function

    ErrorHandler:
        GetCmpd = "Error " & Err
        

End Function
Solution
    • You don't install any extensions. VBA JSON is bas file, you just need to import the module or copy its code.

    VBA-tools / VBA-JSON

    • In VBE add reference : Microsoft Scripting Runtime

    enter image description here

    • Following code read JOSN from cell A1, modify as needed.
    Sub Demo()
    Dim Json As Object
    Set Json = JsonConverter.ParseJson([a1])
    Debug.Print getSrcId(Json, "1")
End Sub

Function getSrcId(oJson As Object, ByVal src_id As String) As String
    Dim oDic
    For Each oDic In oJson
        If oDic("src_id") = src_id Then
            getSrcId = oDic("src_compound_id")
            Exit For
        End If
    Next
End Function