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!
 

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

Forum statistics

Threads
1,082,284
Messages
5,364,281
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top