One obvious approach is to produce and maintain a set of MS Word and Excel documents to keep track of everything and to produce the needed documents. However, much of the information appears in multiple documents, making it time consuming and error prone to keep all the documents correct and up to date.
I decided to apply the "DRY" (don't repeat yourself) principle from software engineering. In this case the goal is to maintain all data regarding programs, students, schedules, etc. in one place and process the source information to automatically produce the required documents. At first, I wanted to use Ruby for the automation since we're using it for one of our projects at AMD. However, I realized that no one at Oak Meadow would be able to maintain Ruby code so I decided to try Excel VBA macros instead.
I had no prior experience with VBA, but after just a few hours of internet searching and experimenting, I was able to produce an example document in .pdf format. From there, I've been making incremental changes - re-organizing the data to eliminate redundancy and improve maintainability. I've also been improving the code and creating additional reports in different formats. I'm still a novice with VBA, but it has some nice properties:
- Easy to learn on your own (given some programming background)
- Quick to get something functional, even if it's not optimal
- Well suited for processing and producing information in typical business formats - MS Word, Excel, PowerPoint, and .pdf
- Mature language with an extensive developer community - simple internet searches quickly produce relevant code snippets and examples.
