Introduction
My buddy needed to locate out the distances between cities in South Africa. He questioned me to give him a hand, as I utilized to do a whole lot of information processing in my 1st banking work. My buddy was going to use this to compute transport charges, but the function I wrote could be utilized for any number of applications.

Preparing
There are several approaches to pores and skin a cat. This was an unpaid occupation, with no glory part, so I just did it in what I believed would be the fastest way feasible.

Method
I understood Google Maps could calculate distances and instructions. So I fired it up and seemed for the length between two cities. I then looked at the source HTML to operate out how to extract the length data.

Attempting Entfernung Haar Bogenhausen , it shortly turned evident that I necessary to specify the state and place, as properly as the city name. As town names are not unique.

I then opened up Excel, created a sheet to estimate the query URLs, and then wrote a function to piece all the areas collectively.

Final results
The function appears to perform most of the time. Sometimes it does not return something, but that is due to the fact Google Maps does not return a length, and that is because the location names are not certain enough.

Disclaimer
Use the getDistance purpose at your possess chance. It may possibly 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 Lengthy

Dim searchStart As String

searchStart = “distance:”””

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

With Sheets(“Soya”)

lRow = two

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

urlData =.Cells(lRow, 6)

browser.Navigate (urlData)
browser.Seen = Correct

Even though browser.ReadyState <> READYSTATE_Comprehensive
Debug.Print Now, “waiting”
DoEvents
Wend

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))

Else
getDistance = “Not Found”
Stop If
Else
getDistance = “not identified”

End If

.Cells(lRow, 8) = getDistance

Debug.Print Now, getDistance

lRow = lRow + one

Wend
Conclude With

browser.Give up

Stop Function

Discover Far 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>