VBA to VSTO: A resolution for the new year
Recently a friend of mine asked me for help with a data management issue. Same story, different day - he had numerous Excel files (about 8-10 he tells me) with sales prospects that he needed to combine and clean. This friend is former professional water skier and national champion with a warm and sunny Florida residence on a private lake. As an avid tournament waterskier and resident of the freezing tundra that is Minnesota, I thought I'd offer a trade - I'll clean up your data (actually I volunteered my wife) and you provide some coaching in the warm waters of Florida. "Done!" he replies.
Then I get them. The files. 25 of them. 1000's of records (~30,000). No common structure. Gulp. I quickly realized that either my wife is going to be busy for a very, very long time or I better intervene and build her some tools. So, did I use all of the goodness of VSTO for this or VBA?
VBA in a heartbeat.
As much as I like VSTO and developing in Visual Studio rather than the VBA IDE, I find myself still using VBA for a lot of "ad-hoc" development tasks when time is of the essence. The reason: a decade old library of tried and true VBA code that I can draw on to accomplish tasks rapidly. Find the code (or template) I need, add the bits specific to the task at hand, connect all the dots, done. My VSTO library pales by comparison.
One of the things I'd like to accomplish this year is migrate all my "old faithful" VBA code to .NET. As I progress on this journey, I hope to fulfill another resolution - share more of what I know and learn through regular blogging and additions to Office Zealot.
Back to my immediate problem for a minute. I decided the first major task was to somehow get all of this into a database. The problem was that I didn't even know what I had - none of the files shared a layout much less common fields. One of the tools in my VBA toolkit is an Excel batch processing utility that I use whenever I need to run some VBA process against a set of workbooks. I've used and refined this for at least seven years. It's really not a lot of code, but it is robust, works exactly like I want it, and is simple to use. Just add the specific processing code and you're done. Anyway, I added grabbed this utility and added workbook processing code to collect the field information (23 of the files at least had the fields in one of the first three rows) and put it all on a common worksheet so I could try and make sense of it all.
Based on the collection of fields, I created two tables. One table contained all of the common fields. A second table was for the "miscellaneous" data that he wanted to keep "just in case". This table was merely a collection of name/value records.
Next, I used the batch processing utility to insert a data mapping row in each workbook/worksheet that needed to be processed. Finally, I used the batch processing utility again to cycle through all the workbooks and load the data.
I bring all of this up to point out the usefulness of this batch processing utility. I use this all the time to do things in minutes that would otherwise take hours or days. My journey begins with this utility...