Thanks, although I could do with not having to go off Lon/lat and just off standard Uk post codes.....been trying to use the below Macro but its returning values of 0?
Public Function getGoogDistanceTime(rngSAdd As Range, rngEAdd As Range, Optional strReturn As String = "distance") As Variant
Dim sURL As String
Dim BodyTxt As String
Dim vUnits As Variant
Dim dblTemp As Double
Dim bUnit As Byte
sURL = "
http://maps.google.co.uk/maps?hl=en&tab=wl"
sURL = sURL & "&saddr=" & Replace(rngSAdd(1).Value, " ", "+")
sURL = sURL & "&daddr=" & Replace(rngEAdd(1).Value, " ", "+")
sURL = sURL & "&hl=en"
BodyTxt = getHTML(sURL)
If InStr(1, BodyTxt, strReturn, vbTextCompare) = 0 Then
getGoogDistanceTime = "Error"
Else
getGoogDistanceTime = parseGoog(strReturn, BodyTxt)
If LCase(strReturn) Like "time*" Then
vUnits = Split(getGoogDistanceTime)
For bUnit = LBound(vUnits) To UBound(vUnits) - 1 Step 2
dblTemp = dblTemp + _
Val(vUnits(bUnit)) / Choose(InStr(1, "hms", Left(vUnits(bUnit + 1), 1), vbTextCompare), 24, 1440, 86400)
Next bUnit
getGoogDistanceTime = dblTemp
Else
getGoogDistanceTime = Val(getGoogDistanceTime)
End If
End If
End Function
Public Function getHTML(strURL As String) As String
Dim oXH As Object
Set oXH = CreateObject("msxml2.xmlhttp")
With oXH
.Open "get", strURL, False
.send
getHTML = .responseText
End With
Set oXH = Nothing
End Function
Public Function parseGoog(strSearch As String, strHTML As String) As String
strSearch = strSearch & ":'"
If InStr(1, strHTML, strSearch) = 0 Then
parseGoog = "Not Found"
Exit Function
Else
parseGoog = Mid(strHTML, InStr(1, strHTML, strSearch) + Len(strSearch))
parseGoog = Mid(parseGoog, 1, InStr(1, parseGoog, """") - 1)
End If
End Function
