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

Wednesday, April 6, 2011

Media Library Tracking

My project allows a user to manage a media library through an Excel spreadsheet. It is a database of sorts for a DVD and/or CD collection. It simplifies management by allowing input via barcode scanning. Every DVD and CD sold in the United States includes a barcode on the back. That barcode is specifically tied to that product.

CueCat barcode scanners are the required method of input. As these scanners output encrypted barcodes, a library is referenced that allows decryption. This library is referenced by the first macro.

The first macro is triggered by scanning a barcode from anywhere within the spreadsheet. A user form opens that allows the user to specify whether the item is a CD or DVD. After selecting, the program scrapes the item’s details from www.upcdatabase.com. The details are then pasted into either the CDs or DVDs worksheet.

On each worksheet the records are organized in tables, which allows for simple sorting. On each page there is also a search button. This button starts a separate macro for both the DVD and CD worksheets. These macros launch user forms with fields for searching the respective table. The appropriate record is highlighted by the search function.

Writeup

Workbook

-Jared McGann

Blog Archive