Exercise 6: Document Assembly with Word Content Controls and VBA

Thus far, you have worked exclusively with Microsoft Excel. In this Exercise, you will learn how to combine Word Content Controls with VBA for robust data-driven document assembly. Many businesses use Word templates for everything from one-off memo’s to extensive, highly customized and polished research reports. Many business templates often require their users to populate specific portions of the document with external data. In the absence of anything else, it is common, though not efficient, to just enter the data manually. Not only is this terribly inefficient, it is often error prone. A much better approach is to combine Word content controls with a little bit of VBA so that a user can simply select the data used to generate the document.

In this Exercise, you will add content controls to a letter used by HR to notify job applicants of a hiring decision. Then, using VBA, you will populate the values of the content controls based on a user’s selection.

Task 1 – Add Content Controls to the Document

1. Open the document HR Applicant Letter.docm found at %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter Files. Several things have already been done to this document:
   1. A custom button labeled Select Applicant on the Insert tab (it is in a custom group labeled Contoso).
   2. The document also contains a user form with the caption Generate Correspondence. Provided the database this form uses is present, it should display a list of applicants along with the type of correspondence to generate. Click on the custom button in the ribbon labeled Select Applicant to view this form. Note that it does not do anything else yet – that is what you’ll do.

   3. Open the VBE by pressing CTRL + F11.

      Figure 1

      Project Explorer

   4. Double-click on ThisDocument in the HR Applicant Letter project. Observe that a handful of code is present. You will be completing this code to implement the desired functionality.
   5. Double-click on the CONSTANTS module in the HR Applicant Letter project. This module contains the connection string used to connect to the Job Applicants database (Job Applicants.accdb) that should be located at %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database. If you move this database or it is in a different location, modify the path located in the CONN_STRING constant.
   6. The CONSTANTS module also contains a constant named BOILERPLATE. This is the path to the document containing generic boilerplate text for use in generating letters. If you move this document or it is in a different location, modify the path of this constant. By default the value of this %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter Files\HR Boilerplate Text.docx
   7. Double-click on the form named frmApplicants in the HR Applicant Letter project. This form is complete – you should not need to modify this form.
   8. Switch back over to Word to view the HR Applicant Letter document
2. If the Developer tab is not visible in the ribbon, right-click on the ribbon and choose Customize the Ribbon.
   1. Place a check next to the Developer item on the right side of the window
   2. Click OK
3. Add a content control for the applicant’s name
   1. Select the text <TO DO: Name>
   2. Click on the Developer tab on the ribbon

   3. In the Controls group, click the small button with the screen-tip Rich Text Content Control. Word will place a rich text content control in the document for the selected range

      Figure 2

      Add Content Controls

   4. Click Properties
   5. Set the Title to Applicant

   6. Check Content control cannot be deleted. The ability to “lock” a content control in this manner is a chief advantage of using content controls as opposed to performing a mail merge or using bookmarks. Notice that you can also prevent a content control from being edited.

      Figure 3

      Content Control Properties

   7. Click OK
4. Add a content control for the applicant’s address
   1. Select the text <TO DO: Address>
   2. Insert a rich text content control
   3. Click Properties
   4. Set the Title to Address
   5. Check Content control cannot be deleted
   6. Click OK
5. Repeat step 4 for the following items:
   1. <TO DO: City> (set the title to City)
   2. <TO DO: State> (set the title to State)
   3. <TO DO: Zip> (set the title to Zip)
   4. <TO DO: Salutation> (set the title to Salutation)
   5. <TO DO: Body> (set the title to Body)
6. Press CTRL + S to save your work

Task 2 – Add VBA to populate the Content Controls

In this task, you will add some VBA code to retrieve data from the Job Applicant database to populate the content controls.

1. Press ALT + F11 to switch to display the VBE
2. One key procedure that you need to write is a way to set the text of a content control given a content control’s title. Double-click on ThisDocument in the HR Applicant Letter project to view the code associated with ThisDocument.

3. Enter the following code at the bottom of this module (below the ResetContentControls procedure). This code loops through the content controls in the document looking for content controls with a title equal to the sCCTitle argument. When a match is found, the procedure sets the text of the content control equal to the value supplied by the sValue argument.

   VBA

   Private Sub SetCCValue(sCCTitle As String, sValue As String) Dim cc As ContentControl For Each cc In ThisDocument.ContentControls If cc.Title = sCCTitle Then cc.Range.Text = sValue End If Next End Sub

4. To test this code, complete the procedure named ResetContentControls. Enter the following code beneath the TO DO comment in this procedure

   VBA

   SetCCValue "Applicant", "<applicant>" SetCCValue "Address", "<address>" SetCCValue "City", "<city>" SetCCValue "State", "<state>" SetCCValue "Zip", "<zip>" SetCCValue "PositionTitle", "<positiontitle>" SetCCValue "Salutation", "<salutation>" SetCCValue "Body", "<body>"

5. Switch back to Word
6. Press Alt + F8 to display a list of runnable macros in the document

7. Highlight the macro named ResetContentControls and click Run. Observe that Word updates the values in the content controls. If you run into problems, be sure to double-check the spelling of each of the content controls’ title in the document and in the ResetContentControls procedure.

   Figure 4

   ResetContentControls Macro Result

8. Press Alt + F11 to return to the VBE

9. The procedure LoadApplicant, immediately below the SelectApplicant procedure, is responsible for retrieving the details associated with an applicant from the Job Applicants database. This database is located at %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database\ Job Applicants.accdb. After you retrieve the record from the database, you can use the SetCCValue procedure completed in step 3 to update the appropriate content controls. To do this, enter the following code beneath the TO DO comment in the LoadApplicant procedure:

   VBA

   SetCCValue "Applicant", rst.Fields("ContactName").Value SetCCValue "Address", rst.Fields("Address").Value SetCCValue "City", rst.Fields("City").Value SetCCValue "State", rst.Fields("StateProvince").Value SetCCValue "Zip", rst.Fields("ZipPostal").Value SetCCValue "PositionTitle", rst.Fields("Position Title").Value SetCCValue "Salutation", rst.Fields("ContactName").Value

10. Review the procedure named LoadBody immediately below the LoadApplicant procedure. This procedure loads the body content control with the appropriate text depending on what type of correspondence is required. For this exercise, the boilerplate text is stored in a separate document called HR Boilerplate Text.docx. LoadBody opens this document “behind-the-scenes”, retrieves the content from the appropriate content control, and then copies the content into the body content control. LoadBody requires one parameter, bOffer, that is used to determine if the procedure loads an offer letter or a rejection letter.

11. The final step is to hook up the LoadBody and LoadApplicant procedures to the SelectApplicant procedure. SelectApplicant is the procedure called when you click the custom SelectApplicant button on the ribbon. All you need to do to finish this is call LoadBody and LoadApplicant, passing in the applicant ID selected in the user form. Enter the following lines of code below the TO DO comment in the SelectApplicant procedure:

    VBA

    LoadBody frm.optOffer LoadApplicant nID

Exercise 6 Verification

Perform the following steps to verify your work.

1. Press Ctrl + S to save your work
2. In the VBE, select DebugCompile Project. If everything is ok, the code will compile almost instantly without any warnings or dialog boxes.
3. Switch back to Word
4. Click on the Insert tab

5. Click on the button labeled Select Applicant

   Figure 5

   Applicant Selection

6. Select the first applicant in the list, choose Offer Letter and click OK. Observe that the applicant’s data appears in the document along with text for an offer letter.

   Figure 6

   Applicant Offer Letter

7. Click on Select Applicant again. This time choose the same applicant, except choose the Rejection Letter option.

   Figure 7

   Applicant Rejection Letter

8. Notice in the first sentence of the rejection letter that it references the correct job title. This job title is not hard-coded in the boilerplate document. The boilerplate document contains nested content controls that VBA populates at runtime after copying them into the document.
9. Verify that when you close the form by canceling, nothing in the document is changed. Click Select Applicant on the ribbon
10. Select the 2nd Applicant in the list
11. Close the Select Applicant form by clicking on the Close button (the X in the upper right corner). Observe that nothing in the document changes.