Get Distance between two Cities Using Google Map

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
              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)


This post is locked
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ