How to get JSON result into a cell?

[varcorb]

Hello,

I have a JSON URL "https://maps.googleapis.com/maps/api/distancematrix/json?origins=Aurora+CO&destinations=Willis+TX&mode=driving&language=en_US" That Results Like

{   "destination_addresses" : [ "Willis, TX, USA" ],   "origin_addresses" : [ "Aurora, CO, USA" ],   "rows" : [      {         "elements" : [            {               "distance" : {                  "text" : "1,577 km",                  "value" : 1577246               },               "duration" : {                  "text" : "14 hours 22 mins",                  "value" : 51716               },               "status" : "OK"            }         ]      }   ],   "status" : "OK"}

I am trying to get the Distance Text into a Cell? How can i do that in Excel?

Thank You for all of your help!

 

[pgc01]

Hi

This is not a general solution. This code parses directly the JSON string assuming it has the structure like you posted and extracts the value into Sheet1!A2:

Sub Get_JSON_Goals()
Dim regex As RegExp, regexMatches As MatchCollection
Dim sJSON As String
Dim rOut As Range

' read the JSON text into a string
sJSON = Get_JSON_Text

' get rid of newline characters
sJSON = Replace(sJSON, vbLf, " ")

Set rOut = Worksheets("Sheet1").Range("B1")

Set regex = New RegExp
With regex
    .Pattern = """distance""\s*:\s*{\s*""text""\s*:\s*""([^""]*)"""
    Set regexMatches = .Execute(sJSON)

    ' if no goals are found exit
    If regexMatches.Count = 0 Then
        MsgBox "Not found"
        Exit Sub
    End If

    ' output write distance text
    rOut.Value = regexMatches(0).SubMatches(0)

End With
End Sub


Function Get_JSON_Text() As String
Dim IXMLHTTPReq As MSXML2.xmlhttp

Set IXMLHTTPReq = New MSXML2.xmlhttp

With IXMLHTTPReq
    .Open "GET", "https://maps.googleapis.com/maps/api/distancematrix/json?origins=Aurora+CO&destinations=Willis+TX&mode=driving&language=en_US", False
    .send
    Get_JSON_Text = .responseText
End With
    
End Function