Auto Populate Latitude and Longitude Function in Excel (NEW VERSION)
Vložit
- čas přidán 22. 07. 2024
- How to write a custom function that converts a physical address to latitude and longitude coordinates in Excel using Google's Geocoding API.
00:00 - Introduction
00:16 - Overview of Custom Function in Excel
00:47 - Accessing Developer Ribbon in Excel
01:09 - Opening Visual Basic for Applications
01:31 - Finding and Copying the Code
02:21 - Pasting the Code in Visual Basic
02:51 - Enabling Microsoft XML Version 3.0 Reference
03:18 - Requirement of Google API Key (2018 Update)
04:11 - How to Get Google Geocoding API Key
05:41 - Testing the Custom Function in Excel
06:05 - Conclusion and Final Thoughts
The VBA code used in this video was written by Christos Samaras.: www.myengineeringworld.net/20....
For a link to the VBA code, to download an Add-in that will make this function work across files, or for more instructions on how to use this tool visit: www.adventuresincre.com/auto-p...
To see other Excel tutorials, visit: www.adventuresincre.com/catego...
------------------------------------
Several people have been running into #VALUE and #NAME errors, and so I wanted to test to see if this tutorial still works (as of Nov 21, 2018) for me. I recorded a quick follow up video, where I follow the instructions from this video to see if the process still works. It does still work, and you can find that quick video here: • Auto Populate Latitude...
My best advice for those struggling to get this to work is to:
A) follow the instructions to the T. I know that's easier said than done, but if you're not saving the file as a Macro-Enabled file, not turning on Microsoft XML 3.0, entering the API key in the wrong place or inadvertently deleting something from the VBA code, or not entering the address correctly, etc, than you will get an error.
B) Make sure you're running the most recent version of Excel. This code may work with older version of Excel, but I have not tested it on versions earlier than 2016.
C) Make sure you have billing details on file with Google in order for your API key to work.
While Google gives you a certain number of API requests for free (~2500/day last I checked), Google requires users have a credit card on file to avoid abuse of the API.
Also here's a quick tip. If you use the GETCOORDINATES() function, the VBA code has a few built-in custom error messages that will help you troubleshoot what you're doing wrong. For instance, if you haven't setup your API key correctly, the cell will output: "Invalid API Key".
Note that in the video, I'm using Excel 365 on Windows 10, and I have provided billing details to Google in order to secure the API.
Several people have been running into #VALUE and #NAME errors, and so I wanted to test to see if this tutorial still works (as of Nov 21, 2018) for me. I recorded a quick follow up video, where I follow the instructions from this video to see if the process still works. It does still work, and you can find that quick video here: czcams.com/video/YUVMdfgAAGA/video.html
My best advice for those struggling to get this to work is to:
A) follow the instructions to the T. I know that's easier said than done, but if you're not saving the file as a Macro-Enabled file, not turning on Microsoft XML 3.0, entering the API key in the wrong place or inadvertently deleting something from the VBA code, or not entering the address correctly, etc, than you will get an error.
B) Make sure you're running the most recent version of Excel. This code may work with older version of Excel, but I have not tested it on versions earlier than 2016.
C) Make sure you have billing details on file with Google in order for your API key to work.
While Google gives you a certain number of API requests for free (~2500/day last I checked), Google requires users have a credit card on file to avoid abuse of the API.
Also here's a quick tip. If you use the GETCOORDINATES() function, the VBA code has a few built-in custom error messages that will help you troubleshoot what you're doing wrong. For instance, if you haven't setup your API key correctly, the cell will output: "Invalid API Key".
Note that in the video, I'm using Excel 365 on Windows 10, and I have provided billing details to Google in order to secure the API.
Instead of pulling from an address can you pull the coordinates from your location?
I requested the wrong function for my API from google (Maps) but when I selected "Places" for my API it worked!
Did the bit about billing and IT WORKED! Thank you!
Absolutely superb!! I just began working with GIS and this works like a charm! Thank you.
Spencer you are an amazing person on this planet. This planet is proud of you. Thank you.
thank you for the updated code. gets my project going again
Hey Spencer! Thank you very much, it is a really good explained tutorial!
Holy Shnikeys... It worked... Dude, you have no idea what you have done for me... I am not a real estate person, but this is huge for me..
I just tried this and it worked, thank you so much for the clear tutorial!
Thank you so much! Even if Google has changed some features of the service, the vba does works great.
Thank you! You have explained everything so clearly, wish all tutorials were like yours
Cem - appreciate the kind words!
Definitely works with just city/state or city/country. No zip needed. But nice to have. Great!
It works!! Thank you Spencer
Thanks for the video Spencer. Quick question, do you know if this should work for Canada addresses? I am getting the #VALUE error as well. thanks
Thanks, it works well !
It worked!!! Thanks for sharing
Thanks a lot! It works perfectly. That's Awesome.
Thank you for this tutorial. I’m venturing in uncharted territory - would you have a solution for an “Invalid API Key” message? FYI: there is an IP address restriction.
Thanks, very handy tutorial and relatively easy to implement. One suggestion, you’ve created two individual functions for latitude and longitude each requiring an API call if called individually. Quick solution return the lat/lng as a string into a single variable and then break down in Excel rather than VB. Reduces calls to the API and hence saving time and money.
Do you have a sample file of this?
It worked thank you so much!
Hi thanks for your tutorial. When I test it, I get Server denied the request. Any suggestions on what I can do?
Thank you Spencer, I got my api key, It worked with the sheets you have on your site as an example, but both methods (addin and VBA) return the same error. I tried using the address you typed in the video but the same, In Spanish it says "error de compilación" No se ha definido el tipo definido por el usuario. (ie. compilation error the user defines type hasnt been defined . The program stops in this line Dim Request As New XMLHTTP30. Have you seen it before, any idea where I can look? Many thanks.
Thanks. Do you have any video on the reverse please (i.e. latitude + longitude to an address ) please.
Very useful. Thanks
Will appreciate if reverse search and convert any given coordinate (lat/lon) into a complete address...
I keep getting Server denied request. Is there anything I can do? Also I needed to get a free trial on google to access the API key. Thank you for the video!
One note- I was having issues with the #Name error and realized that my address information wasn't entered correctly. Make sure your information is entered correctly: Street City Comma State Zip Code
Thanks for the awesome tutorial! Using this in Power BI!
I am trying to use it in Tableau, but it just doesn't work in Excel as of 2022. I keep getting "Server Denied the Request". It really shouldn't be this hard to get an API key.
really thanks
Is it possible to process multiple addresses at the same time in a column?
Does anyone know how to enable XML on Excel for Mac?
I believe i am running into a error because my data are in seperate coloms instead of one cell. Is there a way around this?
thanks Pat
For the #VALUE errors - one more thing to check: make sure you have enabled the Google Maps "Geocoding API". There are a number of different Google Map API's and this code requires the Geocoding API to work (and valid API key, billing account, etc.)
How do you do this?
@@eagillum I also struggled to do this, but I figured out how! In the current user interface (hopefully it doesn't change too often) I can go to my project on the Google Cloud Platform. On the left side there is a list of items, the top one is Overview and the second one is APIs. Click that. You will see a list of "enabled APIs" and "additional APIs" and to turn addresses into latitude and longitude, like pdmarsh said, you want "Geocoding". This is different from the "Places" API that is already automatically enabled.
I am using MAC OS Mojave Version 10.14.2 and the XML v3.0 library isn't shown at all...
Thanks a lot!
1 dollar for subscription in cloud, it's not bad
Can you do addresses in large batches?
How can i convert GEOHASH to late/long.
please help on urgent basis.
i want just automatically import excel data into system software ... could u help out
will this work if a Plus Code is entered into the address cell.
Thanks for sharing. Worked for me. As said below, now Google asks for a credit card.
its giving me the #NAME as the result, how to fix it?
Hello and thank you for your tutorial. I tested it and I got a Server denied the request. Is there a way to fix this
I got a new API and it is giving me #NAME?
I would appreciate any suggestion.
I have a program that exports the address in B, city in C, state in D, zip in E, I need lat in H and long in I to re-import with the GEO data; I now have Excel 2019 my 2010 would not work with this VBA script. I have it working as is if I move all the columns together in a single cell, if there is an easy way to combine B,C,D and E for the script and then save the data as .xlsx to secure the lat/long (as .xlsm you cannot remove the single cell information or the script changes it to #VALUE! since the city state zip are removed) and then pull them back apart... Any ides would be greatly appreciated...!!
Well sometimes over thinking it... Inserted a new column B, used =CONCAT(C2," ",D2," ",E2," ",F2) to get them in B, used the GetLat and GetLong as described; saved the file as CSV to preserve the Lat/Long, loaded it back into Excel as Text Data and removed the Inserted B column and saved as CSV again for re-import. Does not feel elegant but gets the job done....
I am not getting decimal point with lat/long function, only with coordinates. Do you know how to correct the VBA code to get properly formatted value? Thanks for your tutorial.
Same issue, did you find the solution?
The only solution I found for this was to multiply the value by 0.0000001
Hello, i need help as i am receiving the NAME error, what did i miss?
My Excel is not working. I am getting the value name errors. I watched the follow up video and google wants me to set up a billing account. Any way to get around this. I am not going to give then my credit info nor do I want cloud services.
Howdy, I'm still receiving a #VALUE! error when using this code. Any suggestions on workarounds?
Have you figured this out? I am also receiving #VALUE! when using this.
Me too
@Fortnite Funnny which of custom formulas gives you value..?
LatLong-Add-in-v3 file is not having module , any clue?
Thanks.
Me too, opened the download "follow the instructions to the T" the VB window does not show any module.
Get #name as a result. Verified google api is activate. So what’s the solution to the problem?
Throws value error. Tried everything in the comment section and troubleshooting section.
Can I use UK postcodes/ addresses with this function?
harkiran uppal Definitely, just done that exercise myself. Just ensure you format the address correctly.
I was struggling with both #VALUE and Api Key issues, and solved them deleting this lines of the VBA code:
'Check that an API key has been provided.
If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then
GetCoordinates = "Invalid API Key"
Exit Function
End If
Hope it works for ya'll as well
That worked thank you!!
Getting error Server Denied the request
I get Server denied the request. Not sure how to fix it.
Actually, once we got a key, we need to go to Google Cloud Platform and enable the API. We need to setup the billing account to enable the free trail. Otherwise the key won't work.
@@lingyu497 Thanks man, really helped me out
I am getting a #VALUE error, there is something missing in the video as everyone is getting the same kind of error.
The #VALUE error is a general error code created by Excel saying "something is wrong with the formula/code." As a result, one person may get an #VALUE error for a completely different reason than another. I've created a Troubleshooting Issues section of the blog post this video is related to here: www.adventuresincre.com/auto-populate-latitude-longitude-excel/. The most common reasons for this issue are failure to enable XNL v3.0 and inserting the wrong API key. It may also be related to how the address is entered. Wish I could be more helpful, but it's impossible to say exactly what's causing the error in your case without seeing the Workbook.
why it shows 0
Hello. Everything looks fine, but both functions (GetLatitude, GetLongitude) returns zero as coordinates. I double checked the API key, it works. Any idea?
Gábor Berki Might be how you’ve formatted the address. ,
API Key is not free anymore. So it doesn't work anymore in 2022
"Google Maps Platform features a recurring $200 monthly credit. The monthly credit applies towards each Maps-related billing account you create. This monthly credit resets on the first day of each month, at midnight Pacific time."
developers.google.com/maps/billing-and-pricing/billing
#VALUE ERROR
Thanks a lot for the video, looks like the API key from google is not free anymore
It says "Ambiguous name detected: GetLongitude"