Reading values from Google Maps distance matrix in visual Basic

I am using the google maps distance matrix and I am trying to read the values in miles and display it into the excel file. I get information like this

{
   "destination_addresses" : [ "Destination" ],
   "origin_addresses" : [ "Origin" ],
   "rows" : [
      {
         "elements" : [
            {
               "distance" : {
                  "text" : "38.1 mi",
                  "value" : 61252
               },
               "duration" : {
                  "text" : "44 mins",
                  "value" : 2668
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}

My Code is

Dim orgin As String
origin = Cells(2, 1)

Dim destination As String
destination = Cells(2, 2)

Dim apikey As String
apikey = Cells(2, 4)

Dim strUrl As String
    strUrl = "https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=" & origin & "&destination=s" & destination & "&key=" & apikey
     
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    With httpReq
         .Open "GET", strUrl, False
         .Send
    End With
     
    Dim response As String
    response = httpReq.ResponseText
     
    Dim parsed As Dictionary
    Set parsed = JsonConverter.ParseJson(response)
     
    Dim miles As String
     
        miles = parsed("rows")(0)("elements")(0)("distance")("text")
     
    Cells(2, 5).Value = miles

I am confused as to how to navigate the webpage and obtain the value in miles. Thank you for helping me out.

Source: Ask Javascript Questions

LEAVE A COMMENT