i am trying to find the distance and travel time between multiple origins and destinations. For some reason, my code does not work at all. There are no errors, i just have nothing as output. the problem is that activex can not create an object.
The code is as follows:
#########
Sub Origins_Destinations()
Dim a, b, i, Str As String
Dim lineS As Variant
'On Error Resume Next
'Application.ScreenUpdating = False
With CreateObject("WinHttp.WinHttpRequest")
Dim iRow As Long: iRow = ThisWorkbook.Worksheets(1).Range("g65000").End(xlUp).Row
For j = 4 To iRow
b = ThisWorkbook.Worksheets(1).Range("b4" & j)
a = ThisWorkbook.Worksheets(1).Range("a4" & j)
.Open "GET", "https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=" & a & " &destinations= " & b & " &key=MY_KEY", False
.Send
lineS = Split(.ResponseText, vbLf)
For k = 25 To UBound(lineS)
If Trim(lineS(k)) = """distance"" : {" Then
Exit For
End If
Next k
ThisWorkbook.Worksheets(1).Range("c" & j) = lineS(k + 1)
ThisWorkbook.Worksheets(1).Range("d" & j) = lineS(k + 5)
Application.Wait (Now + TimeValue("0:00:01"))
Next j
End With
Application.ScreenUpdating = True
End Sub
#########
Any suggestions ??
Bookmarks