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

Thursday, December 6, 2012

Gather, Edit, and Input Data

Executive Summary

I work as an Accounts Payable clerk in the BYU Bookstore. There are many vendors with which the bookstore is in a credit balance. The AP department keeps track of all the monthly credit balances throughout the year. At the beginning of each month, one of the tasks is to download the end of month data from the bookstore’s accounting software. This data is then inputted into a summary file that shows the vendors and the credit balances throughout the year. This can be a challenge because first, the vendors with a zero balance during the month do not have a balance in the downloaded data, yet they exist in the year summary file. Second, some credit balances are new; vendors which previously did not have a credit balance now do. However, they do not exist in the year summary file. It is a tedious process to find which vendors exist and add rows of data to the file in the correct location. Previously, it would take my supervisor many valuable hours to input this data. My project saves her this time and helps her to avoid this menial task.

My project performs the following key steps:
·         * Creates a connection to the downloaded text file and imports the data to a new sheet in the file.
·         * Formats the data for improved readability and enhanced transferability to the summary sheet.
·         * Transfers the monthly data to the correct month location in the summary file based on the modified date of the downloaded text file.
·         * Adds rows of data for the new vendors as needed.
·         * Moves and saves the month data to the same directory as the year summary file.

At the end of the year, a new file will need to be created for 2013. When the end of month file is downloaded next year and the program is run, it will generate a new file, clear out the old balances, create a new folder for the next year, and save the file in that new year folder. Thus, this program will continue for as long as the bookstore stands.

No comments:

Post a Comment

Blog Archive