Sales Macros
One of the programs that I’m happiest with is an Excel add-in that I call Sales Macros. It’s a set of Excel macros that I wrote to automate all sorts of tasks commonly performed by folks in the Sales department.
We have several different online systems that will provide information about our titles - but most of them work on one title at a time. So if a rep or a sales assistant needs, say, updated prices for a spreadsheet full of titles, she would need to look up each title individually. But with Sales Macros, the whole spreadsheet can be filled in with a single operation!
It started off with programs that I needed for myself; I think the first one was a routine to check the validity of ISBNs (the industry’s standard book number) and restore the leading 0s that Excel frequently dropped.
My big breakthrough was when I figured out how to write a SQL Server stored procedure that would accept a list of ISBNs as a parameter, query our data warehouse for various bits of data (title, price, on-sale date, sales . . .), and return a recordset back to Excel for loading into the spreadsheet.
The basic structure is the same, then, for a half-dozen key macros. The user is presented with a dialog box that asks for the spreadsheet column containing the ISBNs, and presents a list of available data fields that the macro will return; the user chooses which fields he wants and which column in the worksheet will hold the data. (The program tries to guess at the appropriate columns, based on the headers, but everything can be changed or corrected easily.)
Once the user makes his selections and clicks the “Run” button, Excel assembles a list of the ISBNs (in the format “Row#,ISBN|Row#,ISBN . . .”) and passes that as a parameter to the appropriate stored procedure. The procedure then breaks apart the ISBN parameter into a temporary table and uses that table to pull the desired data from the data warehouse. Once all the data has been assembled, the stored procedure returns the data in the form of a recordset to the Excel macro.
Once Excel receives the recordset, it loads the worksheet using the row number and the user-selected column assignments. In most cases, the whole process takes a minute or two (up to 10 minutes or more if the spreadsheet has thousands of ISBNs) - and that’s obviously a huge time savings over looking up the data on a title-by-title basis!