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

Tuesday, April 14, 2015

Generating Grant Agreements with VBA

Executive Summary


Description of Business

I work for, a nonprofit foundation. The Foundation distributes about 150 grants each year to help K-8 schools implement The Leader In Me program. Each grant contains a combination of classroom materials, student activity guides, and training days. As such, each grant is unique and results in a different dollar amount.

In the past three years, has created and generated these grants entirely through its Salesforce account. Schools apply for a grant through an online application and captures and stores this information in its Salesforce account. This has worked relatively well in recent years. However, this year, the Foundation has added some complex logic to determine a school’s grant package. We submitted our customization requests to a Salesforce developer and the bid came in at $26,000! I told my boss that I thought we might be able to do the entire process with VBA in Excel.

Overview of System Built

This project has four main objectives:
1.     Create an Excel spreadsheet to calculate a grant package for a given school.
2.     Import the raw data from Salesforce, clean the data, and organize it into the spreadsheet.
3.     Generate grant exhibits from the spreadsheet that include the products, prices, and total amounts for the grant.
4.     Merge Excel grant exhibits with the Word grant agreement and save it as a PDF file.

A major challenge with this project was that I had to make the grant agreement PDFs look clean and professional. This required me to install Adobe Acrobat Pro in order to manipulate PDFs through VBA.  A prerequisite to running this macro successfully is that a user must have Adobe Acrobat Pro installed on their machine.  The final output of this project (the grant PDF) is attached in Appendix A.

*NOTE: The uploaded workbook does not contain sensitive data. Actual Foundation school names have been replaced with a random list of Utah school names found online. 


No comments:

Post a Comment

Blog Archive