Route afstand berekenen in Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

Public Function GetPage(sLink As String) As XMLHTTP40
    Dim oObj As MSXML2.XMLHTTP40
    Set oObj = New XMLHTTP40
    oObj.Open "GET", sLink, False
    oObj.send ""
    Set GetPage = oObj
End Function

Public Function GetDistanceBetweenPostCodes(Code1 As String, Code2 As String)
    Dim oResult As XMLHTTP40
    Dim oDOM As DOMDocument40
    Dim sStr As String
    Dim sResult As String
    Dim dResult As Variant
    Dim sURL As String
    Const sKeyWords As String = "km</span>"
    sURL = "http://maps.google.nl/maps?f=d&hl=nl&geocode=&saddr="
    sURL = sURL & Code1 & "+to:"
    sURL = sURL & Code2
    Set oResult = GetPage(sURL)
    Set oDOM = New DOMDocument40
    sStr = oResult.responseText
    sResult = Mid(sStr, InStr(sStr, sKeyWords) - Len(sKeyWords), Len(sKeyWords))
    sResult = Replace(sResult, "<", "")
    sResult = Replace(sResult, "s", "")
    sResult = Replace(sResult, "p", "")
    sResult = Replace(sResult, "a", "")
    sResult = Replace(sResult, "n", "")
    sResult = Replace(sResult, ">", "")
    sResult = Replace(sResult, " ", "")
    dResult = CDec(sResult)
    GetDistanceBetweenPostCodes = dResult
End Function