Plymouth Design Group

Fairhaven Health asked us to streamline the daily processing of orders by automating tasks in their Excel spreadsheet.
Their requests were a straightforward project for Windows Excel, but a much more complex solution was required to work in their Macintosh Excel 2004 environment.

Their existing procedure involved several steps:

(1) importing an order file from each of several sources into an Excel 2004 spreadsheet
(2) sorting to find duplicate addresses
(3) generating a new worksheet with orders combined from all sources
(4) constructing new columns such as the total cost and a barcode
(5) saving the resulting spreadsheet as a source for mail merges in Word 2004 to generate invoices and labels

The procedure was partially automated — they already had a template spreadsheet with formulas for the new columns, along with some VBA macros. Our task was to refine the process and make it more robust.

We first developed a VBA macro to automate the entire process. Under the new scheme, we store the order
files with fixed names, in a folder based on the date. We then run this new macro which does all the necessary importing, sorting, combining and saving.

Here are some specific changes and improvements that we implemented:

(1) Data Validation: The existing spreadsheet imported zip codes as numbers, with the result that leading zeroes were
dropped. The new macro explicitly imports the zip code column as text.

(2) Code Optimization: The existing spreadsheet had formulas to build the new columns, presuming a certain maximum
number of rows. The new macro builds the needed columns for just as many rows as are actually present.

(3) Error Trapping: Some orders are paid with PayPal and have authorization but no transaction ID. If the client didn’t catch this omission, he doesn’t get paid for that order. The new macro counts the PayPal authorizations and transaction IDs, with the transaction ID count highlighted in red if it doesn’t match the authorization count.

(4) Error Trapping: For international orders, the country name is found by looking up the country code in a table. We added an error trap to the macro in case the country code is not found.

(5) The existing procedure involved saving the worksheet as tab-delimited text to generate a file to be uploaded to mark orders as shipped. This worked fine except that the last line was missing the end-of-line character (CR). He had to edit the file manually and insert a CR at the end, or else the last line wouldn’t be processed. The new macro solves this problem by building the file directly with VBA file I/O.