If you want to find the distance between the two cities using the Google map with VBA Excel.
Here is the udf
Public Function getGoogDistanceTime(startAddr As String, startCity As String, _
startState As String, startZip As String, endAddr As String, _
endCity As String, endState As String, endZip As String) As String
Dim sURL As String
Dim BodyTxt As String
Dim apan As String
Dim oXH As Object
sURL = sURL & Replace(startAddr, " ", "+") & ",+" & Replace(startCity, " ", "+") & ",+" & startState
sURL = sURL & "&daddr=" & Replace(endAddr, " ", "+") & ",+" & Replace(endCity, " ", "+") & ",+" & endState
sURL = sURL & "&hl=en"
Set oXH = CreateObject("msxml2.xmlhttp")
With oXH
.Open "get", sURL, False
.Send
BodyTxt = .RESPONSETEXT
End With
apan = Application.WorksheetFunction.Trim(apan)
apan = Mid(BodyTxt, InStr(1, BodyTxt, "<div class=""altroute-rcol altroute-info"">") + 49, 200)
apan = Left(apan, InStr(1, apan, "</span> </div>") - 1)
apan = Replace(apan, "</span>", "")
apan = Replace(apan, "<span>", "")
getGoogDistanceTime = apan
Set oXH = Nothing
End Function
How to use
For example
Cell A2 is havingFrom Street Addres
Cell B2 is havingFrom City
Cell C2 is havingFrom State
Cell D2 is havingFrom Zip Code
Cell E2 is havingTO Street Addres
Cell F2 is havingTO City
Cell G2 is havingTO State
Cell H2 is havingTO Zip Code
In I2 Type UDF=getGoogDistanceTime(A2,B2,C2,D2,E2,F2,G2,H2)
Comments