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