Results 1 to 8 of 8

Calculate distance between two addresses

Threaded View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Calculate distance between two addresses

    Hello everyone
    I have the following UDF that calculates the distance between two addresses
    Public Function GetDistance(start As String, dest As String)
        Dim firstVal As String, secondVal As String, lastVal As String
        firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
        secondVal = "&destinations="
        lastVal = "&mode=car&language=pl&sensor=false"
        Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
        objHTTP.Open "GET", URL, False
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        objHTTP.send ("")
        If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
        Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
        Set matches = regex.Execute(objHTTP.responseText)
        tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
        GetDistance = CDbl(tmpVal)
        Exit Function
    ErrorHandl:
        GetDistance = -1
    End Function
    But it is very slow and most of the time it doesn't return correct results ..
    There is a sample of the origonal file .. the original file has more then 15000 addresses ..
    Is there a faster way ?
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to calculate distance between coordinates
    By madelingirly in forum Excel General
    Replies: 4
    Last Post: 10-01-2015, 02:00 PM
  2. multi IF THEN with text AND distance between 2 addresses
    By kaczynski in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2015, 03:08 AM
  3. Calculating distance between addresses
    By funkmeister79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2015, 01:06 PM
  4. Distance between 2 addresses
    By kaczynski in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2015, 05:51 AM
  5. Freight Calculate per distance
    By mohammedalkhatim in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 11-06-2013, 06:30 AM
  6. Calculate distance
    By mukesh mishra in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2013, 06:23 AM
  7. [SOLVED] Calculate Distance Between Two Zip Codes
    By XUMuskies in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2013, 01:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1