Specify the Contents of the Report with a Click of a Button

Next, you want to create reports that display the teams for a specific conference or all of the teams in both conferences. By adding Visual Basic Code to your document, you can quickly create these reports with the click of a button.
  1. In the Word document, select the location for the button.
  2. In the Ribbon, click the Developer tab.
  3. In the Controls group, click Legacy icon on the Developer tab, and under the ActiveX Controls heading, click the icon for the command button.
    Command button under the ActiveX Controls heading
    The button appears at the location that you selected.
    Location of CommandButton1 in the Word document
  4. To change the text on this button, select Properties on the Developer tab. The Properties dialog box appears.
  5. In the Caption field, type All.
    Specify All as the caption for the first button
  6. Select the Font field. Click Ellipsis button. The Font dialog box appears. Specify the font size for the button text and click OK. In this example, the font size is 7 points.
    Close the Properties dialog box. In the document, the button is now labeled “All” and appears in the specified font.
    All button in the Word document
  7. Create the buttons for the eastern and western conferences.
    1. Select the location for the button for the eastern conference and repeat steps 2 through 5. The caption for this button is Eastern Conference.
    2. Select the location for the button for the western conference and repeat steps 2 through 5. The caption for this button is Western Conference.
    When you are finished, you should have three buttons in your document, as shown in the following display:
    All, Eastern Conference, and Western Conference buttons in the Word document
  8. Double-click All to open the Visual Basic Editor. The code for this button is created for you.
    Initial code All button
    Next, it is important that you add a reference to the SAS Add-In 6.1 for Microsoft Office in your Visual Basic code.
  9. In the Visual Basic Editor, select Toolsthen selectReferences. The References - Project dialog box appears.
  10. Select the SAS Add-In 6.1 for Microsoft Office check box.
    The SAS add-in check box in the References dialog box
    Click OK.
  11. To access the automation interface for the SAS Add-In for Microsoft Office, add the following lines of code: 
    Dim sas As SASWordAddIn
Set sas = Application.COMAddIns.Item("SAS.WordAddIn").Object
    Adding a reference to the SAS add-in for the All button
  12. To run the Sort Data and List Data tasks when you click the All button, add the following lines of code: 
    sas.Refresh ("Sort_Data") 'output: SASApp:WORK.SOCCERCLUB
sas.Refresh ("List_Data")
    The first line of code runs the Sort Data task so that the generated Work.SoccerClub data set will include all of the soccer teams. Now, you need the object name (Sort_Data) for this task. For more information, see Determine the Object Name for the Sort Data Task.
    The second line of code runs the List Data task to create a report that includes all of the teams.
    Visual Basic code for the All button
  13. In the Visual Basic Editor, select CommandButton2 from the drop-down menu.
    Select CommandButton2 from the drop-down menu
    The code for this button is added to the Visual Basic editor.
    Initial Code for the Eastern Conference Button
  14. Add the following code for the second command button, which is the button labeled Eastern Conference: 
    Dim sas As SASWordAddIn
Set sas = Application.COMAddIns.Item("SAS.WordAddIn").Object

sas.Refresh ("Sort_Data_2") 'output: SASApp:WORK.SOCCERCLUB
sas.Refresh ("List_Data")
    The first line of code runs the Sort Data task so that the generated WORK.SOCCERCLUB data set contains the data only for teams in the Eastern Conference. Now, you need the object name (Sort_Data_2) for the task. For more information, see Determine the Object Name for the Second Sort Data Task.
    The second line of code runs the List Data task to create a report that ranks only the teams in the Eastern Conference.
    Visual Basic code for the All and Eastern Conference buttons
  15. In the Visual Basic Editor, select CommandButton3 from the drop-down menu. The code for this button is added to the Visual Basic editor.
  16. Add the following code for the third command button, which is the button labeled Western Conference: 
    Dim sas As SASWordAddIn
Set sas = Application.COMAddIns.Item("SAS.WordAddIn").Object

sas.Refresh ("Sort_Data_3") 'output: SASApp:WORK.SOCCERCLUB
sas.Refresh ("List_Data")
    The first line of code runs the Sort Data task so that the generated WORK.SOCCERCLUB data set contains the data only for teams in the Western Conference. Now, you need the object name (Sort_Data_3) for the task. For more information, see Create a Temporary Data Set for the Western Conference.
    The second line of code runs the List Data task to create a report that ranks only the teams in the Western Conference.
    Visual Basic Code for the All, Eastern Conference, and Western Conference buttons
  17. Save your changes and close the Visual Basic Editor.
    Note: You must save the Word document as a macro-enabled document (.docm).
  18. On the Developer tab, deselect Design Mode.
  19. In the document, click All. Your results should appear similar to the following display:
    Report listing all of the teams in the soccer league
    When you click Eastern Conference, your results should appear similar to the following display:
    Report listing all of the teams in the Eastern Conference
    When you click Western Conference, your results should appear similar to the following display:
    Report listing all of the teams in the Western Conference
Copyright © SAS Institute Inc. All rights reserved.