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.
Tags: General, Scripting is Cool, Technology // 1 Comment »
