We often have projects that put a smile on our face when we help a client save massive amounts of time and money.

We have a client with a growing number of e-commerce web sites. Each of these e-commerce sites has a specific theme and carries products related to that theme; however there are also many products that are shared across the entire set of sites. The main problem was that this client was just spending too much time manually managing the hundreds of products across each site using the administration interface. They desperately needed a system that allowed them to manage all products for all web sites from one place, and then push this information out to each site in an automated fashion.

When they brought us in we discussed various options and potential solutions. One important aspect of the solution for our client was that they did not want a large database backed system to manage their information. They wanted something simple and portable, which can easily be moved from machine to machine if necessary. They also wanted this to interface directly into QuickBooks, which is what they used for accounting. We decided on an excel spreadsheet based solution which would contain all information for all products across all web sites.

The information in the spreadsheet included basic product information, such as the product name, short description, detailed description, image URLs, price, sale price, SKU and target websites. It also included more complex information such as product categories, product options, tax code information, gallery thumbnails and detailed images. The size and complexity of the spreadsheet grew as more and more of their e-commerce business requirements were met. As the complexity of the spreadsheet grew, it became more difficult to manage the product information contained therein. To solve this problem we used Excel's built in VBA to provide a single manageable product view that allowed a user to navigate the hierarchy of products and product categories, and enter and maintain their product information.

We also used VBA to automate the publishing process and generate export data sets according to the requirements of each e-commerce site. This allowed each site to be bulk loaded from this single excel spreadsheet and saved our client tremendous amounts of time. What was a full time job is now reduced to a few hours per week. They are very happy with their solution.