Sub
Entfernung()
Dim
r
As
String
, Distanz
As
String
, Zeit
As
String
Dim
p1
As
Long
, p2
As
Long
For
i = 2
To
Cells(Rows.Count, 1).
End
(xlUp).Row
r = GetRoute(Range(
"A"
& i), Range(
"B"
& i))
p1 = InStr(1, r,
"distance"
)
If
p1 > 0
Then
p1 = InStr(p1, r,
"text"
)
If
p1 > 0
Then
p2 = InStr(p1, r,
","
)
If
p1 > 0
And
p2 > 0
Then
Distanz = Mid(r, p1 + 9, p2 - p1 - 10)
Range(
"C"
& i) = Distanz
End
If
If
p1 > 0
Then
p1 = InStr(1, r,
"duration"
)
If
p1 > 0
Then
p1 = InStr(p1, r,
"text"
)
If
p1 > 0
Then
p2 = InStr(p1, r,
","
)
If
p1 > 0
And
p2 > 0
Then
Zeit = Mid(r, p1 + 9, p2 - p1 - 10)
Range(
"D"
& i) = Zeit
End
If
Next
i
End
Sub
Public
Function
GetRoute(Start
As
String
, Ziel
As
String
)
As
String
Dim
Link
As
String
Application.Wait Now + TimeValue(
"0:00:01"
)
With
CreateObject(
"WinHttp.WinHttpRequest.5.1"
)
.Open
"GET"
, Link &
"origin="
& Start &
"&destination="
& Ziel,
False
.send
GetRoute = .responsetext
End
With
End
Function