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

Friday, April 17, 2015

Automating Circulation Data Records

EXECUTIVE SUMMARY

I have been a student worker in the Harold B. Lee Library’s
Office of Digital Content Management for close to three years. Among other
things, my office is in charge of generating and recording the library’s
circulation reports. Circulation data is collected as follows: At the beginning
of each month, a text file is generated using a program called WorkFlows,
which, among other things, stores and records information about library
circulation. This text file shows 1) the number of items checked out, 2) the number
of individual patrons who check out items, and 3) the ratio of items checked
out per patron for each hour of each day that the library’s circulation desks
are in operation during the last full month. The output from these text files
is then read into an Excel spreadsheet, where it can be used for analysis. Up
until recently, data from these text files was entered manually into the
spreadsheet. For my project, I made the decision to write a program which would
automate this process, saving on average half an hour to 45 minutes of
worktime.

The program that I have written includes a File Dialog,
which allows the user updating the spreadsheet to open the text file containing
the data for the month that they wish to import into the spreadsheet. The
program then uses arrays to find and paste in the data, as well as the date
heading for the data. After the program has run, the user is asked whether they
would like to import more text files containing circulation into the
spreadsheet, continuing in a loop if the user selects the “Yes” option and
exiting the program if the user selects the “No” option.