Microsoft Office Prize-giving solution

I am busy automating the prize-giving process at school so that certificates and PowerPoint presentations can be produced easily and consistently for the award ceremony. In years gone by, the process was done manually which resulted in wasted time, errors and inconsistent wording and formatting of printed certificates and PowerPoint presentations. This solution makes use of the integration between Microsoft Office applications, namely: Excel, Word, and PowerPoint.

The broad outline of the solution at this stage is as follows:

1. Create an Excel workbook with logic to handle languages etc. to create awards “statements” based on marks entered.
2. Create a Word document and use Mail Merge to produce a document that has an outline based on styles.
3. Use PowerPoint to create slides based on the Word outline.

Step 1: the Excel Workbook

Each learner’s name and surname, as well as their home language, is captured. Marks are captured in columns per subject and logic is used to compose sentences describing each award the learner earns based on the mark they received. The concatenated results of a combination of IF logic statements and VLOOKUPs, based on the language value, handle the “translation”. The minimum mark required per subject is added to the logic via VLOOKUPs to ensure the mark matches the minimum requirement per subject.

Columns containing the merged information are hidden at run-time using VBA to make the “interface” easier to use and to prevent the calculations from being changed.

Step 2: the Word document

A Mail Merge is created using the Excel workbook created in Step 1. Merge fields for the learner’s name and surname are inserted and styled as Heading 1.

The titles for the different sections of awards (Academics, Sports, Culture, etc) are added and styled as Heading 2.

The award statements are added below each related heading and styled Heading 3.

The style Heading 3 paragraphs can be sorted in Word so that the awards appear in descending order per section.

The result of the Mail Merge is a document containing one page per learner listing the awards for that learner.

Step 3: the PowerPoint presentation

1. Open your final Word document (created in Step 2).
2. Select the View ribbon and activate the Outline view.
3. Save and close the document.
4. Create and open a new PowerPoint presentation.
5. On the Home ribbon, select the New Slide drop-down and select the Slides from Outline command. ^[1]
6. Navigate to and select your Word document and click on the Insert button.
7. Your new slides will be added automatically; one slide per learner.
8. Apply formatting (using a Theme) and add title slides etc as appropriate.

The process is currently done on a per Grade basis as the task is usually delegated to the Grade Head. I am busy working on creating a universal Excel workbook with logic to handle all/any grades.

References:

1. Import a Word outline into PowerPoint (no date) Microsoft Support. Available at: https://support.microsoft.com/en-us/office/create-a-powerpoint-presentation-from-an-outline-f6294909-04e9-4020-b9a8-4587b112692c (Accessed: 28 October 2023).