Making use of Excel, VBA and Google Maps to Discover often the Distance Among Places

My buddy wanted to uncover out the distances in between cities in South Africa. distance calculator questioned me to give him a hand, as I utilised to do a lot of info processing in my 1st banking work. My buddy was likely to use this to estimate transport costs, but the operate I wrote could be employed for any variety of programs.

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

I knew Google Maps could estimate distances and instructions. So I fired it up and appeared for the distance among two towns. I then appeared at the source HTML to work out how to extract the length information.

Striving distinct cities, it before long turned obvious that I required to specify the state and place, as properly as the city title. As city names are not unique.

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

The purpose would seem to perform most of the time. Occasionally it does not return anything, but that is due to the fact Google Maps does not return a distance, and that is simply because the place names are not distinct adequate.

Use the getDistance perform at your possess threat. It may well not operate for you, but I won’t 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 = “length:”””

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

With Sheets(“Soya”)

lRow = 2

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

urlData =.Cells(lRow, six)

browser.Navigate (urlData)
browser.Noticeable = Accurate

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

sHtml = browser.Doc.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))

getDistance = “Not Identified”
Finish If
getDistance = “not discovered”

Stop If

.Cells(lRow, eight) = getDistance

Debug.Print Now, getDistance

lRow = lRow + 1

Finish With


End Function

Learn Much more!


Leave a Reply