Extract right value from a HTTP response (Google API Distance)

[most]

I found a great implementation of Google distance API at analystcave.com, it works great!
Now I would like to expand it by adding status and destination to the output.
But I don't quite understand how the code does the lookup in the response and there are no help to get at analystcave.

My try to extract destination address, but the output is not what I expect, I only get "20" (as in Sveavägen 20).

Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """destination_addresses"".*?([0-9]+)": regex.Global = False
 Set matches = regex.Execute(objHTTP.responseText)
 tmpVal2 = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
 Debug.Print " Dest: " & tmpVal2

My try to extract status, results in a run-time error.

    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """status"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal3 = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    Debug.Print " Stat: " & tmpVal3

The response...

The whole code, BUT it won't work unless you have a API key.

Sub GDistance()
'Get and update distance
Dim myFrom As String, myTo As String
 myFrom = Range("B5").Value
 myTo = Range("G1").Value
   If myTo = "" Or myFrom = "" Then
     MsgBox "Startplats eller destination saknas!", vbOKOnly + vbExclamation
     Exit Sub
   End If
 MyDistance = Round((GetDistance(myFrom, myTo) / 1000), 1)
 Range("G2").Value = MyDistance
End Sub
'Calculate Google Maps distance between two addresses
'https://analystcave.com/excel-calculate-distances-between-addresses/
Public Function GetDistance(start As String, dest As String)
   Dim firstVal As String, secondVal As String, lastVal As String
   firstVal = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
   secondVal = "&destinations="
   lastVal = "&mode=driving&sensor=false&key=" & Range("gmapkey").Value
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
Debug.Print URL
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal1 = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistance = CDbl(tmpVal1)
    
   'My trial code
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """destination_addresses"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal2 = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    Debug.Print " Dest: " & tmpVal2
   'My trial code
    
    Exit Function
ErrorHandl:
    GetDistance = -1
End Function

 

[John_w]

I would never use Regex to parse something as complicated as this data.

Why not use a JSON converter, for example VBA-JSON, to parse the response? There are examples on this forum showing how to use it.

Alternatively, here is code which requests and parses the XML response from the Distance Matrix API:

Get Google Maps XML data, parse it and input to Excel cells

With help from Stackoverflow I managed to get a working solution for getting Google Maps XML data, parsing it and inputting to Excel cells. However my next intention get it working for several requests when there are 5 different locations to be analyzed and data from each XML should be inputted...

www.mrexcel.com

 

[most]

Thanks, will look into it.