Geocoding is the process of converting addresses to the x and y coordinate or longitude and latitude. There are various option available in the market to perform geocoding, some are paid and some are free. You can use The Google Maps Geocoding API to retrieve the longitude and latitude for free. Later you can convert this longitude and latitude to your desire coordinate values, there are number of online tools for doing that. So it will be hassle to perform each addresses one at time in google, for that you can use Excel software to perform large number of addresses. You create excel function to perform the geocoding for multiple addresses.
For this task I am using Microsoft Excel 2013, you can use 2007 or 2010 version. First task is to turn on the Developer tab. To find this tab in 2007 or 2010 version you have to play around.
1. Go to the Microsoft File > Options > Customize Ribbon and check Developer
You will notice Developer tab is added on the ribbon.
2. Now click the Visual Basic button on the far left corner which will open the new window. Now we want a new module to insert which will store our new function. For that click on Insert > New Module
Copy the below code and paste into the empty area (code source: policeanalyst.com)
|
This code make use of Microsoft’s XML library, so you have to add references to it. To add the reference go to Tools > References
You will see list of references with the check box, select the Microsoft XML v6.0, after that click Ok button.
Finally save your Visual basic window by pressing save button. Then from the drop down menu of Save as type select Excel Add-In, assign the file name of your desire. Once the file in saved in “*.xlam” extension you can open any new document and use the function. Close all the open excel files and open the new one.
3. Open the new excel file and then go to file > Options > Add-Inns. From the Manage drop down box select the Excel-Add Ins.
After selecting then press the GO button, this will list all the saved Add-ins files, as I have saved as Book1. Xlam I will check that box. Then press Ok after selecting,
4. As everything is done to perform the geocode, we are going to use the function to retrieve the longitude and latitude value. Let us suppose I have address 65 Hawthorn way, Olds, Alberta, Canada then you type that value in excel and use the function MyGeocode (as you type my it will show function on the box).
It will take few seconds to run the function because it has to pull the information from the google server and then result will display.
Now if you have number of addresses and you want to run the geocode then this function will help you. Just keep in mind few things while using this function, after the execution process is done then you can copy and paste the values because each time you open the file then it will processing each cells which might take up few minutes to perform it.
Second things as it is free they will not allow you to do perform thousands of geocode at once, so best way is to have a 10 seconds break between addresses. If Google stop you for retrieving large number of addresses, you will see error saying “Not Found (try again, you may have done too many too fast)”. See below example, I have tried few number of addressee without following 10 seconds rule.
I hope you liked this tutorial, please comment if you have any question regarding this.
Kafui
22 Mar 2016Hello,
I am working on a final year project and after finding the longitudes and latitudes i want to get a function that can provide the distance between them by car from google maps to form a matrix.
Thanks
Stuart
24 Jun 2016Hi thanks for this. My excel sheets give me a compile error…User-defined type not defined. Have searched but cannot get it to work – any ideas?
admin
24 Jun 2016Well Thanks, I will see that.
Marco
18 Jul 2016Hi. Thanks for this, but I get the same error as Stuart. Do you already have a solution by now?
admin
18 Jul 2016I will see tonight and send you message, is this your email address mme……
admin
18 Jul 2016Just thought to ask you which Microsoft excel version you are using it, I wrote this for 2013 version but will check later to confirm for other version
kunal
21 Jul 2016My excel sheets give me a compile error…User-defined type not defined. Have searched but cannot get it to work – any ideas?
i am using excel 2016 version
SteveH
22 Jul 2016Keep getting an error that file is not a valid Add-In. Running Excel 2010. Any thoughts?
Eric
5 Aug 2016Getting the same error too;
googleResult As New MSXML2.DOMDocument (This line seems to be highlighted)
admin
16 Aug 2016Today I checked on Microsoft 2010 and the code is working, yes MSXML2.DOMDocument get highlighted if you forget to checked in Microsoft XML v6.0, please see above steps. Also do not forget to enable Macros security. If you guys have any problem on 2010 and 2013 versions let me know, becasue these two I have checked. Just make sure you follow all the steps it will work. Thank you for the patience and my apology for not responding on time.
Colin
30 Aug 2016I get the error on the ‘Dim googleResult As New MSXML2.DOMDocument’ line with Excel 2013 and I can confirm that I do have the Microsoft XML v6.0 library checked off.
Colin
31 Aug 2016Here is the solution for those in the future:
Dim googleResult As Object
Set googleResult = CreateObject(“MSXML2.DOMDocument.6.0”)
Dim googleService As Object
Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)
Also, for those concerned about the timing… create a macro with 1 second delay between each iteration of the formula. Also, Google restricts an IP address to just 2500 instances with their GeoCoding service within a 24 hour period. I’ve attached a sample below… .for my example I had my addresses listed in column H and my formula =MyGeocode was used in column I… just adjust this according to your worksheet. I also changed the CodeName of the worksheet to GeoCode.
Sub GeoCode()
Dim GeoCode As Worksheet
Set GeoCode = ThisWorkbook.Sheets(“GeoCode”)
Dim i As Integer
i = GeoCode.Cells(GeoCode.Rows.Count, 9).End(xlUp).Row
For i = i To (i + 2400)
GeoCode.Range(Cells(i, 9), Cells(i, 9)).Formula = “=MyGeoCode(H” & i & “)”
Application.Wait (Now + TimeValue(“0:00:01”))
GeoCode.Range(Cells(i, 9), Cells(i, 9)) = GeoCode.Range(Cells(i, 9), Cells(i, 9)).Value
Next
End Sub
admin
31 Aug 2016Thanks Colin
admin
25 Sep 2016I have wrote a simple tutorial to perform Geocoding with Excel and Bing Maps which can let you perform up-to 10000 addresses.
http://grindgis.com/software/microsoft-excel/geocoding-excel-and-bing
Tom McGee
9 Nov 2016If you’re copying and pasting this code, watch out for the smart quotes. You’ll need to replace them with the straight up-and-down type.
Cosmin
1 Sep 2016Thank you so much! it works perfect!!!
Jack
2 Sep 2016I am a novice with excel with limited knowledge. Will you send me the syntax of the actual formula you are typing in? Thank you in advance.
Charlie
2 Sep 2016Thanks for this–your code along with Colin’s notes worked perfectly.
I’ve been trying to figure out how to do something similar, except to get the Zip Code when given just the street, city and state. Can you show me how to do this?
Thanks.
Michael
7 Sep 2016Hello,
I am trying to use my own api key with this tool.
I tried replacing
strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?”
with
strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?key=YOUR_API_KEY”
and it does not work. Any ideas?
Jamie
3 Oct 2016I got the function to work with Exel 2016 with Colin’s adjustments. But if you recalculate the sheet and you have many lines of address you get the “Not Found” error probably because you have requested either too many in the day or too fast. So if an “if” statement could be added before the “googleservice get” to test the contents of the current cell and if “Not Found” then execute else skip then upon open/close/recalculate of the XLS or automatic or manual recalc not every address would then be reevaluated. Not a VBA guru. Anyone have the sample code to do that?
admin
8 Oct 2016You can use bing maps to perform geocoding, http://grindgis.com/software/microsoft-excel/geocoding-excel-and-bing
Howie
24 Oct 2016Thanks for the code – works great. However, I would like to overcome the time/daily max limits so I signed up for the Google billing. How do I go about using my “Key” for the API?
admin
25 Oct 2016Can anybody help Howie, Please? Let me know Howie if you did not receive answer.
admin
25 Oct 2016or use this tutorial, bing map api. http://grindgis.com/software/microsoft-excel/geocoding-excel-and-bing
Anam Riaz
6 Nov 2016Main Boulevard ,Lahore,Punjab #VALUE!
I’m working on my thesis and i need coordinates of about 3000 locations but the excel is showing this VALUE error. i’ve used the same address in the tutorial for a test run but the error is same for “65 Hawthorn way, Olds, Alberta, Canada” location also. Any solution to this problem?
admin
8 Nov 2016Please use this tutorial instead of google maps api, http://grindgis.com/software/microsoft-excel/geocoding-excel-and-bing
wowwoowow
19 Nov 2017=MyGeocode() will give you an error. Put your address cell reference inside parentheses:
=MyGeocode(A1) given cell A1 contains address.
martin
11 Nov 2016put this
Sheets(“GeoCode”).Cells(i, 9).Copy
Sheets(“GeoCode”).Cells(i, 9).PasteSpecial xlPasteValues
before the wait time in colins code to past the values over the forumla so next time you open excel it wont refresh what you already geomapped
admin
11 Nov 2016Thanks
nuvia
15 Mar 2017He agregado todo habilitado macros, agregado microsoft v6 y mi código no me marca error pero al ejecutar la función en la celda se cicla y ya no responde mi exel.
Heather
15 Nov 2016I am trying to perform reverse geocoding in Excel. Would you happen to have the code for that?
Nate
21 Nov 2016This was working great for me for months, I came back into work after having the weekend off and now I am getting a number of errors. Sometimes Excel will hang up and crash, then sometimes it will just list #VALUE! Not sure what happened. Tried removing and reinstalling the add-in with no luck. Any suggestions?
admin
22 Nov 2016I will try
RC
23 Nov 2016i want to use google map api and not bing. While using the code you shared, and after adding
Dim googleResult As Object
Set googleResult = CreateObject(“MSXML2.DOMDocument.6.0”)
Dim googleService As Object
Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)
it is still giving error of ‘syntax error’ under compile error.
Pls help.
Andres Gomez
24 Nov 2016¿Hello, is it possible to something like this to get the next data?: Address, sublocality, sublocality_level_1 and administrative_area_level_1 . I would provide latitude and longitude in excel. Thanks.
Pramod Nair
15 Dec 2016Thank you so much. It works perfectly!! In Excel 2013, I have used it, and earlier an error message popped up, which got rectified as I checked v6.0 Microsoft XML
admin
15 Dec 2016Thank you
Nate
20 Dec 2016Is there anyway to have the myGeoCode function automatically replace the formula in the cell with its value? I tried the macro, but I could not get it to work and I am completely lost with VBA.
Andres Gomez
1 Feb 2017How mucho would you charge me to write something that allows me to get the next data in excel:
neighborhood
locality
Sublocality
sublocality_level_1
administrative_area_level_1
I have latitude, longitude and address
Md Momtaz Uddin
15 Feb 2017Your website is very helpful to me.
I was running my Excel vba program and it downloaded zip code. But stopped as exceeded daily limit. So I’ve an API Key for Google geocoding API. Please e-mail me how I can use this API key to download about 20,000 zip code in 2-3 days. Thank you for your help.
admin
16 Feb 2017Ok Momtaz, I will send you an email. Thank you
Andreas
14 Sep 2017Hi,
we have a google for works client id. Could you sent us the vba code to geocode with using the api-key?
Thank you,
Andreas
Steve Gresham
20 Feb 2017Thanks, this works great. Is there a way to have results show latitude and longitude in separate columns? Thanks.
admin
23 Feb 2017I will try to see.. Thank you
Ben
3 Mar 2017Does this calculate straight line distance or driving distance? I’ve gone through a lot of these VBA’s and each has done straight line and I am looking for driving distance.
admin
6 Mar 2017I have to see, thanks for your comment
Barry
7 Mar 2017Seems to work well. Thanks!!!
MJ23
27 Mar 2017Thanks,
It work very well in English language. However, if my address is another language such as Thai language, could you please suggest me to find latitude and longitude from them. Unless I have to translate all data in English.
Google map also has Thai language.
Regards,
admin
3 Apr 2017Change the line strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?” to strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?&language=th”. Try this and let me know 🙂
Thimios
1 Jul 2017I tried to use this for Greek language since I have the same problem but does not work. Can you help?
Seel below
I add &language=el
‘Assemble the query string
strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?&language=el”
strQuery = strQuery & “address=” & strAddress
strQuery = strQuery & “&sensor=false”
I tried for the following address
Address :
Αθανάτων30,Aχαρναί
Result:
Not Found (try again, you may have done too many too fast)
I put the same code (URL) in chrome and is working fine
http://maps.googleapis.com/maps/api/geocode/xml?&language=el&address=Aθανάτων30, Aχαρναί &sensor=false
Any idea?
Yuval
3 Feb 2020@Thimios Where you able to solve it?
Nicolas Rojas
24 Apr 2017hi, how can i doit backwords, i have the latitude and longitude but need to get the address back.
admin
30 Apr 2017I will see that and will update you.
KStar
3 May 2017If you are getting a user defined type not defined error around this section of the code –> MSXML2.DOMDocument , try unchecking the Microsoft XML v6.0 reference and checking the Microsoft XML v3.0 one instead and so forth.
admin
4 May 2017Thanks Karen
Nate
24 May 2017This was working for me until yesterday. Now I am getting #Value! errors. I have logged in an out of my API account. Tried requesting a new API Key. I have tried the above XML related suggestions. Nothing is working. Does anyone have any suggestions?
HBIT
21 Jun 2017As I was trying to debug it, deleting and rewriting the codes, noticed that VB now takes these new integrated handles instead. Note the “60” at the end of each line below with reference to the MS.XML6.0. Just adding 60 at the end of these lines got it working.
Dim googleResult As New MSXML2.DOMDocument60
Dim googleService As New MSXML2.XMLHTTP60
Good luck everyone!
admin
21 Jun 2017Thank you 🙂
Dipti
28 Jun 2017Its awesome!! Thanks a ton..
admin
29 Jun 2017Thank you Dipti
Jaeho
6 Jul 2017Can we get the longitude and latitude separately?
Jaeho
6 Jul 2017Oh, I have success to get the longitude and latitude separately with amending the source and make different module for longitude and latitude.
admin
10 Jul 2017Hi Jaeho, Great Job, we will appreciate if you can add your solution on the comment so others know how to do it. Thank you once again
Phongnguyen
13 Aug 2017Thank you very much, your website and this post are really useful with me. I have a other project involve with geocoding and google API. I want to find all the nearest places like ATM, Banks, restaurants…with given my location or latitude, longitude.
Thanks for your time, and hope you reply my answer.
admin
17 Aug 2017It will much easier if you easy Arcgis software to perform analysis.
Hector
11 Sep 2017Hello,
I am running EXCEL 2016, followed intructions and I am getting
Dim googleResult As New MSXML2.DOMDocument type no defined
appreciate any help on this.
best regads, Hector
Hector
11 Sep 2017SOLVED! with
Dim googleResult As Object
Set googleResult = CreateObject(“MSXML2.DOMDocument.6.0”)
Dim googleService As Object
Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)
Thank you!
slradke
11 Sep 2017I too am using the GoogleMaps geocoding service to geocode over 1,000 addresses. Got the same message “Not Found (try again, you may have done too many too fast).” Set up a billable Google Maps Geocoding API account, but don’t know how to use it with the Excel routing. Same question as Andres Gomez, which was answered in an off line email. Anyone have a solution here?
RC
14 Sep 2017Hi. Works great. Thanks for the code. Saw a couple of posts:
1. How do I modify to separate Long and Lat into 2 fields?
2. If I have a Google API key, how can I add to the StrQuery statement?
Thanks in advance.
RC
Ernie
27 Oct 2017I am using Excel 2010. I made the corrections published by Colin 30 Aug 2016 for proper dimensioning of googleResult and googleService. Even when I extended this to oNodes and oNode. I continue to get #VALUE! error no matter the format of the input address. The remark is that “A value used in the formula is of the wrong data type”. What are your suggestions to correct this?
wowwoowow
19 Nov 2017insert your cell address between parentheses like this:
=MyGeocode(A1)
Olaolu
22 Nov 2017Hey, Guys, i got it to work by adding 60 to both “Dim googleResult As New MSXML2.DOMDocument” and ” Dim googleService As New MSXML2.XMLHTTP”. Now no matter what I do i keep getting the message Not Found (try again, you may have done too many too fast)
I am connected to the internet L.O.L. I cannot figure out the problem kindly advice me on how to resolve the issue
admin
26 Nov 2017Can anybody help this gentleman, please
Dexter
6 Dec 2017Hey! I believe I figured out how to use your Google API alongside this macro add on. Just adjust the macro as below. Essentially you are adding your API key to the end of all of the strings, so you want to add it to the last StrQuery.
‘Assemble the query string
strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?”
strQuery = strQuery & “address=” & strAddress
strQuery = strQuery & “&sensor=false” & “&key=YOUR_API_KEY”
‘define XML and HTTP components
Let me know if this works for you as well.
Dexter
6 Dec 2017Later I had some trouble with this, but removing the &key= before my Api key seemed to rectify that which makes me wonder if my api key is just being ignored.
Dexter
6 Dec 2017OK! Sorry Everyone. I didn’t have it working but now I do. See below for the changes you need to make in the add-on in order to make the API key work. What I was missing earlier is that the web address must be https://
Function MyGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strAddress = URLEncode(address)
‘Assemble the query string
strQuery = “https://maps.googleapis.com/maps/api/geocode/xml?”
strQuery = strQuery & “address=” & strAddress
strQuery = strQuery & “&sensor=false”
strQuery = strQuery & “&key=MY_API_KEY”
‘define XML and HTTP components
admin
12 Dec 2017Thank you 🙂
Tente
6 Jan 2018I have no errors on execution but i don’t get a result i just have =MyGeocode(L2) typed in my cell, what i’m missing ?
Thanks in advance
Deny Saputra
11 Jan 2018Hi, I got an error “in My excel sheets give me a compile error…User-defined type nodefined”. Could anyone get any ideas?
Fabio
19 Jan 2018It works great! Many Thanks! Is there a way to get also “Result quality” and “Returned address” in other columns? Example
“Address”,Latitude,Longitude,Quality,”Returned address”
“corso lodi 13, milano”,45.450693,9.205117,Rooftop,”Corso Lodi, 13, 20135 Milano MI, Italia”
admin
19 Jan 2018Thanks Fabio
Ernie Lippert
19 Jan 2018MyGeocode function wont compile because of MSXML2.DOMDocument and probably MSXML2.XMLHTTP. Will appreciate your advice.
Dendy
23 Jan 2018hi may i have the solution for this problem, because in xls 2007 the error and didnt works any one can help tx
admin
24 Jan 2018Help him please 🙂
Jeff C
8 Mar 2018for everybody having difficulty with this, I strongly suggest you take the admin’s suggestion and use the excel sheet recommended on: http://grindgis.com/software/microsoft-excel/geocoding-excel-and-bing
it uses Bing to get the geocodes because Bing doesn’t have a 1,000 records/day limit like Google and then it maps them with google maps. best of both worlds!. I downloaded it, created a Bing maps key in under 60 seconds and presto it worked perfectly!! thank you so much for this!!!
admin
8 Mar 2018Thank you Jeff 🙂
Sue B
26 Apr 2018Using the reverse coding to get an address from Lat / Long. Issue… I need to have it return an address with county.
How can I make the “Formatted_address” include county.
below is my code for GEOAddress
Function GEOAddress(dblLatitude As Double, dblLongitude) As String
Dim strJSON As String
Dim strAddress As String
Dim lngTemp As Long
Dim objXml As Object
Dim strURL As String
strURL = “http://maps.googleapis.com/maps/api/geocode/json?latlng=” & dblLatitude & “,” & dblLongitude & “&sensor=false”
Set objXml = CreateObject(“Microsoft.XMLHTTP”)
With objXml
.Open “GET”, strURL, False
.send
strJSON = .responseText
End With
Set objXml = Nothing
lngTemp = InStr(1, strJSON, “formatted_address”)
strAddress = Mid(strJSON, lngTemp + 22, InStr(lngTemp, strJSON, “””,”) – (lngTemp + 22))
GEOAddress = strAddress
End Function
Srikanta Sahoo
24 May 2018Please refer this link for RecerseGeo.
https://www.myengineeringworld.net/2018/03/reverse-geocoding-vba-google-api.html
Yoni Golden
16 Jul 2018I’m using Excel 2016. I have two questions:
1. How can I set the module to insert the coordinates in two separate cell? a latitude column and a longitude column?
Is it possible to use this module if my wordbook is dynamic? new addresses being added and removed.
regards
Yoni
Yoni Golden
26 Jul 2018I have another question:
can it recognize addresses in Hebrew?
and how can i set it to read an address from two cells (city: a1, address: b1)?
Juliano
1 Aug 2018I performed all the steps, at the time of geocoding the address in EXCEL appears: Compile Error. The user-defined type was not defined.
this is the command with the error: Dim googleResult As New MSXML2.DOMDocument.
the addresses are for Brazil, and I use WINDOWS 2016.
Jake Erlich
19 Sep 2018Every time I try using the =MyGeocode() function I am met with the “Not Found (try again, you may have done too many too fast)” error even though I am only trying to geolocate one address as a test. I am using Windows 2016 I am wondering if that is the issue.
I have followed all of the steps to the letter. Here is the Visual Basic script I used as it needed a fix included in the comments above:
“Function MyGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strAddress = URLEncode(address)
‘Assemble the query string
strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?”
strQuery = strQuery & “address=” & strAddress
strQuery = strQuery & “&sensor=false”
‘define XML and HTTP components
Dim googleResult As Object
Set googleResult = CreateObject(“MSXML2.DOMDocument.6.0”)
Dim googleService As Object
Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)
Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
‘create HTTP request to query URL – make sure to have
‘that last “False” there for synchronous operation
googleService.Open “GET”, strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)
Set oNodes = googleResult.getElementsByTagName(“geometry”)
If oNodes.Length = 1 Then
For Each oNode In oNodes
strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
MyGeocode = strLatitude & “,” & strLongitude
Next oNode
Else
MyGeocode = “Not Found (try again, you may have done too many too fast)”
End If
End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
Dim StringLen As Long: StringLen = Len(StringVal)
If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String
If SpaceAsPlus Then Space = “+” Else Space = “%20”
For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = “%0” & Hex(CharCode)
Case Else
result(i) = “%” & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, “”)
End If
End Function”
Anyone have any thoughts or suggestions? Thanks very much!
admin
20 Sep 2018Thank you for your comment, can anybody help Jake please?
admin
20 Sep 2018Jake I will try to check with 2016 version.
Jake Erlich
18 Oct 2018Any luck testing with excel 2016? I recently upgraded my machine and OS to Windows 10 and I also reinstalled Excel 2013 but am still encountering issues. I am only receiving the error “Not Found” in both excel 2013 and 2016. Have there been any updates to Googles API that could be affecting this? Any help is greatly appreciated. Thank you.
Rosa
15 Oct 2018Hi, thanks so much for the script!
I used it a lot in another computer, in April, and now I’m trying to use it in a different one, and I always get the message “Not Found …”
I’m using Office 2010, made a xlam, with the suggesting of Dexter (API key, httpS, ..) and with the 6.0 versions.
The xml 6.0, http, internet, everything seems active and working fine, macros are enabled.
Using a query string directly on browser, like “https://maps.googleapis.com/maps/api/geocode/xml?key=MY_KEY&address=1900-104Lisboa&sensor=false” gives a good response and result, but on excel, the same address (or other ones), always give me the error “Not Found (try again, you may have done too many too fast)”.
Any clues of what could be wrong?
Nate
24 Oct 2018I think Google updated their cloud services requiring you to have payment information on file so they can charge you if you use their service more that the free.
Dave Easey
16 Nov 2018In case it is useful for anyone, steps I went through to get this working on Google.
1. Google Cloud Account
Yes you need one now. You get $300 worth free. You have to give your credit card but they are very clear that you will never be charged without you authorising it. This is needed to get a personal ‘API key’. This goes into the code and is sent with every call.
2. Compile Error as others mentioned above.
The trick from Nate above fixed this for me:
Dim googleResult As New MSXML2.DOMDocument60
Dim googleService As New MSXML2.XMLHTTP60
3. API Key usage
The help from Dexter worked for me:
i) Use https://
ii) Use strQuery = strQuery & “&key=MY_API_KEY”
BUT be careful with Cut and Paste!
The quotation marks may not copy correctly so you need to re-type them. This showed as a compile error in VBA.
Also somehow I added a space before the = sign. Watch for that.
MY_API_KEY was replaced with the ~40chars key that Google assigned to me.
4) “Not Found (try again, you may have done too many too fast)”
After I’d fixed the above issues the function was working correctly sometimes but not others.
By stepping through the code I found that Google was returning something but still the error was occurring.
In the below code the If statement was returning the message not because oNodes.Length was zero but because it was more than 1.
If oNodes.Length = 1 Then
For Each oNode In oNodes
strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
MyGeocode = strLatitude & “,” & strLongitude
Next oNode
Else
MyGeocode = “Not Found (try again, you may have done too many too fast)”
End If
Changing to ‘ If oNodes.Length => 1 Then’ fixed this.
An interesting case was an address in Germany that worked fine with the ‘=1’ code if it had a street/City/PostCode/Country structure, but adding an extra address line between street and City caused it to only work with the ‘=> 1’ code.
Anyone know why?
A suggestion to Admin is to supply a development macro version of the code with the variables sent to a worksheet. That can be tried first and the sent/return variables evaluated.
Big thanks to Admin and all other contributors.
Regards
Dave
admin
18 Nov 2018Thank You Dave 🙂
Leonardo
30 Apr 2019Thanks Dave! It worked perfectly!
Just one question… Do you happen to know what is the daily limit of searches with the free trial?
stuck
11 Mar 2019This doesn’t give me any errors but I get a blank cell.
I had errors, I added the 60 to the end of those 2 lines which got rid of the errors but now it just doesn’t return anything.
I have tried changing the type of the column, but still no results
Any ideas?
ASFAND YAR KHAN RABBANI
7 Aug 2019Hi i have followed your steps and i am ok with it. But now the question is how to use the Function in excel cell. I am using it like =MyGeocode(“Address Cell”) but it gives error
M.R.
17 Dec 2019Does anyone use this on non-Western alphabets? If I try for Jordan or Morocco in local language can it give a result?
Yuval
3 Feb 2020I got everything working but i want to enforce English language.
when i’m adding & “language=en” it doesn’t work.
any idea?
Code:
Function ReverseGeocode(lat As String, lng As String) As String
Dim strAddress As String
Dim strLat As String
Dim strLng As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strLat = URLEncode(lat)
strLng = URLEncode(lng)
‘Assemble the query string
strQuery = gstrGeocodingURL
strQuery = strQuery & “latlng=” & strLat & “,” & strLng & “language=en”
If gintType = 0 Then ‘ free Google Geocoder
strQuery = strQuery & “&sensor=false”
ElseIf gintType = 1 Then ‘ Enterprise Geocoder
strQuery = strQuery & “&client=” & gstrClientID
strQuery = strQuery & “&signature=” & Base64_HMACSHA1(strQuery, gstrKey)
ElseIf gintType = 2 Then ‘ API Premium Plan
strQuery = strQuery & “&key=” & gstrKey
End If
‘define XML and HTTP components
Dim googleResult As Object
Set googleResult = CreateObject(“Msxml2.DOMDocument.6.0”)
Dim googleService As Object
Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)
Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
‘create HTTP request to query URL – make sure to have
googleService.Open “GET”, gstrGeocodingDomain & strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)
Set oNodes = googleResult.getElementsByTagName(“formatted_address”)
If oNodes.Length > 0 Then
ReverseGeocode = oNodes.Item(0).Text
Else
ReverseGeocode = “Not Found (try again, you may have done too many too fast)”
End If
End Function