Making use of Excel, VBA and Google Maps to Uncover often the Distance Amongst Areas
My good friend desired to uncover out the distances in between towns 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 occupation. My buddy was going to use this to determine transport fees, but the function I wrote could be used for any variety of applications.
There are many approaches to pores and skin a cat. This was an unpaid job, with no glory ingredient, so I just did it in what I believed would be the quickest way possible.
I realized Google Maps could calculate distances and instructions. So I fired it up and appeared for the length among two cities. I then appeared at the supply HTML to operate out how to extract the distance data.
Striving diverse cities, it soon turned evident that I necessary to specify the point out and nation, as nicely as the city name. As town names are not distinctive.
I then opened up Excel, created a sheet to estimate the query URLs, and then wrote a purpose to piece all the components jointly.
The perform seems to function most of the time. Occasionally it does not return everything, but that is simply because Google Maps does not return a distance, and that is due to the fact the spot names are not particular enough.
Use the getDistance perform at your very own threat. It may not perform for you, but I will not 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 = “distance:”””
Dim browser As InternetExplorer
Set browser = CreateObject(“InternetExplorer.Software”)
lRow = two
Whilst Not IsEmpty(.Cells(lRow, 6))
urlData =.Cells(lRow, 6)
browser.Obvious = Real
Even though browser.ReadyState <> READYSTATE_Comprehensive
Debug.Print Now, “waiting”
sHtml = browser.Document.DocumentElement.innerhtml
i0 = InStr(1, sHtml, “distance:”””)
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 discovered”
.Cells(lRow, 8) = getDistance
Debug.Print Now, getDistance
lRow = lRow + one
Understand Much more!