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.
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.
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.
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.
Use the getDistance purpose at your possess chance. It may possibly not perform for you, but I will not likely be supporting it.
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”)
lRow = two
Even though Not IsEmpty(.Cells(lRow, six))
urlData =.Cells(lRow, 6)
browser.Seen = Correct
Even though browser.ReadyState <> READYSTATE_Comprehensive
Debug.Print Now, “waiting”
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 Found”
getDistance = “not identified”
.Cells(lRow, 8) = getDistance
Debug.Print Now, getDistance
lRow = lRow + one
Discover Far more!