Auto Populate Latitude and Longitude Function in Excel (NEW VERSION)

[@adventuresincre]

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: kzbin.info/www/bejne/j4a5fpecnKZ0faM 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.

[@Deerefacts]

I requested the wrong function for my API from google (Maps) but when I selected "Places" for my API it worked!

[@miguelkhan6020]

Did the bit about billing and IT WORKED! Thank you!

[@chipymunk1]

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..

[@pv0315]

Spencer you are an amazing person on this planet. This planet is proud of you. Thank you.

[@sammweh]

I just tried this and it worked, thank you so much for the clear tutorial!

[@charisselavell4119]

Hi thanks for your tutorial. When I test it, I get Server denied the request. Any suggestions on what I can do?

[@pdmarsh]

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.)

[@eagillum]

How do you do this?

[@bidaubadeadieu]

@@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.

[@cmsty92]

Thank you! You have explained everything so clearly, wish all tutorials were like yours

[@adventuresincre]

Cem - appreciate the kind words!

[@ElizabethSLazaro]

Definitely works with just city/state or city/country. No zip needed. But nice to have. Great!

[@ciairmi]

Thank you so much! Even if Google has changed some features of the service, the vba does works great.

[@MKdreambox]

Absolutely superb!! I just began working with GIS and this works like a charm! Thank you.

[@Steve-hu7rk]

Does anyone know how to enable XML on Excel for Mac?

[@konstantinegorov7028]

thank you for the updated code. gets my project going again

[@taylorhenderson8123]

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!

[@lemaywebdesign]

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.

[@omairbinenam6337]

Will appreciate if reverse search and convert any given coordinate (lat/lon) into a complete address...

[@richardhabbershaw2988]

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.

[@user-hl2pv3pt3w]

Do you have a sample file of this?

[@blackwhite7134]

Hey Spencer! Thank you very much, it is a really good explained tutorial!

[@AlexanderArndt]

I am using MAC OS Mojave Version 10.14.2 and the XML v3.0 library isn't shown at all...

[@user-fv9pq8yf3u]

Throws value error. Tried everything in the comment section and troubleshooting section.

[@zipovimg]

It worked!!! Thanks for sharing

[@sumantra_sarkar]

Thanks. Do you have any video on the reverse please (i.e. latitude + longitude to an address ) please.

[@UnavailableMMaciel]

Thanks for sharing. Worked for me. As said below, now Google asks for a credit card.

[@ektasharma6225]

i want just automatically import excel data into system software ... could u help out

[@billjenkins1535]

will this work if a Plus Code is entered into the address cell.

[@i-nomaprofessionaldevelopm9877]

Is it possible to process multiple addresses at the same time in a column?

[@TopLooper]

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!

[@eagillum]

Can you do addresses in large batches?

[@mayirialcala6395]

It worked thank you so much!

[@ricardopierrepont6271]

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.

[@lucasll2198]

Very useful. Thanks

[@moyavlog]

It works!! Thank you Spencer

[@patbcsllc]

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

[@aymantabbaa1655]

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

[@aymantabbaa1655]

I got a new API and it is giving me #NAME? I would appreciate any suggestion.

[@MsAnderhi]

Howdy, I'm still receiving a #VALUE! error when using this code. Any suggestions on workarounds?

[@TopLooper]

Have you figured this out? I am also receiving #VALUE! when using this.

[@victorbetancurt6786]

Me too

[@mienzillaz]

@Fortnite Funnny which of custom formulas gives you value..?

[@darbycarpenter3032]

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.

[@garagengio]

API Key is not free anymore. So it doesn't work anymore in 2022

[@adventuresincre]

"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

[@francisuche1537]

Hello, i need help as i am receiving the NAME error, what did i miss?

[@pagalpaisa]

How can i convert GEOHASH to late/long. please help on urgent basis.

[@davidg.4856]

Thanks a lot! It works perfectly. That's Awesome.

[@MarshallMelnychuk]

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

[@benoitlepetitcolin9469]

Thanks, it works well !

[@anjsorn]

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.

[@woutertt]

Same issue, did you find the solution?

[@oscarberrocal6801]

The only solution I found for this was to multiply the value by 0.0000001

[@Zorios17]

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

[@Doubrovskaia]

That worked thank you!!

[@tammamhazzouri1067]

LatLong-Add-in-v3 file is not having module , any clue? Thanks.

[@tyfisher9876]

Me too, opened the download "follow the instructions to the T" the VB window does not show any module.

[@orlandovelazco8337]

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.

[@karlmoyers2537]

Get #name as a result. Verified google api is activate. So what’s the solution to the problem?

[@jatt1598]

Can I use UK postcodes/ addresses with this function?

[@richardhabbershaw2988]

harkiran uppal Definitely, just done that exercise myself. Just ensure you format the address correctly.

[@marlyntamayo4080]

its giving me the #NAME as the result, how to fix it?

[@jasonnorton3677]

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...!!

[@jasonnorton3677]

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....

[@asif1179]

Getting error Server Denied the request

[@RaviYadav-bu2pg]

I am getting a #VALUE error, there is something missing in the video as everyone is getting the same kind of error.

[@adventuresincre]

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.

[@lingyu497]

I get Server denied the request. Not sure how to fix it.

[@lingyu497]

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.

[@Thinkingoutloud-ek6xw]

@@lingyu497 Thanks man, really helped me out

[@mahmoudalykamel5589]

really thanks

[@jayaure8242]

why it shows 0

[@3djdavid]

Thanks a lot!

[@3djdavid]

1 dollar for subscription in cloud, it's not bad

[@gaborberki486]

Hello. Everything looks fine, but both functions (GetLatitude, GetLongitude) returns zero as coordinates. I double checked the API key, it works. Any idea?

[@richardhabbershaw2988]

Gábor Berki Might be how you’ve formatted the address. ,

[@miladalavi3530]

Thanks a lot for the video, looks like the API key from google is not free anymore

[@Diallende]

#VALUE ERROR

[@MsAnderhi]

It says "Ambiguous name detected: GetLongitude"