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

Introduction
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 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 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!

Visit the Microsoft Office Section of Cheapskatemate.com to read more about interesting things that you can do with Microsoft Office.