Making use of Excel, VBA and Google Maps to Uncover often the Distance Amongst Areas

Introduction
My good friend desired to uncover out the distances in between towns in South Africa. He questioned me to give him a hand, as I utilized to do a whole lot of information processing in my 1st banking occupation. My buddy was going to use this to determine transport fees, but the function I wrote could be used for any variety of applications.

Entfernungsrechner
There are many approaches to pores and skin a cat. This was an unpaid job, with no glory ingredient, so I just did it in what I believed would be the quickest way possible.

Technique
I realized Google Maps could calculate distances and instructions. So I fired it up and appeared for the length among two cities. I then appeared at the supply HTML to operate out how to extract the distance data.

Striving diverse cities, it soon turned evident that I necessary to specify the point out and nation, as nicely as the city name. As town names are not distinctive.

I then opened up Excel, created a sheet to estimate the query URLs, and then wrote a purpose to piece all the components jointly.

Outcomes
The perform seems to function most of the time. Occasionally it does not return everything, but that is simply because Google Maps does not return a distance, and that is due to the fact the spot names are not particular enough.

Disclaimer
Use the getDistance perform at your very own threat. It may not perform for you, but I will not be supporting it.

The Code:

Perform getDistance(urlData As String)

Dim sHtml As String
Dim iStart As Integer
Dim iEnd As Integer
Dim lRow As Lengthy

Dim searchStart As String

searchStart = “distance:”””

Dim browser As InternetExplorer
Set browser = CreateObject(“InternetExplorer.Software”)

With Sheets(“Soya”)

lRow = two

Whilst Not IsEmpty(.Cells(lRow, 6))

urlData =.Cells(lRow, 6)

browser.Navigate (urlData)
browser.Obvious = Real

Even though browser.ReadyState <> READYSTATE_Comprehensive
Debug.Print Now, “waiting”
DoEvents
Wend

sHtml = browser.Document.DocumentElement.innerhtml
Debug.Print sHtml

i0 = InStr(1, sHtml, “distance:”””)

If i0 > Then
i1 = InStr(i0 + Len(searchStart), sHtml, “”””)
If i1 > Then
getDistance = Mid(sHtml, i0 + Len(searchStart), i1 – i0 – Len(searchStart))

Else
getDistance = “Not Found”
Finish If
Else
getDistance = “not discovered”

Stop If

.Cells(lRow, 8) = getDistance

Debug.Print Now, getDistance

lRow = lRow + one

Wend
Finish With

browser.Quit

Conclude Function

Understand Much more!