I once got one request by the CRM unit to assist in devising a solution that helps them in performing their routine task of checking the quality of data in one business system. Unfortunately, the business application was not sufficient in handling that requirement, so they had to do some workarounds by importing the data into Excel and manually performing data cleansing with the help of Excel formulas.
After discussing the requirements with them, I got to know that their pain is not Excel by its own. On the contrary, they love Excel actually. They just need it to be “more understanding” so it saves them of doing same steps over and over again (remove duplicates, check @ sign in Email field, check phone formats,…), and then save this back to the business system, automatically!
I came to understand that the dream of every “information worker” is not to abandon Excel and migrate to some other application, but rather to leverage Excel and fantasize about plugging more automation into it. Information workers won’t and cannot abandon Excel (or to make it more general and brand-free let us say Spreadsheet processing program), simply because it is the most essential productivity tool for processing numbers and data in general.
So I explored with one fun solution: programming Excel! I thought that it will be super cool to our users to have an “upgraded” Excel sheet with buttons that can automate the tasks of loading data from the business system, performing data quality checks and highlighting the cells that fail the test, allowing the user to apply quality fixing rules, and finally to save back to the business system!
Well, this nice dream was possible by using Microsoft’s paradigm of Office Business Applications (OBA). I used Visual Studio Tools for Office (VSTO) available in Visual Studio and developed the Excel template. Below is a screenshot. It was fun programming Excel and I have to say that it is brilliant idea by Microsoft to introduce such a capability. OBAs are defined officially as “a new breed of applications that combine the use of the Office system with information and processes defined in line-of-business systems (LOBs)”.