CODEWITHSUNDEEP
jsonexcelvbaparsing
Ashley Davies
Ashley Davies

Reputation: 25

VBA Parsing with Json

I am trying to parse this with JsonConverter.bas but everything i have tried is not working, I am coming to the conclusion that this is not json but i cannot figure else what it is and well how to parse it!

"{
  ""success"": true,
  ""delivered"": true,
  ""contactDetailsRequired"": false,
  ""message"": ""Signed For by: 29 CARDIFF EARLY MAI"",
  ""signature"": ""https://webservices.thedx.co.uk/PodImage/ImageHandler.ashx?tn=505012368126"",
  ""date"": ""08-02-2018"",
  ""serviceLevelName"": ""Tracked Mail"",
  ""time"": ""07:30:00"",
  ""trackedProductName"": ""TMS""
}"

I am trying to get each value as a string or an array which then i will insert on sheet, here is the code I have been playing with,

 Dim httpObject As Object
 Set httpObject = CreateObject("MSXML2.XMLHTTP")

 sURL = "https://www.dxdelivery.com/umbraco/Api/TrackingApi/TrackingData?trackingNumber=505012368126&postcode=&trackingType=0"

 sRequest = sURL
 httpObject.Open "GET", sRequest, False
 httpObject.send
 sgetresult = httpObject.responseText
MsgBox (sgetresult)

Sheets("sheet1").Range("A1") = sgetresult

Dim FSO As New FileSystemObject
Dim JsonTS As TextStream
Dim JsonText As String
Dim Parsed As Dictionary

' Read .json file
' Parse json to Dictionary
' "values" is parsed as Collection
' each item in "values" is parsed as Dictionary
Set Parsed = JsonConverter.ParseJson(sgetresult)

MsgBox Parsed("""success""")

Hope you can help, Thank you.

Upvotes: 0

Views: 767

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

If the JsonConverter.bas (https://github.com/VBA-tools/VBA-JSON) is properly installed, then the following works for me:

Sub test()

 Dim httpObject As Object
 Set httpObject = CreateObject("MSXML2.XMLHTTP")

 sURL = "https://www.dxdelivery.com/umbraco/Api/TrackingApi/TrackingData?trackingNumber=505012368126&postcode=&trackingType=0"

 sRequest = sURL
 httpObject.Open "GET", sRequest, False
 httpObject.send
 sGetResult = httpObject.responseText

 Set oJSON = JsonConverter.ParseJson(sGetResult)

 MsgBox oJSON("success")

 For Each sItem In oJSON
  MsgBox sItem & " = " & oJSON(sItem)
 Next

End Sub

Upvotes: 1

Related Questions