Excel Geocoding Tool v2

http://www.public-health.uiowa.edu Alan

I’ve been working with data that only has ZIP and ZIP+4 info so this new version is a huge help and works great! Much faster than batchgeocode.com.

Any way to add batch processing of data to get travel time and distace?? I would bow at your feet if this was possible in a similar fashion!!!!

Clayton

Is there a way to support Canadian Postal codes? Yahoo can process them, but is there a way to change the tool to allow this?

Thanks!

Landmine_Mapper

Just the macro that spits out the kml from the spreadsheet is awesome.
Have you seen anything that could do the same thing from an MSaccess database?

For some reason the kml file is not opening automatically when excel spits it out.
I checked paths. Running GEbeta4. Output.kml will open fine when double clicked.

I guess placing the export button on the toolbar and then allowing user to select
ranges for the X,Y,label,and multiple attributes would make it all 100% perfect

Many many thanks!

http://exceltool.info/?p=35 exceltool.info » Blog Archive »

[...] Excel Geocoding Tool v2 – Juice AnalyticsI m happy to announce a few small revisions to our Excel geocoding tool. The tool takes a list of addresses and will look up the latitude and longitude of those addresses. [...]

Underdetermined

I keep getting an error saying:

Compile Error:
Can’t find project or library

Do you have any advice?

Thanks. UD

dave

hi

can this be used for other countries other than the USA , if it could be developed to do so it would be great

seth

For some reason, this tool no longer works. That’s a shame. I loved this tool. So fast and easy.

http://www.election-countdown.com Marc

Great macros! Thanks.

What would need to be changed to make it possible to change the icon based on other data. For example, you could make republicans red and democrats blue.

Ujval Gandhi

Chris,

The http.send is preventing me from running the file. Could you tell me which Tools–> References to click to make this work ?

Sebastian

Works perfectly for me using a Yahoo API key. Thanks for a easy to use and great tool!

Dawn

I’m in love with this! Sadly, I can only do 1,000 records at a time, then it bombs with a runtime error. I want to do 210,000 records.

Adi

Chirs,
Its seriously great work, I just cant tell how much its helping me. As Dawn said apart from the error, it just works great. I also want this to give be results for about a 100,000 records. Let me know if I can be of any help. I am working on my VBA Skills.

Jay

Chris,
I am probably doing something wrong but
I have clicked on the link above Geocoding Tool v3.1.xls which sends me to http://media.juiceanalytics.com/downloads/Geocoding%20Tool%20v3.1.xls
which just hangs.
Thanks in advance for your help.

Jay

R. J.

Chris,

I have the same problem as Ujval Gandhi, the VBA bails on the http:send line. Any suggestions on how to solve the problem?

~ R. J.

Amanda

I tried to sign up for a geocoder.us username/password and never got one. So I signed up for a yahoo! account at:
https://edit.yahoo.com/registration?.intl=us&new=1&.done=http%3A//mail.yahoo.com&.src=ym
and it worked great!
Thanks for this great resource!
Amanda

Nathan

I’m not sure that the zip+4 is working. Shoud it show “zip+4″ in the Precision field? When I compare a a zip+4 with just the same Zip i am getting the exact same XY coord and both precision fields say “zip”. Please let me know if this is working correctly or what i can do to get Zip+4 working. Thank you.

John

Fantastic! Works well for UK and USA

Dicki Smits

Hello dear devoleper,

It seems a great program you wrote in excel. but is it possible to ad the field COUNTRY to the macro? than i can use it for the netherlands as well.
Now it is not working well for this country.

Thanks and i hope to hear from You.

Greetings

Bob

The link to the yahoo registration page for the geocoder seems to have moved (link broken). Not finding it on a quick google search. Can you post updated info?

bob

I am not finding the spreadsheet to work using the provided sample data. I am getting all “address not found” results. Haven’t modified the file other than adding in username and password, and selecting geocoder.us. Any ideas on how to debug? Hve internet connections, and hear the beep and see the status bar entry for the geocode address data.

http://xl.barasch.com Alan

Cool tool! — I need to figure out how to adapt this for Access. Parsing the XML is my big stumbling block

http://www.wisper-wireless.com WirelessGuru

Awesome Tool, So much faster than batchgeocode.com. Seems very accurate. i did 4,000 recodrs in one shot with no problems.

Thank you!!!

Kevin

Great Tool!

I used this tool a while back, and it worked great!

However, I just tried to use it today, and it keeps bombing out on me. I am getting the following error:

“A connection with the server could not be established”.

This is thrown in the mGeoCode Module, at the http.send command in the yahooAddressLookup Function.

Any ideas why this might be happening? I looked up the API documentation on Yahoo’s website, and the URL path in the same function/module has changed from what is in the VBA code. Could that be the error? I tired to change it myself, but it still bombed out. Thanks!

Kevin

Is it possible to include additional information in the KML output file to be displayed in various ways in Google Earth? For example, if I have information that details the income level of the head of household at each address, can that information somehow be included in the KML file so it can be color-coded in Google Earth? Or what if I have a flag that says a particular address is a “Friend” vs. “Business Partner” vs. “Family” for example. Can that information be included?

If so, how do I incorporate that information in the xls spreadsheet?

Thanks in advance for your help.
Kevin

Ken

Having the same connection issues. Seems to work fine as long as the address the querry address is well formed and findable, if the address cannot be found the script often errors out with “a connection could not be established”

Ken

EDIT: And now the script is working fine again. Strange. Seems odd that yahoo would be having server issues….

Tanya

I try downloading the file and get errors:

Mod_python error: “PythonHandler django.core.handlers.modpython”

Traceback (most recent call last):

File “/usr/lib/python2.5/site-packages/mod_python/apache.py”, line 299, in HandlerDispatch
result = object(req)

File “/usr/lib/python2.5/site-packages/django/core/handlers/modpython.py”, line 177, in handler
return ModPythonHandler()(req)

File “/usr/lib/python2.5/site-packages/django/core/handlers/modpython.py”, line 150, in __call__
response = self.get_response(request)

File “/usr/lib/python2.5/site-packages/django/core/handlers/base.py”, line 59, in get_response
response = middleware_method(request)

File “/usr/lib/python2.5/site-packages/django/middleware/cache.py”, line 55, in process_request
cache_key = get_cache_key(request, self.key_prefix)

File “/usr/lib/python2.5/site-packages/django/utils/cache.py”, line 134, in get_cache_key
headerlist = cache.get(cache_key, None)

File “/usr/lib/python2.5/site-packages/django/core/cache/backends/memcached.py”, line 19, in get
val = self._cache.get(key)

File “/usr/lib/python2.5/site-packages/python_memcached-1.40-py2.5.egg/memcache.py”, line 619, in get
check_key(key)

File “/usr/lib/python2.5/site-packages/python_memcached-1.40-py2.5.egg/memcache.py”, line 888, in check_key
raise Client.MemcachedKeyCharacterError, “Control characters not allowed”

MemcachedKeyCharacterError: Control characters not allowed

Bonnie

Same problem as Tanya above. Am I supposed to get a spreadsheet? Am I doing anything wrong?

http://juiceanalytics.com Chris Gemignani

Bonnie/Tanya: It should link correctly to the spreadsheet now. Thanks.

Susana

Apparently I am not the only one with this problem. When I try to run the macro I am getting the message that the server name or address can not be resolved. How can I fix this?

Pete

I just tried to view my output.kml file and I’m getting a Google Earth error. Opening of file failed… parse error at line 11, column 20…not well-formed (invalid token). Is this due to the new version of Google Earth?

Nathan

Thanks for this very usefull tool. I used the same idea to create my own Geocoding program in MS Access. I wrote an application that gets the lat, long and precision from both GOOGLE and YAHOO servers, determines which one is best precision, and even calculates the distance between them. I was wondering about the 5,000 daily limit. Lately I have been doing way more, about 30,000 – 40,000 a day with no problems. Does anyone know if they lifted the limit?

James

Nathan – Using the GeocodingTool v3.1.xls, I have been able to Geocode way more than the said 5,000 limit. So it would appear to me the limit has been lifted.

on a side note – does anyone know how to Geocode locations outside of North America?

Wes

I have been able to geocode 55K in one day with Yahoo. The API has been lifted in conjunction with Y! Search Boss.
http://developer.yahoo.com/search/boss/

Tim

Nathan – I am interested in your Access geocoding program. Do you have plans to make it available?

Mark

I’m italian and we use the decimal char = comma “,”, so I have to edit the output.klm file and change the string
coordinates>12,90542,43,912535,0 in
coordinates>12.90542,43.912535,0 (change “,” withh “.”.
Is possible to change the program for European use?
Thanks very mutch.

Salah

All,

Can someone suggest a good geocoder that will also correct addresses and is in executable format ? I can’t use API’s.

Thanks,

Sal@khattak.com

Simon

Hello! very nice tools! Bravo!

We use it to map some of our point of sales. Plus, it is possible to change the icon and the color used allowing to display different POS in different way (red=bad, green=good, etc…).

I have the same problem then Mark. I lived in canada and our decimal separator is also a coma (65,0000). i have to manually change the KML file. however, i think I can manage modifying the macro by myself

Scott

I love the geocoding tool. Works great for addresses. Can this tool work for intersections? I have a list of motor vehicle accidents which only locational information is the intersections and need lat-long.

Dave M

It does work with intersections, use “AND”
e.g., Main St AND North St

Jonathen

I get the “A connection with the server could not be established” error that people were getting awhile back. The error is the “http.send” line in the “mGeoCode” module. I get the error with Yahoo and geocoder.us no matter which one I have selected to use. (I have set up an account with both).

James

Jonathen – I am having the same issue. It appears this tool stopped working sometime within the past week or two.

Chris – any chance you can take a look at this?

Many thanks,
James

Sal Khattak

This is a GREAT TOOL.
I use it almost everyday to geocode properties. I am glad the guys at Juice took the time to develop this and save me countless hours…

David CO

Very nice tool! Thank-you. Using Yahoo it also works for european countries provided you change the zip cleaning funtion [Function geocodecleanZip] in the code (Zipcodes in Belgium have 4 numbers, for exemple), and change “state” by “country” in the “URL =” line from the [Function yahooAddressLookup]in the mGeoCode Module. Some special character cleaning is also usefull. =New options for version 4?

SinginInTheRain

if comma “,” needs to be replaced by period “.” you need to

1. open the Visual Basic Environment in Excel ( [Alt] + [F11] )
2. on the upper left corner expand the Tree to VBAproject (Geocoding) -> Module and double-click mKML
3. go to line 100 and insert
latitude = Replace(latitude, “,”, “.”)
longitude = Replace(longitude, “,”, “.”)
4. save and retry

Carl R

Has anyone converted this to *.xlsx?

Marty O

I was excited to find this tool, appears to be exactly what I need. Unfortunatley I can’t seem to get it to work with either the supplied data examples or my data. After reading through these comments, I’ve run it with my firewall turned off … still get the “not found” result. I’m using it with a Geocoder.us account.

Appreciate any thoughts or suggestions on how to overcome this problem.

matt

Is there any chance that an option to use the Google geocoder can be added? The Yahoo! one almost never understands addresses given as street corners.

Joy

somehow i can’t get color coded markers when i upload the file to google maps. i’d like to designate different levels of prospects or clients. please help!

Sal Khattak

I tried to follow the instructions and added the two lines of code to start using google for geocoding with google but where do I insert my google API key and where do I select google to geo code with google ?
I can really use help with this or if anyone already has the juice utility working with google, can you guys e-mail it to me please ?
Thank you in advance !
SAl

Sal Khattak

I tried to follow the instructions and added the two lines of code to start using google for geocoding with google but where do I insert my google API key and where do I select google to geo code with google ?
I can really use help with this or if anyone already has the juice utility working with google, can you guys e-mail it to me please ?
Thank you in advance !
SAl
sal@khattak.com

KrezzyKid

It stopped working;
Yahoo must have done something to their api or changed the syntax or something.
Dear Chris Gemignani, can something be done about that?
I have too little understanding of this matter to do something about it.

strangely enough http://www.batchgeocode.com/ still yahoo-geocodes my addresses. I just can’t modify the kml-output and in my opinion it is too intricate for anyone in my office to use…

Aperi

Recently, We have been able to geocode only around 350-360 address in the excel tool.. Is this a know problem?? Is there a way to bring the # back to 5K, Any repsonses would be greatly appreciated,
THanks!

Sal Khattak

If you can no longer geocode more then a few hundard address via yahoo. Please release / renew your IP address. You have to keep repeating this every minute.
Yahoo is treating constant requests as a DDOS attack I guess.

Sal Khattak

Does anyone have this utility working with GOOGLE YET ?

Please e-mail me:
sal@khattak.com

Aries

This is a great tool. Is there any way I can change it to work with Google Maps API?

jk

Great tool! I’m modifying it for use in an Access database.
http://riceball.com/d/content/ms-access-geocoding-and-distance-reporting

Chuck

I rec’d a error message in excel 2007 even with the dummy data, it reads: Microsoft Visual Basic – Run-time error ’429′ Active-x component can’t create object

Bernard

Hi, great tool. One problem I have though is I get a
Parse error at line 21. Not well formed (invalid token). Line 21 is where I have an api chart url for display as the point icon. The url works fine when I paste it into a browser or paste it directly into G Earth as a custom icon. However I can’t add the kml which your tool creates. There are no blanks etc. Any help would be excellent,
ta,
Bernard

Greg

I’m looking to geocode all addresses by zip code or some other means. Is this possible?
One problem I have is that I have a list of address, but I know some are missing and address ranges don’t help.
I also understand that the 2010 census is collecting lat/longs for all addresses collected. Any chance that these will be used in fucture geocoding applications?

Marian

hi there,
has anyone got a solution concerning the “http:send” line yet?
the macro always seems to stop at that point.
thanks for your help
marian

Chrispy1

Hi Marian,

I’m running into the same problem and believe it’s because I’m running the code while inside my company’s firewall. if i’m at home and not connected to my company’s network, everything runs beautifully. I’m currently investigating a workaround, and will post back here if I figure something out…..
Chris

Marian

hi folks,
in my case it doesn`t work at home either not sure whether the firewall is causing this problem. Chrispy, did you work it out yet? appreciate any help on this.
cheers
marian

Fillipe

Hi All,
I had the same problem at the company’s network. I just unplugged the cable and went to the aircard and it worked. So it must be something related to firewalls.
Cheers
Fillipe

Kirnbauer

Hi,
When I use the latest geocoder tool (v3.1.xls) and click view in google earth, I get a message that says “could not open file documents output.kml” – how do I fix this? I don’t want to have to click “ok” then navigate to the file and double-click it (which will work but is not ideal).

Many thanks.

El-Noor

Fantastic tool! I modified the code to accept Canadian postal codes. Very comprehensive programming – completely impressed for a free tool like this.

Martin

Hello. El-Noor, is it possible to ask for your version supporting canadian postal codes. I would need that. If you can share it. You can email to martin_chalifoux@hotmail.com

Andy

Hi,
Worked perfectly the fist time I used it but now I’m getting inconsistent results. Some times it finds the location and other times it just comes up with “not found”. It’s the same location so how can it find it one time and not the next?
Any ideas what’s going on?
Thanks,
Andy

Gerard

Hi there,
Nice tool, unfortunatly the dutch zipcode consists of 4 digits, and 2 letters. therefore it isnt really useable for dutchmen. is there a way i can make this work for me?
Example dutch zipcode: 2122 GM; or 1099AE

Greetz Gerard

nico

@Kirnbauer save the file in root or other directory, that works for me…

Tamara

I have the same problem as Andy back in March. I am getting not found results for addresses even when i have the same location already mapped. Also, it seems to be erroring out in spite of no spelling errors.

http://twitter.com/IvanTasev Ivan Tasev

Exelent tool. I’m using it very often. Thanks Bro!
Also like to share this:

http://mapsys.info/geocoding-tool/

There is geocoding and reverse geocoding tool for manual geocoding and fine tune of the points coordinates.

GtH

Hallo Gerard,

Ik las je vraag over het geocoderen van NL postcodes met 4 cijfers en 2 letters. Is hier inmiddels al een oplossing voor gevonden ?

Alvast bedankt voor je reactie.
m.vr.gr.
GtH
gth@dth.net

http://www.thedirecttree.com John

great tool… thanks thats very usefull

Zulma_Prieto

Hi there, I am trying to use the file, but I get the message that a connection with the server cannot be stabished
Could someone help me to find out what I am doing wrong?
Thanks

Mallory

El-Noor could you send me your version that supports Canadian postal codes? That would be a huge help! mallory.masfrankc@gmail.com

Angelfiggas

can u share this version with me as well please – angelfiggas@yahoo.com

http://twitter.com/stevecarlton Steve Carlton

Well I was having issues coding addresses today which stopped around 590 addresses.

I visited the Yahoo API page and noticed they have deprecated the API this tool uses and that the new API supports 50,000 addresses per day.

The good news is that the change was very easy to fix in the macro, can we have a v3.2 of this tool? It should only take 5 minutes

The yahooaddresslookup function needs to be updated:
URL = needs to be updated to…
URL = “http://where.yahooapis.com/geocode?q=” & street & “,” & city & “,” & state & “&appid=” & yahoo

Also, the output from the API has changed slightly. The main issue is that the API no longer gives a precision (address, city, zip) like before, but this can easily be substituted for the quality score given by the new API.

These lines also need to be updated for the new API output:
lat = RegExMatch(response, “([.-0-9]+)”) lng = RegExMatch(response, “([.-0-9]+)”) precision = RegExMatch(response, “([.-0-9]+)”)

That’s it! I haven’t run a huge test, but I have 22,000 addresses to code tonight so I’ll let you know tomorrow.

Here is the info on the new API http://developer.yahoo.com/geo/placefinder/

Steve

http://twitter.com/colocarto Christopher Rice

Chris! Great application dude! Helped me out big time yesterday! I highly recommend to anyone needed to gcode a long list of addy’s. FYI get a Yahoo Map API key, the other one didn’t work for me.

guest

Sometimes occurs when you have approached the limit in Yahoo, I think.

Cassidysf

Compile error:

The code in this project must be updated for use on 64-bit systems.
Please review and update Declare statemetns and then mark them with the PtrSafe attribute.

Jeff Goudie

Hey for some reason all of my address’ are coming back as “not found” I have all the write address’ cities, state, and zip. what could be the problem? Do i need to buy credits for the geocode?

HessMan

Sad, I was really hoping this would have worked for me until yahoo decided to demarc the api. If we could have a v.3.2 that would be great! thanks! unfortunately i don’t know anything about vbs coding but easy enough to update the script if I had a guiding hand. try to find an alternative elsewhere.

Anguslou

I tried geocoding tools v3.1 for an UK address but it was different from what I input directly on google map (which was correct). Why?

http://profiles.google.com/qingdom Joa Ly

Hello Steve, did you run on 22,000 addresses ever work? if so, would you mind sharing what kind of code edits you made with the VB?

I’m getting constant ‘not found’ errors for anything I throw at it with what you mentioned above. The utility is no longer working for me at all.

Any help is greatly appreciated. Thank you.

Rkaspar

I too was looking at an update to this. I found the same changes that Steve did before I found this page, and was looking for the next step.

Any thoughts?

-Rk

Rkaspar

Update – I finally decided to actually run my code update instead of staring at VBA, and it did work. Best of luck, steve’s changes match mine and they work for me.

http://twitter.com/maxrice Max Rice

I edited the file to use the new Yahoo PlaceFinder API and parse the new response, as well as encode the url before sending to yahoo. With an app key, I believe it now supports 50,000 addresses per day. I also made the file compatible with 64bit versions of Excel, though I don’t know if this breaks it for 32bit versions.

You can download it here: http://dl.dropbox.com/u/19032952/Geocoding%20Tool%20v3.2.xls

http://twitter.com/stevecarlton Steve Carlton

Thanks Rkaspar, I meant to update to file today but it looks like someone beat me to it! I recently switched to Excel on Mac and now Im getting other errors.

http://twitter.com/stevecarlton Steve Carlton

Hey Joa,

My 22,000 address query worked great on the new API. Looks like someone else posted an update to the excel file.

I had several requests to update this file today and took a look but I recently switched to Mac and had other compile errors that I didn’t have time to look into.

Hopefully the updated posted today helps you!

Steve

http://twitter.com/stevecarlton Steve Carlton

Thanks Max for posting this update, I didn’t get around to giving everyone an updated file.

I am getting some compile errors on Excel 2011 for Mac (on your version and my version), any ideas why? Im new to Mac

http://twitter.com/maxrice Max Rice

I looked into this and the problem is that the Regular Expression vbscript library isn’t available on Mac. This is pretty much a deal breaker for the whole tool since it uses regex in multiple places to parse results, validate zip codes, etc. The only option is to rewrite the regex module using applescript to access osx shell programs (macscript() + awk, sed, etc).

Chris

Getting compiling errors as follows:

Expected sub or function

(in red)
Declare PtrSafe Function GlobalUnlock Lib “kernel32″ (ByVal hMem As Long) _ As LongDeclare PtrSafe Function GlobalLock Lib “kernel32″ (ByVal hMem As Long) _ As LongDeclare PtrSafe Function GlobalAlloc Lib “kernel32″ (ByVal wFlags As Long, _ ByVal dwBytes As Long) As LongDeclare PtrSafe Function CloseClipboard Lib “User32″ () As LongDeclare PtrSafe Function OpenClipboard Lib “User32″ (ByVal hwnd As Long) _ As LongDeclare PtrSafe Function EmptyClipboard Lib “User32″ () As LongDeclare PtrSafe Function lstrcpy Lib “kernel32″ (ByVal lpString1 As Any, _ ByVal lpString2 As Any) As LongDeclare PtrSafe Function SetClipboardData Lib “User32″ (ByVal wFormat _ As Long, ByVal hMem As Long) As Long

http://twitter.com/maxrice Max Rice

I think the changes I made to make the tool 64bit compatible broke it with 32bit versions of Excel. Try this version and let me know if it works for you.

http://dl.dropbox.com/u/19032952/Geocoding%20Tool%20v3.2-32bit.xls

Jcarlosluna

geocode has changed its URL address find it in the macros y replace it by the new URL address

Luke

I don’t know if it’s the API or the excel file but I’m getting some strange results for addresses in australia.

http://pulse.yahoo.com/_DKFM4OL7743UFSM2OIDDPPVZWQ thomas

Hi there !
Thank’s a lot for your tool !

I have translate it in french and add some fonctions like Y! PlaceFinder (new API from Yahoo!) or GoogleMap v3…

You can download this update here :
http://www.icocarto.lautre.net/drupal/?q=node/48

Respect and Merci beaucoup !
ïco

Nagendra0

can anyone please upload the latest version. All I I get is not found. Does this work with all versions of excel?

http://twitter.com/maxrice Max Rice

Try this: http://dl.dropbox.com/u/19032952/Geocoding%20Tool%20v3.2-32bit.xls

AJ

Max .. thanks a lot. worked very well.

Basil Veerman

Also need to change the URLEncode line to &postal instead of &zip to conform with new api.

I’ve also modified it to work with Canadian postal codes by changing:
Function geocodeCleanZip(zip As String) As String ‘ normalize zipcode to 5 digits or 9 digits
zip = RegExValidate(zip, “[A-Za-z0-9]“)

If Len(zip) = 4 Or Len(zip) = 5 Then
geocodeCleanZip = Application.WorksheetFunction.Text(zip, “00000″)

ElseIf Len(zip) = 6 Then
geocodeCleanZip = zip

I really don’t do macros and the downside to my edit is that ANY 6 number/letter combination will be passed… Does anyone have a better validation for this case?

Anonymous

Due to a recent change in the way my company routes traffic
to the internet, I must go through a proxy.
I found the following code and incorporated it into the Geocoding tool.

Module mGeocode

Function yahooAddressLookup

Change from this code

‘Create Http object

If IsEmpty(http)
Then Set http = CreateObject(“WinHttp.WinHttpRequest.5.1″)

‘Send request To
URL

http.Open
“GET”, URL

http.send

‘Get response data
As a string

Change to this code

‘Create Http
object

If IsEmpty(Http)
Then Set Http = CreateObject(“WinHttp.WinHttpRequest.5.1″)

‘ ‘Send request To
URL

‘ Http.Open
“GET”, URL

””— This Proxy code allows a query through the
proxy.

‘http://forums.aspfree.com/visual-basic-programming-38/proxy-auth-in-this-vb-script-20625.html

‘ Set to use PROXY

‘
http://msdn.microsoft.com/en-us/library/aa384059%28v=VS.85%29.aspx

Const HTTPREQUEST_SETCREDENTIALS_FOR_PROXY = 1

Const HTTPREQUEST_PROXYSETTING_PROXY = 2

Const AutoLogonPolicy_Always = 0

‘ Change the “YOURPOXYHERE” to your proxy id. For example 155.247.10.25:8000. Leave the quotation marks in place

Http.SetProxy HTTPREQUEST_PROXYSETTING_PROXY, “YOURPOXYHERE”,
“*.intra”

Http.Open “GET”, URL, False

‘Http.SetCredentials
“DomainAccount”,”*********”,HTTPREQUEST_SETCREDENTIALS_FOR_PROXY

Http.SetAutoLogonPolicy AutoLogonPolicy_Always

””— END This
Proxy code

Http.send

‘Get response data
As a string

Anonymous

Due to a recent change in the way my company routes traffic
to the internet, I must go through a proxy.
I found the following code and incorporated it into the Geocoding tool.

Module mGeocode

Function yahooAddressLookup

Change from this code

‘Create Http object

If IsEmpty(http)
Then Set http = CreateObject(“WinHttp.WinHttpRequest.5.1″)

‘Send request To
URL

http.Open
“GET”, URL

http.send

‘Get response data
As a string

Change to this code

‘Create Http
object

If IsEmpty(Http)
Then Set Http = CreateObject(“WinHttp.WinHttpRequest.5.1″)

‘ ‘Send request To
URL

‘ Http.Open
“GET”, URL

””— This Proxy code allows a query through the
proxy.

‘http://forums.aspfree.com/visual-basic-programming-38/proxy-auth-in-this-vb-script-20625.html

‘ Set to use PROXY

‘
http://msdn.microsoft.com/en-us/library/aa384059%28v=VS.85%29.aspx

Const HTTPREQUEST_SETCREDENTIALS_FOR_PROXY = 1

Const HTTPREQUEST_PROXYSETTING_PROXY = 2

Const AutoLogonPolicy_Always = 0

‘ Change the “YOURPOXYHERE” to your proxy id. For example 155.247.10.25:8000. Leave the quotation marks in place

Http.SetProxy HTTPREQUEST_PROXYSETTING_PROXY, “YOURPOXYHERE”,
“*.intra”

Http.Open “GET”, URL, False

‘Http.SetCredentials
“DomainAccount”,”*********”,HTTPREQUEST_SETCREDENTIALS_FOR_PROXY

Http.SetAutoLogonPolicy AutoLogonPolicy_Always

””— END This
Proxy code

Http.send

‘Get response data
As a string

Anonymous

Repost to attempt to fix the mangled code. I used this same code in an older version of the tool and it woked fine both at work behind the proxy and at home with no proxy. If someone wanted to update the master file, they may want to put the proxy IP code on the Settings and Instructions sheet.

‘Create Http object
If IsEmpty(Http) Then Set Http = CreateObject(“WinHttp.WinHttpRequest.5.1″)

‘ ‘Send request To URL
‘ Http.Open “GET”, URL

    ‘— This Proxy code allows request the corp firewall.
    ‘This is where I got the code.
    ‘http://forums.aspfree.com/visual-basic-programming-38/proxy-auth-in-this-vb-script-20625.html

    ‘ Set to use PROXY
    ‘ http://msdn.microsoft.com/en-us/library/aa384059%28v=VS.85%29.aspx
    Const HTTPREQUEST_SETCREDENTIALS_FOR_PROXY = 1
    Const HTTPREQUEST_PROXYSETTING_PROXY = 2
    Const AutoLogonPolicy_Always = 0

    ‘ Change the “YOURPOXYHERE” to your proxy id. For example 155.247.10.25:8000.   Leave the quotation marks in place
    Http.SetProxy HTTPREQUEST_PROXYSETTING_PROXY, “YOURPROXYHERE”, “*.intra”
    Http.Open “GET”, URL, False
    ‘Http.SetCredentials “DomainAccount”,”*********”,HTTPREQUEST_SETCREDENTIALS_FOR_PROXY
    Http.SetAutoLogonPolicy AutoLogonPolicy_Always

””— END This Proxy code

Http.send
‘Get response data As a string

Anonymous

I needed to make a few tweaks for my usage. I wanted to share this version in case anyone else would like to use it. I would like to thank all of the coders who put together this tool.

Download location

http://dl.dropbox.com/u/50906829/Geocoding%20Tool%20v3.2-32bit%20%20%20V%204.17.zip

Description of Changes

Downloaded the original file from
http://www.juiceanalytics.com/writing/excel-geocoding-tool-v2/ link down in the
comments section. The original official release can be found at the link:
http://www.juiceanalytics.com/writing/excel-geocoding-tool-v2/

Removed the Juice logo. They should not be held responsible for the changes made if I broke something or it does not function as originally posted. They are more than welcome to roll the changes I made back into the official version.

All changes were only tested for Yahoo retrievals only. No attempts was made to verify that GEOCODER.US code still works nor was any of the specific code modified.

Tested on both Win7 Pro 64bit with Office 2007 32 bit and WIN XP Pro Office 2007 32 bit. Test were made both behind and without a proxy.

Added code to support Proxy traversal. Added a field in Settings and Instructions
tab to set the Proxy IP address.

Fixed URL build code so it would retrieve based on just zip codes.

Added code to support building a hyperlink to Google Maps for the geocoded location.

Modified GEOCODE All macro to clear the Lat/lon, Precision and hyperlink data.

Added GEOCODE Not Found macro to retry only the rows with “Not Found” messages. This eliminates manually clearing the data to rerun the look up.

Changed the Address, CITY, STATE ZIP headers to Field1, Field2 etc. This better reflects the fact that the address can be in a free flow and not hard coded to a specific
field location.

Commented out the code the launches Google Earth as it could not find the path to the file. To reenable this capability, in Module mKML, un comment the line “‘ Shell (CStr([GoogleEarthExecutableLocation]) & ” ” & sFileName)”

Modified the Geocode Selected Row macro so it clears the LAT data on the selected rows so the macro will run.

Added macro to clear the data entry field

Added a change log.

http://www.facebook.com/lapizz Bill GreatApps

Hey man, I did the changes you mentioned, but it doesn’t work for me and Greece. Stays fixed in the goddamned US.. Is the world really revolving around 1 continent?! We Greeks are more prominent in the news than those guys!

Any help would be much appreciated.

Lindsey Winn

Hello,

I’ve downloaded the tool and enabled Macros, but it’s not giving me Lat/Long (even for the example address in the default). I’ve used this tool for a year (haven’t for a couple months, though) and now it’s not working – am i doing something wrong? Thanks.

Kjrottinghaus

Havent used this in a while…. but used to all the time….. whats wrong ….. keep getting “not found”

Hans-Werner Schlieper

hi there, nice tool
and the modifications by “switchman2210″ are very usefull – thx.one feature-request:is ist possible to add more informations via the api like Country Code / Country / State / Sublocality and so on?? so it would be possible to complete missing datas

Jason curilla

Great tool

Vishal Jadhav

Hi there

I have started using your tool, for fetching distances

eduf

Asking me all the times to enable macros and no way that it works. any idea??? it is new windows 7. need it quite urgent!!! help.

http://pulse.yahoo.com/_TW5ODE565VM7Q6PGMER7VAWR6M Sturdley

Neither Yahoo nor Geocoder.us is working with perfectly good addresses. Suggestions? I’ve registered with both organizations.

http://twitter.com/maxrice Max Rice

You shouldn’t need to register with Yahoo — is it giving you a specific error message?

Packers

Awesome Max! Works great!

http://twitter.com/maxrice Max Rice

I integrated your changes into the forked version on github (https://github.com/maxrice/Excel-Geocoding-Tool), thank you!

Mucrick

Love this tool! Thanks to all who had a hand in creating!

Is there any way to manually adjust the min height and width of the placemark balloon? I have a rather lengthy description for each plot point and would like to change the balloon width.

Thanks fro any assistance!

mucrick

Mitch_Mintz

Hi -
The juice tool worked Great.
Then they gave me Win 7.
Has anyone had problems after upgrading?
Thanks -
Mitch

Pietro

Great tool. Any idea of how make it work on Mac?

http://twitter.com/maxrice Max Rice

Excel on mac doesn’t have access to the regex library so there would need to be a mac specific version of the tool. It’s in-progress, but I don’t know when I’ll be finished with it.

http://twitter.com/maxrice Max Rice

I lied, it’s finished! Download https://github.com/maxrice/Excel-Geocoding-Tool/downloads

Bobrob

Excellent tool Max. Create KML was the reason I used this tool, kindly add it back

http://twitter.com/maxrice Max Rice

Thanks for the suggestion, I’ll look to add this into the next release.

Alanreitsch

I’ve not been successful running Max Rice’s mac version https://github.com/maxrice/Exc… and I think it’s something simple that I’m missing. I need a reverse geocode lookup and have lat, long, name in appropriate columns, but no other data. I’ve created and set my YahooID, have geocoder set to yahoo, and no proxy. Macros are enabled. I select my 425 rows, then run ’Geocode selected rows’. The error is: sub of function not defined in mGeoCode | geocodeRow:

resultarray = Split(resultstr, “,”)

I also get the error on the urlEncode functions call

URLEncode = Join(result, “”)

I’m running on Mac OSX 10.6.8

Any help is greatly appreciated!
Alan

http://twitter.com/maxrice Max Rice

Hey Alan — the tool isn’t designed to do reverse geocoding, but it could certainly be extended to do so. Can you email me to discuss further?

David

Hi, I’m getting very poor results, and they are different depending on which ID I use. I created a new ID and using it some of the examples in your spreadsheet failed.

David

And, obviously, thank you for the work!

http://twitter.com/maxrice Max Rice

Are you seeing a lot of “not found” entries? What sort of locations are you trying to geocode? (cities, zips, specific addresses, etc)

David

Hi Max,
Thanks for responding. I’m using full addresses, and can give you the two Yahoo User IDs I’ve tried if that will help. If you email me I will send it all to you, as well as a couple of addresses that are not mapping correctly.
Thanks!

Jason Spence

any way this can be used to append a Zip4 to the record?

BrianH

I’m trying to use this and I’m wondering how accurate this is supposed to be. I entered my office in Exton, PA and the marker ended up 5 miles away. I entered the address of a customer in Gladwyne, PA and the marker ended up 1 mile away. I enter my house, which is in Newark, DE and it gave me Newark, NJ. I need this to be accurate to the neighborhood level, is this an issue with the programming or the geocoding service?

http://twitter.com/maxrice Max Rice

Sorry, it’s not really designed to do address validation. There’s a few low-cost web services that could do that, but none that are free.

http://twitter.com/maxrice Max Rice

This might be an issue with the 3.4.1 release and should be fixed shortly.

Frank

Where is this excel table being connected to? Google Maps or Yahoo? I am looking at excel-geocoding-tool.xls — version 3.4.2 – fixed url bug and added debug mode

http://twitter.com/maxrice Max Rice

The tool uses Yahoo.

Peter

I’m trying to use this (thanks!) but it just doesn’t work. nothing geocodes. at all. All I get is “not found” with all of the sample data. Whether I use my Yahoo ID name, the entire Yahoo email address, or the API “AppID”, I’m getting the same non-response. What might I be doing wrong?

http://twitter.com/maxrice Max Rice

Hey Peter, if you’ve downloaded the most recent version from Github, there’s a setting on the instructions page called Debug Mode. Turn this to On and shoot me an email with the output.

senior_v

I’m having the same trouble as Peter, and cannot find the “Debug Mode” as described by Max.

http://twitter.com/maxrice Max Rice

Try downloading this version: https://github.com/downloads/maxrice/excel-geocoding-tool/excel-geocoding-tool.xls

KDA

Is the Geocoding Tool v3.1.xls still functioning? I have an Excel file containing 890 street addresses, but am not able to geocode them (even the four examples on the spreadsheet provided by Juice return a “not found” flag). My attempt to manually find 2 addresses on the Geocoder US website returned correct coordinates.

http://www.facebook.com/profile.php?id=890370493 Moataz Sultan

how i can export different Descriptions for same name .
I know i can merge different cells into one row …..but i want to make for each name different description Rows

As Example : Name1 : Des1

Des2
Des3

Ken S

HI, the latest Excel version works great, but is it possible to offer a modified version that returns the Neigbhorhood field on each line? I am a real estate broker and have a nicely formatted list of street addresses (all in New York), all I need is the local neighborhood (not borough or city, but something like “Upper East Side” or “Murray Hill”) returned in tabular/column format in batch. This info is actually returned by Google (not sure about other API’s?) but it’s simply a matter of knowing how to program something to parse that field and display it along side the address on the same line.

I would be eternally grateful if you could help! Only need to process about 2,500 records…shouldn’t be more than that. Need is pretty urgent. Please email me privately if you can help! ken shvetz at yahoo dot com

dee J

Hi I have tried to use the new excel geocoder that Max Price updated and my location results are very poor. Locations that are in Georgia are geocoding to the south pole for example. Has anyone else experienced these issues with the new tool?
example out put: These are all Georgia locations but the lat/longs wrong
-31.898464 26.8648671531 Dunbar St SW
45.88306 -90.73815249550 OXBO RD.
26.80599 104.650829605530 Pattillo Way
49.516674 6.1825605750 Buffington Road
35.434296 126.60091860585 McWilliam Road
32.976764 -115.53580139599 James P Brawley DR NW
Please help I have 80K recs to geocode

dee J

Hi Max,
I attempted to use the tool in this post but the geocoded results that are returning are not correct. I have added a sample of the output can you please assist. I have thousands of records and can’t go through manually changing all of them. I need to complete this project this week and my old juice analytics v3.2 geocoder just stopped working today. That’s when I saw the post for the new tool.
Locations that are in Georgia are geocoding to the south pole for example. example out put: These are all Georgia locations but the lat/longs wrong
-31.898464 26.8648671531 Dunbar St SW
45.88306 -90.73815249550 OXBO RD.
26.80599 104.650829605530 Pattillo Way
49.516674 6.1825605750 Buffington Road
35.434296 126.60091860585 McWilliam Road
32.976764 -115.53580139599 James P Brawley DR NW

Juice Analytics

Our Blog

Fresh Articles

Most Popular

Fresh Comments

Categories

twitter@juiceanalytics