Utilizing Excel, VBA and Google Maps to Uncover often the Distance In between Areas

routenplaner kostenlos wished to uncover out the distances in between towns in South Africa. He questioned me to give him a hand, as I utilised to do a good deal of information processing in my 1st banking work. My buddy was going to use this to determine transport fees, but the function I wrote could be utilised for any number of apps.

Planning
There are numerous methods to pores and skin a cat. This was an unpaid job, with no glory ingredient, so I just did it in what I thought would be the quickest way attainable.

Approach
I knew Google Maps could estimate distances and directions. So I fired it up and looked for the distance amongst two cities. I then appeared at the resource HTML to work out how to extract the distance knowledge.

Making an attempt various towns, it quickly became evident that I needed to specify the condition and nation, as nicely as the town identify. As town names are not exclusive.

I then opened up Excel, made a sheet to compute the question URLs, and then wrote a purpose to piece all the parts with each other.

Benefits
The purpose looks to perform most of the time. Often it does not return something, but that is simply because Google Maps does not return a length, and that is since the place names are not distinct enough.

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

The Code:

Operate getDistance(urlData As String)

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

Dim searchStart As String

searchStart = “distance:”””

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

With Sheets(“Soya”)

lRow = 2

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

urlData =.Cells(lRow, six)

browser.Navigate (urlData)
browser.Visible = True

Although browser.ReadyState <> READYSTATE_Comprehensive
Debug.Print Now, “ready”
DoEvents
Wend

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

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

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 Discovered”
Conclude If
Else
getDistance = “not located”

Conclude If

.Cells(lRow, 8) = getDistance

Debug.Print Now, getDistance

lRow = lRow + one

Wend
Stop With

browser.Give up

Stop Perform

Find out Far more!