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

#### ashish koul

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

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

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)

Last edited by a moderator:

#### SirJB7

##### Excel Rōnin
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:

#### Debraj

##### Excel Ninja
Last edited by a moderator:

#### SirJB7

##### Excel Rōnin
Hi, ashish koul!

Very interesting link, thanks for sharing.

BTW, I found this link of year 2005:

It seems as there're many implementations of this method, so if the one posted is of your own just keep on going!... but if not, it'd be nice and fair to quote the source of each article.

Regards!

#### ashish koul

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

#### Sujay

##### New Member
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

#### SirJB7

##### Excel Rōnin
@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:

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!

#### rgiljohann

##### New Member
@ 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?

#### Umesh Lohani

##### New Member
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

#### allanwalton01

##### New Member
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

• 102.3 KB Views: 12