Utilizing Excel, VBA and Google Maps to Find the Length Among Locations

My good friend needed to discover out the distances between cities in South Africa. He asked me to give him a hand, as I utilized to do a lot of info processing in my very first banking job. Entfernung Heide Pfungstadt was likely to use this to calculate transport fees, but the purpose I wrote could be used for any amount of programs.

There are several methods to skin a cat. This was an unpaid job, with no glory element, so I just did it in what I believed would be the quickest way attainable.

I knew Google Maps could determine distances and instructions. So I fired it up and looked for the distance in between two towns. I then looked at the resource HTML to function out how to extract the distance data.

Attempting diverse cities, it before long turned obvious that I required to specify the point out and country, as well as the town identify. As city names are not distinctive.

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

The function appears to perform most of the time. Occasionally it does not return something, but that is since Google Maps does not return a length, and that is simply because the area names are not specific sufficient.

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

The Code:

Purpose 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 = “length:”””

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

With Sheets(“Soya”)

lRow = 2

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

urlData =.Cells(lRow, 6)

browser.Navigate (urlData)
browser.Seen = True

Although browser.ReadyState <> READYSTATE_Complete
Debug.Print Now, “waiting”

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

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

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

getDistance = “Not Identified”
Conclude If
getDistance = “not identified”

End If

.Cells(lRow, eight) = getDistance

Debug.Print Now, getDistance

lRow = lRow + one

Stop With


Conclude Perform

Find out Much more!

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>