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.
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”)
lRow = 2
Whilst Not IsEmpty(.Cells(lRow, 6))
urlData =.Cells(lRow, six)
browser.Noticeable = Accurate
Although browser.ReadyState <> READYSTATE_Total
Debug.Print Now, “waiting around”
sHtml = browser.Doc.DocumentElement.innerhtml
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”
getDistance = “not discovered”
.Cells(lRow, eight) = getDistance
Debug.Print Now, getDistance
lRow = lRow + 1
Learn Much more!