Heres a little example of some VBA functions I put together to get geocodes into excel using the google maps API.
It’s not the best geocoder in the world, but it does ok in the test I’ve done so far, although it does just come up with blanks for some locations. To use it you will need a Google maps API key which you need to store in a text file on the same path as the workbook, details are on the first sheet of the workbook.
As for the code itself I had to automate IE rather than use an API or the HTTP or XMLHTTP libraries, as my corporate firewall would not allow access using these. The function only asks for 1 string, so you have to add spaces yourself, this might be done better, but it suited my needs (that is to say, I didn’t bother writing in optional arguments etc.)
It’s worth while looking at the code as I have done a few things that might not be considered “best practice”, mainly not closing the IE application when the function has run - this is to speed up the function - there is a public function that you can use to close it from a worksheet if you wish (see code as it’s not explained in the “documentation”)
Theres probably some other stuff that I should mention, but I did this a week or so ago and have not been able to post it as my internet connection was down, so I have forgotten that!
Feel free to post any questions and I’ll feel free not to answer them!!!!
Enjoy
Ross
Hey Cool, Ross!
We’ll need to teach Google about Canada though… I’m on the left cost, on the big island, right about even with the US border. :)
Thanks Ken,
Your location is mostly down to me putting it in wrong I think!!!! I didn’t know you were an island dweller though!
Really nice Ross. Any guidance on how I could push this further to report driving distances between two locations? I’ve got the basic “http://maps.google.com/maps?saddr={startlocation}&daddr={endlocation}” , but would really value how I could capture the results back from Google.
I was wondering the same thing concerning driving distances. Anyone got this to work - you yet JS?
Thanks!
Hi Chaps,
I will take a look at this when i get a little bit of free time, mean while if you are really stuck you can try using great circles to get the stright line distance see : http://mathworld.wolfram.com/GreatCircle.html
then sum the result by about 1.2 - 1.4 to get the road distance, this is gives ok results depending on your needs.
I did another loop on this and have a way of getting the distance. Basically within the (18 page) output from appIE.Document.Body.innerHTML are journey distance and journey time. So I have simply added a text search of the output and then sliced out the data required.
Very ugly, but seems reasonably reliable: I hope there is a more elegant way. One comment - for Europe at least - is that Google Earth has much poorer journey offer than Microsoft. For instance Google cannot do anything in Russia, Balkans or China (I know this is not Europe) where Microsoft Maps Live does.
Listing
Sub Router()
‘function(sStart As String, sEnd As String) as string
‘//Dont want to open and close all day long - make once use many
Dim sLocation As String
Dim sStart As String
Dim sEnd As String
Dim Answer As String
sStart = “Warsawa+Poland”
sEnd = “Northampton+England”
If appIE Is Nothing Then
CreateIEApp
‘// if = nothing now then there was an error!
If appIE Is Nothing Then
Answer = “Sorry could not launch IE”
Exit Sub
Else
‘//do nothing
End If
Else
‘//do nothing!
End If
If GMAPI_Key = “” Then
‘//Get Google API key
GMAPI_Key = GetGMAPIKey
End If
‘// check we got API key OK
If GMAPI_Key = CST_NOGMAPI_KEY Then
Answer = CST_NOGMAPI_KEY
Exit Sub
Else
‘//do nothing
End If
‘//Build URL for Query
sLocation = “http://maps.google.com/maps?saddr={” & sStart & “}&daddr={” & sEnd & “}”
sLocation = sLocation & “&output=csv&key=%20″
sLocation = sLocation & GMAPI_Key
‘Debug.Print sLocation
‘// go to the google web service and get the raw CSV data!
appIE.Navigate sLocation
Do While appIE.Busy
Application.StatusBar = “Contacting Google Maps API…”
Loop
Application.StatusBar = False
On Error GoTo depart
‘//we have to do a bit of prasing, luckily the formate is constant
Dim ansMiles As Single
Dim ansUom As String
Dim ansHrs As Integer
Dim ansMin As Integer
Answer = appIE.Document.Body.innerHTML
Answer = Mid(Answer, InStr(Answer, “class=pw>”) + 9, 50)
If Len(Answer)
sorry missed the key bit from listing….
Answer = appIE.Document.Body.innerHTML
Answer = Mid(Answer, InStr(Answer, “class=pw>”) + 9, 50)
If Len(Answer)
How strange, Less than symbol in listing kills the rest of the listing…
If Len(Answer) [less than] then
Debug.Print “no journey result”
Else
Debug.Print Answer
ansMiles = CSng(Left(Answer, InStr(Answer, “&”) - 1))
ansUom = Mid(Answer, InStr(1, Answer, “;”) + 1, 2)
ansHrs = CInt(Mid(Answer, InStr(1, Answer, “about “) + 6, 2))
ansMin = CInt(Mid(Answer, InStr(1, Answer, “hours “) + 6, 2))
MsgBox sStart & ” to ” & sEnd & vbCrLf _
& ansMiles & ” ” & ansUom & ” : ” & ansHrs + (ansMin * (100 / 60) * 0.01) & ” Hrs”
End If
Exit Sub
depart:
MsgBox “Journey did not calculate”
‘Answer = CStr(CloseIEApp)
End Sub
JS - thanks for the reply. I keep getting Journey Did Not Calculate though. Any ideas?
Larry.Weideman (at) gmail.com
Larry
Have you followed all of Ross’s original instructions about getting a Google API key?
I have used Ross’s worksheet and added Router as a new procedure. If you are getting a good result on his original geo-coding procedure, I cannot see why it would not calc.
Maybe comment out the ‘On Error GoTo depart’ line and see where the error is coming up?
JS
Yes I did and yes his Geocode sheet works great.
I also added this as a new procedure (and tried it as a seperate worksheet) and it fails at ansMiles = CSng(Left(Answer, InStr(Answer, “&”) - 1)) that line every time. I am not sure why - I’m newer to coding.
Thanks for the reply - larry.weideman (at) gmail.com
OK thats should be straightforward to fix. the Answer variable is just a string extracted from the google return data. The code assumes in there is a “&” and then extracts a sub-string which should be some numbers showing miles (I said it was crude!). If you put a debug.print answer in the previous line and put a break on this you can stop and check what the answer string is you are working on, maybe you can then tune the formula. My guess is that the CSng (conversion of the text to a number) is the cause of failure; the string extracted has, say, space in it. Hope that helps
JS - thanks again for the help to this newbie. I think I must have copied something wrong (thought I’ve done it a few times.) Any way to get this in a text file.
When I tried your suggestion this is what I get in the Immediate Window for the debug Answer.
d=homestate>
Oh! I guess this may be a difference in the output from Google for different geographies. Suggest you might put a “debug.print answer” after line with “Answer = Mid(Answer, InStr(Answer, “class=pw>”) + 9, 50)”. The string SHOULD (it does for me) contain data that aligns with (a) a mileage (b) a journey location. (that is the target answer, but to report it properly you will need to play with the subsequent text slicing formulas to get each element out properly. My guess is that the change in what you are finding is due to different formats between Europe output and North America.
Otherwise examine the previous (raw) output from Google.
I think there must be a more elegant solution than mine, I am hoping Ross comes up with that!
Hello everybody,
I put it in as a new function named ‘router’ in the original sheet.
Wat is the statement I have to put in a cell of my excel sheet to get the result.
ex. for goecode it is =geocode(C51&” “&D51&” “&E51&” “&F51)
thanks,
Steve
Steve
Change router to a function by changing code like this:
Sub Router() - change to Router(sStart as string, sEnd as string) as string
‘function(sStart As String, sEnd As String) as string -delete this line
‘//Dont want to open and close all day long - make once use many
Dim sLocation As String
Dim sStart As String - delete this line
Dim sEnd As String - delete this line
Dim Answer As String
sStart = “Warsawa+Poland” -delete this line
sEnd = “Northampton+England” -delete this line…
find this line…
MsgBox sStart & ” to ” & sEnd & vbCrLf _
& ansMiles & ” ” & ansUom & ” : ” & ansHrs + (ansMin * (100 / 60) * 0.01) & ” Hrs”
replace with
Router= sStart & ” to ” & sEnd & vbCrLf _
& ansMiles & ” ” & ansUom & ” : ” & ansHrs + (ansMin * (100 / 60) * 0.01) & ” Hrs”
so the function entry is =router(YourStartLocation, YourEndLocation) and the result is a string in the cell listing the distance etc.