• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Latitude and Longitude of any address using Google Map API and VBA

If you want to find Latitude and Longitude of any address .


Download Working File https://www.box.com/s/h733p8zufxkkum8nsi2d


Here is the UDF

Code:
Function lat_lon(a_t As String, c_t As String, s_t As String, co_t As String, z_t As String)
Dim sURL As String
Dim BodyTxt As String
Dim apan As String, la_t As String, lo_g As String
Dim oXH As Object
'create web url
sURL = "http://maps.googleapis.com/maps/api/geocode/xml?address="""
sURL = sURL & Replace(a_t, " ", "+") & ",+" & Replace(c_t, " ", "+") & ",+" & Replace(s_t, " ", "+") & _
",+" & Replace(co_t, " ", "+") & ",+" & ",+" & Replace(z_t, " ", "+") & ",+" & _
"&sensor=false"""
' browse url
Set oXH = CreateObject("msxml2.xmlhttp")
With oXH
.Open "get", sURL, False
.Send
BodyTxt = .RESPONSETEXT
End With
apan = Application.WorksheetFunction.Trim(BodyTxt)
'Latitude
apan = Right(apan, Len(apan) - InStr(1, apan, "<lat>") - 4)
la_t = Left(apan, InStr(1, apan, "</lat>") - 1)
'Longitude
apan = Right(apan, Len(apan) - InStr(1, apan, "<lng>") - 4)
lo_g = Left(apan, InStr(1, apan, "</lng>") - 1)
lat_lon = "Lat:" & la_t & " Lng:" & lo_g
End Function
To know more visit https://developers.google.com/maps/documentation/geocoding/


Assuming


Street In Cell A2

City In Cell B2

State In Cell C2

Country In Cell D2

Zip In Cell E2


In cell F2 type formula like =lat_lon(A2,B2,C2,D2,E2)


Download Working File https://www.box.com/s/h733p8zufxkkum8nsi2d
 
Last edited by a moderator:
Hi, ashish koul!

Always very interesting your shared tips and links. I'll give it a try asap, since a few months ago I were so lazy to avoid building a solution like this. Thank you very much.

Regards!

PS: Last time you and other users inspired this, hope you find it useful if didn't see it yet:

http://chandoo.org/forums/topic/excel-files-documenting-inspector


EDIT: works fine, tested with home addresses and public buildings in my city.
 
Last edited by a moderator:
@SirJB7 Yes you will see lot ppl using this method most of the steps is already covered on Google Map API documentation site like how to create a web URL and what will be the output in XML format, etc.


All you will find different is the tricks used to extract the data from XML result.w


btw if I would have copied the code from other source. I would have given the source of code. Anyways Thanks...
 
Hi Chandoo,

Is there a way to get the street, city, state & country by providing the lat and log details to google maps? Do you have any example of that?

Cheers
Sujay
 
@Sujay
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: Please don't answer here at this thread.
 
@ Ashish Koul

I have and use Mapcite for Excel. I love it. The only problem with it is that I do the same thing over and over as I create many different maps from the same data set formats. I would love a way to make this faster by writing some VBA to do the mapping steps for me. However, I could not find any Mapcite references in VBA editor. Do you know if we are able to call Mapcite References at all?
 
Hi Ashish Koul,

What is the requirement for the Geocoding Macro you have created .

Do we require Google API key ?
And How many records at a time we can process in excel. (Though i was able to process 200 records at a time.)

Any help would be greatful.

Btw Nice work done on the Geocoding Macro . Applause . :)

Thanks.
Umesh
 
found this in my files as cleaning out my old laptop we used to use this previously and it was great looks like Google have changed some map items and the map is now not good but it was a great way to do lots of post codes so if anyone has a work around would be good to know
 

Attachments

  • Map Builder Sept 18.xlsm
    102.3 KB · Views: 24
Back
Top