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.
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.
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.
Use the getDistance operate at your very own risk. It may well not work for you, but I will not be supporting it.
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”)
lRow = two
Even though Not IsEmpty(.Cells(lRow, six))
urlData =.Cells(lRow, 6)
browser.Seen = Real
While browser.ReadyState <> READYSTATE_Full
Debug.Print Now, “waiting”
sHtml = browser.Document.DocumentElement.innerhtml
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))
getDistance = “Not Found”
getDistance = “not discovered”
.Cells(lRow, eight) = getDistance
Debug.Print Now, getDistance
lRow = lRow + 1