Excel Calculate Miles from Zip Code to Zip Code

VballRef

New Member
Joined
Feb 12, 2018
Messages
6
I am aware that there are other similar questions like this on here, but they seem to be pieced together, and I cannot seem to find a single answer that can be easily deployed. I previously used G_Distance function to calculate miles between a matrix of zip codes, and it worked flawlessly. I now have obtained a Google API to be able to achieve the same result, but I have been unable to get it to work within my current Excel framework.

Basically, I'm looking for a formula (or macro) using a Google API that will allow the mileage to be calculated as shown below (do not need the actual "mi" suffix--just there to show what I'm calculating).

6182047408522422074248104
Name132220??mi??mi??mi??mi??mi
Name275501??mi??mi??mi??mi??mi
Name331316??mi??mi??mi??mi??mi

<tbody>
</tbody>

I know this is possible...and I'm weary of trying to figure it out! I'm hoping some of my Excel friends can help me!

Thanks!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

VballRef

New Member
Joined
Feb 12, 2018
Messages
6
I finally got the code to work!! Using the code below, I am able to get the result in meters...so the only thing I still need help with is converting to miles. Is there any easy way to build that conversion into this code? Or do I need a separate function for that?

Public Function GetDistance(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal = "+&destinations="
lastVal = "+&units=imperial&mode=car&language=en&sensor=false&key= YourGoogleAPIKey"
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
 

VballRef

New Member
Joined
Feb 12, 2018
Messages
6
Sorry for the duplication...I cannot seem to edit my post. I was clearly over-thinking this :). The conversion part was easy...no need to add to the code! Just used the simple formula: =(CONVERT(GetDistance(C$1,$B2),"m","mi"))
 

Watch MrExcel Video

Forum statistics

Threads
1,095,285
Messages
5,443,565
Members
405,238
Latest member
siddo

This Week's Hot Topics

Top