Access Links, the Add-In included (but not enabled by default) with Excel, can save time when exporting data to MS Access. However, for frequent GeoAccess reporting, I was craving some more flexibility, so I started scripting some alternative solutions (please note: I have only tested this with Excel 2000). Three of them are packaged in this add-in, GeoAccessAssistant.xla (click here to download). Once installed, it adds a GeoAccess menu bar with the following three buttons:

Export Zip Selection to MS Access

Select a range of zip codes. (Please note: Zips must be in a single column. At a later date, I might enhance this to accommodate other types of ranges, i.e., horizontal, non-contiguous, etc., but for now, this should still be useful in the majority of scenarios.) Click Export Zip Selection to MS Access. This script will trim the contents of each cell to remove surrounding white space, add leading zeroes wherever necessary, shift the selection down one row, add zip as the value in the empty cell at the top of the selection, expand the selection one row at the bottom to catch the stray extra cell, create a new, blank Access database with the same name as the active workbook, then transfer the selected range to the new database into a new table named census. The script goes one more step by running this JetSQL Data Definition Query:

alter table census alter column zip text(5)

It seems that GeoNetworks can return inaccurate results if the zip field is not a text field five characters in length. I found this out the hard way. Three hours and two tech support calls later, I decided to avoid that particular trap going forward.

Export Zip Region to MS Access

This goes through essentially the same steps as the above, except you only need to have the cursor in a single column of zips. When you press Export Zip Region to MS Access, it first selects the current region, meaning it will expand to select contiguous, neighboring cells with contents. Try to make sure those cells happen to be in a single column.

Export Zips and Counts (Populate)

This one does not require anything to be selected initially. Clicking Export Zips and Counts (Populate) pulls up a single-button UserForm prompting the user to Select Zip Range, then Click Here. Next, it prompts to Select Counts, then Click Here. Both selections must contain the same number of cells, i.e., one zip code per count, otherwise a message box will pop up warning Cell counts not matching. Try again? The user can either try again or cancel the operation. (I have not torture-tested this, but it should work with all types of selections including non-contiguous areas, as long as both zips and counts have the same number of cells. However, I can’t imagine encountering a scenario such as that. Then again, one never knows…) If the cell counts match, it will prompt Click to Export Data to Access. It will then create a new, blank Access database with the same name as the active workbook, create a new table named census with a five-character text field named zip, then iterate through each zip in the originally selected range, duplicating records according to each corresponding count, essentially replicating the GeoNetworks Data-Populate feature.

I haven’t provided further instructions, as this assumes some Intermediate knowledge of Excel which should include managing Add-Ins. Details are freely available in Excel’s online help.

If anyone either finds this useful or feels that I must be stopped, please feel free to share opinions, experiences, rants, and raves. Any suggestions are always more than welcome, though I cannot guarantee that I will act on them other than to respond to your comments. I assure you, I’m driven by an uncontrollable desire to automate away repetitive tasks wherever possible, not including, of course, those repetitive tasks which might actually prove beneficial, such as breathing.

I’ve finally succumbed to peer pressure and threw up a MySpace account here. For someone who prides himself on being technically savvy, it seems a little late in the game. Frankly, I’ve been a little creeped out by the whole MySpace phenomenon, with all the sexual predators floating around, not to mention the hordes of lonely, desperate, socially inept types who can’t pry themselves away from their computers long enough to skulk out of their dingy basements and meet people the old fashioned way, face to face. I’m not advocating alcoholism here, mind you, but there’s a lot to be said for the occasional visit to the local watering hole for a shot and a game of pool. Sometimes it’s just that kind of night that could lead to marriage and children, if that’s your thing. Of course, it’s much easier to pretend you’re a hundred pounds lighter and thirty years younger, soliciting twelve year olds because you’re just so in tune with their needs. I know plenty of people who are convinced that computers are evil. Apparently it’s the Internet itself that ends marriages, rather than the people compelled to abuse the greatest collective source of information the world has ever known. So maybe I’ve been guilty of labelling MySpace unfairly. After all, guns don’t kill people. People do. I will keep this website and blog going, selectively throwing up a MySpace blog entry, maybe sharing a photo here and a video there. With a little luck, I’ll avoid getting sucked into the dark abyss.