jsonvbaexceljscript

Accessing a Variant/Object/JScriptTypeInfo property in VBA


I have the following code VBA code (for an Excel module)

Function getDesc(ByVal pCode As String) As String
    Dim oRequest As Object
    Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    oRequest.Open "GET", "https://my.url.com/?filter=CODE=" & pCode, False
    oRequest.SetRequestHeader "Accept", "application/json"
    oRequest.Send ""

    Set props = jsonDecode(oRequest.ResponseText)

    getDesc = props.row_data
End Function

Function jsonDecode(jsonString As Variant)
    Set sc = CreateObject("ScriptControl"): sc.Language = "JScript"
    Set jsonDecode = sc.Eval("(" + jsonString + ")")
End Function

The results of props.row_data is as shown enter image description here

I can't figure out how to assign the property LONG_DESCRIPTION to getDesc =

What's the proper syntax I need? Alternatively, I will accept just about any solution that will allow me to return the String of LONG_DESCRIPTION.


Solution

  • Try the below code. Import JSON.bas module into the VBA project for JSON processing.

    Option Explicit
    
    Function getDesc(ByVal pCode As String) As String
    
        Dim oRequest As Object
        Dim sJSONString As String
        Dim vJSON As Variant
        Dim sState As String
    
        Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
        oRequest.Open "GET", "https://my.url.com/?filter=CODE=" & pCode, False
        oRequest.SetRequestHeader "Accept", "application/json"
        oRequest.Send
        sJSONString = oRequest.ResponseText
        JSON.Parse sJSONString, vJSON, sState
        getDesc = vJSON("row_data")(0)("LONG_DESCRIPTION")
    
    End Function
    

    Tested with JSON string {'row_data':[{'LONG_DESCRIPTION':'desc_string'}]}.