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

		61820	47408	52242	20742	48104
Name1	32220	??mi	??mi	??mi	??mi	??mi
Name2	75501	??mi	??mi	??mi	??mi	??mi
Name3	31316	??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!

 

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