Introduction
My buddy needed to discover out the distances among towns in South Africa. He requested me to give him a hand, as I utilized to do a good deal of information processing in my 1st banking work. My buddy was likely to use this to compute transport expenses, but the function I wrote could be utilized for any variety of purposes.

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

Entfernung Dresden Leipzig understood Google Maps could determine distances and directions. So I fired it up and appeared for the distance in between two towns. I then looked at the source HTML to work out how to extract the length knowledge.

Attempting different towns, it soon grew to become evident that I necessary to specify the state and place, as nicely as the town identify. As town names are not unique.

I then opened up Excel, made a sheet to calculate the query URLs, and then wrote a purpose to piece all the elements together.

Outcomes
The perform would seem to operate most of the time. At times it does not return anything, but that is because Google Maps does not return a length, and that is due to the fact the place names are not specific enough.

Disclaimer
Use the getDistance operate at your very own risk. It may well not work 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 Extended

Dim searchStart As String

searchStart = “length:”””

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

With Sheets(“Soya”)

lRow = two

Even though Not IsEmpty(.Cells(lRow, six))

urlData =.Cells(lRow, 6)

browser.Navigate (urlData)
browser.Seen = Real

While browser.ReadyState <> READYSTATE_Full
Debug.Print Now, “waiting”
DoEvents
Wend

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

i0 = InStr(one, 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”
Conclude If
Else
getDistance = “not discovered”

End If

.Cells(lRow, eight) = getDistance

Debug.Print Now, getDistance

lRow = lRow + 1

Wend
Conclude With

browser.Stop

End Perform

Learn More!