Calculate Distance between Zip Codes

[mgirvin]

Dear Smartest Exclers In The World,

I have a table with row headers as source zip code and column headers as destination zip codes and I would like to calculate the distance between the two zip codes. Does anyone know how to do that, maybe a User Defined function or something?

 

[stlpony]

Now it's not working again. and is showing #Name . Think there is something I'm still not understanding here.

Looks like it works consistently if I use this ... =VBAProject.G_Distance.G_DISTANCE(B1,C1)

 

[SARVESHEXCEL]

I have find easy idea for your requirement but not attached file option in your system.

PFA

 

[Axelle]

Hi,

I am also trying to calculate the distance between 2 places in excel.
I have tried the VBA code.
Could is also be the google API code?

I always get 0 or -1 (I tried 2 different codes)

[COLOR=rgba(0, 0, 0, 0.87)]


[/COLOR]​

 

[Axelle]

Because I receive this message:

“This API project is not authorized to use this API”​

 

[John_w]

Because I receive this message:

“This API project is not authorized to use this API”​

Using the Google Cloud Console, you have to enable the relevant Google API in your project.

 

[Axelle]

ok, indeed. I just checked again and now the API is ok I think, but the formula is still not working.
the errorhandle takes over every time.
Any suggestion?
Can it be the language? Or the way my data is formed?

Public Function GetDistance(start As String, dest As String)

Dim firstVal AsString, secondVal As String, lastVal As String

firstVal ="http://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal ="&destinations="
lastVal ="&mode=car&sensor=false&key=google API key "

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

 

[Axelle]

Hi,
Is it already working. I had to change http to https.

 

[SpillerBD]

Not sure. I was recently upgraded to 2016 at work and is currently not working either.
Could be a couple of things for sure....

 

[Macropod]

See: https://www.mrexcel.com/forum/excel...two-postal-codes-post5208605.html#post5208605