These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Monday, December 14, 2009

Inventory Tracker and Exporter

Executive Summary

This project comes from a post on It is a business problem that someone posted dealing with shipping and inventory systems. The system is required to query tracking data against the USPS site and collect shipping information on the tracked item. If the item has been delivered, then an inventory chart is updated with quantities of the items in the order.

The input needed from the user is a worksheet with the order details consisting of sizes, colors, and quantities of the items ordered. Then the worksheet is named after the tracking number associated with the order. The tracking number must almost be added to the list of tracking numbers in order for the process to work.

When the procedures are run (via a button) the list of tracking numbers are processed, querying the USPS site one by one, and collecting the order’s shipping class, status, date shipped, and date delivered, if applicable. If the package had been delivered then the order details are added to the worksheet of the running inventory for the business.

Once all the delivered quantities have been added to the running total the inventory worksheet is sorted by color alphabetically and has all zero-quantity rows collapsed and the worksheet is exported as a GIF picture for use in posting online.

Excel File

Project Write-up

No comments:

Post a Comment

Blog Archive