Accessing a Variant/Object/JScriptTypeInfo property in VBA

user_708969 0
Viewed 4674

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.

jscript vba excel json
2 Answers

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

user_21657590

You don't need external libraries to achieve this:

Option Explicit

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)

    Set sc = CreateObject("ScriptControl"): sc.Language = "JScript"
    sc.AddCode "function jsGetDesc(obj) { return obj[0]['LONG_DESCRIPTION']; }"
    getDesc = sc.Run("jsGetDesc", props.row_data)
End Function
user_114599530
Related