Excel Calculate Miles from Zip Code to Zip Code

[VballRef]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]61820[/TD]
[TD]47408[/TD]
[TD]52242[/TD]
[TD]20742[/TD]
[TD]48104[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]32220[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]75501[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]31316[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[TD]??mi[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[VballRef]

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]

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