Function GetDuration(destination As String) As String
Dim apiKey As String
Dim url As String
Dim responseText As String
Dim objHTTP As Object
Dim distance As String
origin = "WA7 5PS"
' Replace "YOUR_API_KEY" with your actual API key
apiKey = "YOUR_API_KEY"
url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & _
origin & "&destinations=" & destination & "&units=imperial&key=" & apiKey
' Send HTTP request to the Distance Matrix API
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
objHTTP.Open "GET", url, False
objHTTP.send
responseText = objHTTP.responseText
' Parse the JSON response to extract distance
If InStr(responseText, "OK") > 0 Then
duration = Split(Split(responseText, """text"" : """)(2), """")(0)
Else
duration = "0 hours 0 mins"
End If
If InStr(duration, "hours") > 0 Then
hours = Left(duration, InStr(duration, "hours") - 2)
minutes = Mid(duration, InStr(duration, "hours") + 6, Len(duration) - InStr(duration, "hours") - 9)
ElseIf InStr(duration, "mins") > 0 Then
hours = 0
minutes = Left(duration, InStr(duration, "mins") - 2)
End If
' Return the distance=get
GetDuration = Format(hours, "00") & ":" & Format(minutes, "00")
End Function