Get google maps place name from lat/long coordinates in excel

[sabha]

I am looking to find an address on google map and then construct an url where people can click and find directions. I want to do it in excel but not sure if it is possible. So far I was able to find the address in Json format and need help on how to get the formatted_address value from the result.

The url that gives me formatted_address is
https://maps.googleapis.com/maps/api...1&key=YOUR_KEY
(I have generated a key at https://console.developers.google.com)

I have latitudes in column F and longitudes in column G in the range F5:G100 and want to have the result (formatted address) in column K. I wish to request the programmers out here if they can help me with some kind of a UDF or a VBA that can help me achieve this.

Here is what I get from the above url. The result is too big so i have truncated it. If there are two or more formatted_address listed, then I need the first one.

Please Login or Register  to view this content.

Thanks

[Kyle123]

You can't use the google web service when you aren't displaying a map, it's against the terms and conditions.

There are other reverse geocoding services out there though, so if you find one you're happy with, link to it and we'll see what we can do

[LordLoki]

Are you sure you can not use it when his Link points to a google Map for the Direction?
It says "you will not use the Content in a Maps API Implementation without a corresponding Google map"

In this case the API is used to generate a Link that goes to Google Maps to get directions. I Would count that as "Corresponding Google Map" :D

[Kyle123]

You don't need to reverse geocode for that, you can use the lat lon directly, so yes :p

[LordLoki]

good point did not think about that :D

[sabha]

Thank you Kyle and Lorloki for your reply. I could not find other reverse geocoding services. I found an url that does not require a KEY
Here is the url
http://maps.googleapis.com/maps/api/...671,77.0977691
and here is the script (it gives a lot of info but i need only complete address)
Fiddle to check result for the above script. http://jsfiddle.net/NNuDe/367/

I found one service that finds location using client side IP address using ipinfo.io but it does not give me the full address
Here is the link http://jsfiddle.net/zK5FN/2/

Moreover, I also found an html code that gives me what i want but it is in html format and i need it in excel. Could not paste the code as it is giving me firewall error. Its a post answered by Michal with 125+ upvotes available at http://stackoverflow.com/questions/6...ng-geolocation

My objective is to get the formatted complete address in excel and appreciate if someone can help me. Thank you !

[Kyle123]

What do you actually need, your first post contradicts itself.

Do you need a link (from lat/long) that opens at the correct location in google maps that someone can click from excel?

OR

Do you want to get address details from a lat / long?

Other reverse geocoding service: https://msdn.microsoft.com/en-us/library/ff701710.aspx

[LordLoki]

Here is some code i quickly throw together using openstreetmaps that should get you on the right way

Please Login or Register  to view this content.

[sabha]

I went to try it. It worked fine and result looks good too. However, when i change the lat/long to the one in question then it halts on the below line with an error Run time error 5 invalid procedure call or argument
getfield = Mid(Data, Start, ende - Start) & " "

[LordLoki]

Hi the Error you get is probably cause the XML does not have one of the fields you want to show.
My home adress has no town for example

i played around with the thing a bit and put together a function that should account for this.
Check out the file i attached that should give you enough to get to the desired result

if you have a very long list it would be better to use a button that adds the adress data etc instead of an UDF
But if its just a couple this should work fine and you can easy adapt it with a little effort

Greets Loki

[sabha]

Wonderful !
Appreciate you help. God bless.

[tonexplus]

Hi!
Looks good but it seems to me not working...
Is it still actual?

Jozef