Ward Directory Automator

Spencer Johnson

VBA Final Project

Ward Directory Automator

One of the things that has always bugged me about the start of a new semester is how long it takes for the new ward leadership to finish compiling the ward directory. In some of the wards that I have been in, that process is relatively streamlined and the ward directory is completed within two to three weeks. However, some wards, take months to get the directory out and by the time it is available to everyone, it becomes obsolete because a new semester has started and people have moved in and out of the ward. For example, my current ward barely finished the ward directory a month ago after we had lived in the apartment complex for over 2 months. The semester was almost over before we finally got a “rough draft” of the ward directory that includes names and information only, but no pictures.

In today’s age of technology, I wanted to see if there was a way to automate this process to produce a high-quality, error-free ward directory within a few days of finishing all picture taking and information gathering. I also wanted to see if there is a way to automatically pull the data that already exists from the lds.org website and programatically match up that data with the pictures based on picture filename. That way, all the ward clerk would have to do would be quickly rename the pictures in Adobe Bridge or some other program and push a button to automatically generate the bulk of the ward directory.

            The project proved to be much harder than I had original anticipated (see conceptual difficulties section), however, in the end I wrote a program that was able to download ward directory information from lds.org, import that workbook into excel, and pull photos from a folder and match them with the information in the ward directory.

If I had more time, I would have liked to spend another 20-30 hours working on adding features to the ward directory such as options for the number of pictures per row or column, a method to clean up the addresses more, and a few fun ideas I had for dating prospects. In total, I would estimate that I spent about 40-50 hours working on this project.