Project: Find the Distances Between Places with Excel VBA and Google Maps
My friend wanted to find out the distances between towns in South Africa. He asked me to give him a hand, as I used to do a lot of data processing in my first banking job. My buddy was going to use this to calculate transport costs, but the function I wrote using Excel VBA and Google Maps could be used for any number of applications.
There are many ways to skin a cat. This was an unpaid job, with no glory component, so I just did it in what I thought would be the quickest way possible.
I knew Google Maps could calculate distances and directions. So I fired it up and looked for the distance between two towns. I then looked at the source HTML to work out how to extract the distance data. Trying different towns, it soon became apparent that I needed to specify the state and country, as well as the town name. As town names are not unique. I then opened up Excel, made a sheet to calculate the query URLs, and then wrote a function using Excel VBA and Google Maps to piece all the parts together.
The function seems to work most of the time. Sometimes it does not return anything, but that is because Google Maps does not return a distance, and that is because the place names are not specific enough.
Use the getDistance function at your own risk. It might not work for you, but I won’t be supporting it. The Code:
Function getDistance(urlData As String)
Dim sHtml As String
Dim iStart As Integer
Dim iEnd As Integer
Dim lRow As Long
Dim searchStart As String
searchStart = "distance:"""
Dim browser As InternetExplorer
Set browser = CreateObject(“InternetExplorer.Application”) With Sheets(“Soya”)
lRow = 2
While Not IsEmpty(.Cells(lRow, 6))
urlData =.Cells(lRow, 6)
browser.Visible = True
While browser.ReadyState <> READYSTATE_COMPLETE
Debug.Print Now, "waiting"
sHtml = browser.Document.DocumentElement.innerhtml
Debug.Print sHtml i0 = InStr(1, sHtml, "distance:""")
If i0 > 0 Then i1 = InStr(i0 + Len(searchStart), sHtml, """")
If i1 > 0 Then getDistance = Mid(sHtml, i0 + Len(searchStart), i1 − i0 − Len(searchStart))
Else getDistance = "Not Found"
Else getDistance = "not found"
.Cells(lRow, 8) = getDistance
Debug.Print Now, getDistance
lRow = lRow + 1