Using Excel VBA and Google Maps to Find the Distances Between Places

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.

Preparation

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.

Method

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.

Results

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.

Disclaimer

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.Navigate (urlData)
browser.Visible = True
While browser.ReadyState <> READYSTATE_COMPLETE
Debug.Print Now, "waiting"
DoEvents
Wend
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"
End If
Else getDistance = "not found"
End If

.Cells(lRow, 8) = getDistance
Debug.Print Now, getDistance
lRow = lRow + 1
Wend
End
With browser.Quit
End Function

Learn More!

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.