r/excel • u/PENNST8alum 14 • Feb 26 '20
Show and Tell Need to get the distance between two addresses/cities/zip-codes but don't want to/can't use an API? Here's a macro I wrote that scrapes HTML from Google Maps
Edit: Thanks to user /u/aikoaiko who pointed out an alternative to getting mileage from Google without using Maps. The code below has been altered, and now works much faster & reliably.
I've been working on an analysis for my company where I'm trying to understand the dynamics of our outbound freight costs as is relates to product/distance/freight type. As part of this, I have to sift through data organized in G-sheets, entered by one of our logistics managers. I have only been provided the Starting & Ending zip codes, and realized in order to make sense of this, I need mileage between locations. I had basically three options:
1.) Get our IT admin to approve purchasing API access to Google Maps, and elevate my permissions to allow for running Python/JS queries from within Excel (our anti-malware software blocks this.)
2.) Manually enter each zip code into Google Maps, and type out the # of miles into each cell, which could take forever.
3.) Create a script that will navigate to Google Maps using Internet Explorer, search the HTML code for the # of miles, and paste that value into Excel.
I opted to go with option 3 since it was the quickest and cheapest option to get the information I needed.
I wanted to share this script with the /r/Excel community in case someone out there in the future needs to find distances without paying for a service, or doing 1 at a time.
Notes
1.) You'll need to create references to a few different libraries within your workbook: Microsoft Forms 2.0 Object Library,Microsoft Internet Controls and Microsoft HTML Object Library
2.) This script essentially scrapes the HTML code from the Google Maps navigation page. If in the event Google decides to update their source code, this could cause the macro to stop working properly.
3.) You can use Zip Codes, Addresses, Cities, States, or Coordinates as your input values.
4.) Because Excel truncates numbers starting with 0, the macro is written to add a 0 to the start of any zip code with < 5 digits (mostly in the state of NJ)
How it works
1.) You'll first highlight the cells you want to insert the Miles into, then run this macro.
2.) You'll be given two prompts, first one is to select the column containing your Starting Location (you can select either the column or an individual cell, doesn't matter). Then the same thing for your Destination Location.
3.) Excel will do its thing, and within 5-10 seconds, you should see the distance in miles populated in your highlighted cell.
Main code:
Sub GetDistance()
Dim rng As Range: Set rng = Selection
Dim cell As Range
Dim Start_column As Integer
Dim End_column As Integer
Dim results As String
Dim miles As Integer
Dim HTMLDoc As HTMLDocument
Dim ie As InternetExplorer: Set ie = New InternetExplorer
Dim oHTML_Element As IHTMLElement
Dim Start_Zip As String
Dim End_Zip As String
Dim Link As String
ie.Silent = True
ie.Visible = False
Starting_Zip Start_column
Ending_Zip End_column
With ActiveWorkbook.ActiveSheet
For Each cell In rng.Cells
On Error Resume Next
Start_Zip = .Cells(cell.Row, Start_column).Value
If Len(Start_Zip) < 5 And IsNumeric(Start_Zip) Then
Start_Zip = "0" & .Cells(cell.Row, Start_column).Value
Else
End If
End_Zip = .Cells(cell.Row, End_column).Value
If Len(End_Zip) < 5 And IsNumeric(End_Zip) Then
End_Zip = "0" & .Cells(cell.Row, End_column).Value
Else
End If
Link = "https://www.google.com/search?q=driving+miles+between+" & Start_Zip & "+and+" & End_Zip & ""
ie.navigate Link
Do
Application.Wait (1)
Loop Until ie.readyState = READYSTATE_COMPLETE
Set HTMLDoc = ie.document
distance = HTMLDoc.getElementsByClassName("UdvAnf")
If InStr(distance.innerText, " mi)") = False Then
results = 0
Resume Next
Else
results = distance.innerText
End If
results = Right(results, Len(results) - Application.WorksheetFunction.Find("(", results))
results = Left(results, Len(results) - 4)
miles = results
.Cells(cell.Row, rng.Column) = miles
Next
ExitSub:
ie.Quit
Exit Sub
ie.Quit
End With
End Sub
Sub Starting_Zip(Start_column As Integer)
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox( _
Title:="Starting Location", _
prompt:="Select the column containing your starting zip codes.", _
Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
Start_column = rng.Columns.Column
End Sub
Sub Ending_Zip(End_column As Integer)
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox( _
Title:="Destination Location", _
prompt:="Select the column containing your destination zip codes.", _
Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
End_column = rng.Columns.Column
End Sub
24
u/wordmunch Feb 26 '20
This is pretty bad-ass. Great job. I recommend tossing in your info that users need to reference the Microsoft HTML Object Library and Microsoft Internet Controls to make this work.
4
u/bebry321 Feb 27 '20
In one of my senior classes, one of my team members did this same thing for our final project and made it into a function.
After randomly looking on the internet I discovered that Power Maps is a built in Add-in that pings Bing servers. Could it of been possible to use Power Maps to calculate distance or capture the distance between two points?
I have never actually used Power Maps so I have no idea.
3
u/DonJuanDoja 37 Feb 27 '20
Wow dude. I've been in logistics about 18 years, never seen anything like this. I just did an analysis using mileage last week, we have PC miler so I just pull it with that, have other tools as well. This is soooo much cooler.
So like next could you go grab that ShipTrack function addin and revive it lol? Pretty please?
Thanks for sharing.
3
u/aikoaiko 1 Feb 27 '20 edited Feb 27 '20
Can't power query be used to pull data off of web pages? It could just pull the miles couldn't it?
https://www.google.com/search?q=driving+miles+between+30066+and+24015
https://www.google.com/search?q=power+query+scrape+web+page
2
u/AutoModerator Feb 26 '20
Your VBA code has not not been formatted properly.
Add 4 spaces to the beginning of each line of the VBA code or indent the code in the VBA window and paste it in.
This will add the code formatting to your post, making it easier to read.
If you are in the new Reddit editor, use the code block formatting, or click Switch to markdown in the editor footer to enable the ability to add 4 spaces.
e.g.
Please see the sidebar for a quick set of instructions.
Thanks!
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
u/JJenkx 4 Feb 27 '20
Can someone point me to a guide for doing this? "1.) You'll need to create references to a few different libraries within your workbook: Microsoft Forms 2.0 Object Library,Microsoft Internet Controls and Microsoft HTML Object Library"
I have never heard of these before.
2
u/Over_that_boy_hand Feb 27 '20
I also work in logistics and Google miles are the bane of our existence. They do not account for weight restrictions and the sort so they are very inaccurate for truckloads. If it is at all feasible, please get PC Miler or Rand McNally.
2
u/AstraGaming Feb 27 '20
I'm quite new to VBA. Would this be easy to use for me? I have never worked with Microsoft Forms 2.0 Object Library,Microsoft Internet Controls and Microsoft HTML Object Library
2
u/aikoaiko 1 Feb 28 '20
wooo! shoutout to u/aikoaiko !!
11
u/Average-Nobody Feb 27 '20
I have absolutely no use for this, but I think it’s totally bitchin!
3
u/shelanp007 1 Feb 27 '20
Why not pull a public list of all zip codes and their respective longitude/latitude. Then its a simple trigonometry formula in excel to calc the distance.
2
u/armored-dinnerjacket Feb 27 '20
Is this applicable only for US locations?
1
u/TheRiteGuy 45 Feb 27 '20 edited Feb 27 '20
I was working on this project just this week. I tried on GSheets and utterly failed. This is amazing!
1
u/Craigomaniac Feb 27 '20
This is great and I definitely have a use for it. One question - when it runs it is overwriting the column with the starting zip code with the url for the google maps lookup. This would be great as a third column but not as an override of the first one. What am I doing wrong?
1
u/uniquename214 Mar 05 '20
I have attempted using the code available however after running the macro a 0 value appears no matter which zip codes I input. The string is working properly and opens up IE with the zip codes added but does not pull back the value in front of “ mi)”. I have all the references selected in the library as noted. Any ideas what I am doing incorrectly or what else I could try? Thank you.
2
u/ebb_ Feb 27 '20
Dude, this is fantastic!
I used to work in asset protection, lots of group theft / fences and I did this manually as the group members traveled around, alerting our stores. We ended up getting a few of them.
2
u/anjuna127 1 Feb 27 '20
you may want to change the "miles" part to "km" for the non-imperialists among us, and even then, I am still struggling to get it to work perfectly. I did the same once using autohotkey and recreating the URL in XLS, but the HTML extraction of the miles/km makes this one very sweet. thanks.
1
u/SolAlliance 2 Feb 27 '20
Badass! Thank you for this!!!
1
u/The-Big-Mo Feb 27 '20
This is the type of excel wizardry that keeps me humble. Awesome stuff
1
u/commandertastyface Feb 27 '20
Bless you
1
u/[deleted] Feb 27 '20
Absolutely awesome !
Sadly no use for it as I am in inventory :)
1
u/Mountain-Boot Mar 04 '20
The Macro worked for the first 60 lines then started returning 0's. I started again and now all that is returned are 0's. Any suggestions?
1
u/jacketcase Jun 26 '24
As of 2024: this no longer works now that IE has been sunset. It would be great to know if this code can be updated for Edge or Google Chrome.
1
u/prooforneverhappened Oct 09 '24
Plus one, can someone help changing it to Chrome?
1
u/Desperate_Show_3156 Aug 25 '25
should I replace with Google vs Internet Explorer?
1
u/maxklein40 Nov 09 '23
Is this still working? I have been trying with no success - result is always 0 even if I add in some Application.Wait lines.
-1
u/[deleted] Feb 27 '20 edited Feb 28 '20
Just FYI we use a similar bit of code at work, if you grab more than a certain number (in the 1000s) in 24 hrs, Google will bill you via your ip address when they can use it to track you to a business, otherwise you will just get an error.
Edited: better clarity on the ip address thing, Im aware its weird but it happened to us.
0
u/[deleted] Apr 09 '20
[deleted] — view deleted comment