Create an Application-Level Add-In to Automate Common Office Tasks

Article
11/11/2009

MCW Technologies, LLC

Since its inception, Visual Studio Tools for Office has provided developers with a powerful set of tools for building solutions for the Microsoft Office System. There has never been a better time to build solutions that combine familiar .NET coding techniques with the most familiar user interface around.

You can also use VSTO to build application-level add-ins that automate common tasks. This has traditionally been one of the strengths of Visual Basic for Applications (VBA). VSTO provides a more secure and powerful model for automating Office applications using either Visual Basic or C#.

For example, suppose you want to print a PowerPoint presentation. You want to print black and white handouts with three slides per page. To do this, you need to take the following steps:

Click the Office Button.
Click Print to display the Print dialog box.
Select Handouts in the Print what drop-down list.
Select Pure Black and White in the Color/grayscale drop-down list.
Select 6 in the Slides per page drop-down list.
Check Frame slides.
Click OK.

As an alternative, you can create a PowerPoint add-in that adds buttons to the Ribbon to programmatically accomplish tasks like this. In this tutorial, you will create the customization shown in Figure 1. You will add a group to the Ribbon that contains four buttons. The first two buttons print the current presentation as black and white handouts with three or 6 framed slides per page. The last two buttons save the presentation as a PPT file using the PowerPoint 97 – 2003 format or as a PPTX file using the PowerPoint 2007 format.

Figure 1. These Ribbon buttons perform common tasks in PowerPoint.

Using these buttons, each of these tasks can now be perfomed with no more than two mouse clicks, one to bring forward the Ribbon tab and one to select the appropriate button.

Create a PowerPoint Add-in

To get started, in Visual Studio 2008 select File | New | Project to display the New Project dialog box. In the list of project types, expand the Office node. Select Version2007, displaying the list of templates shown in Figure 2.

2. Visual Studio 2008 provides these Office 2007 templates.

If you want to write code that is available when a particular document is open, you can select the Excel Workbook or Template or Word Document or Template project templates. If you want to create a customization that is always available, you can select the Excel, InfoPath, Outlook, PowerPoint, Project, Visual or Word Add-in project templates. For this tutorial, select the PowerPoint 2007 Add-in template. Name your project PowerPointCommonTasksAddIn. Click OK to create the project.

You have two options for customizing the Ribbon in Office applications. You can write Ribbon XML or you can use the Ribbon Designer. You’ll take the second approach here. Select Project | Add New Item. In the Add New Item dialog box, select the Ribbon (Visual Designer) item template and click Add. Visual Studio opens the Ribbon Designer (see Figure 3).

Figure 3. Use the Ribbon Designer to customize the Ribbon.

Select Group1 and change the Label property to Common Tasks. From the Toolbox, drag four Button controls into the Common Tasks group. Name the first button print3Button. Set the Label property to Print 3 and the SuperTip property to Print handouts with 3 slides per page.

You have two choices for button size: regular or large. You will use large buttons here, so set the ControlSize property to RibbonControlSizeLarge. You also have the option of including an image with your button in addition to the label. In general, Ribbon buttons have an image, so you will typically specify one. To use a custom image, click the ellipsis associated with the Image property. This displays the Select Resource dialog box. You can then select a local or a project image resource. This is the same technique you would use to add an image to the button on a Windows Form.

You can also use a built-in Office images. To do that, set the OfficeImageId property to the id of the image you want. Here, set the property for the button to FilePrintQuick.

How do you know what id to use? One easy way to find the id is to right-click on the Ribbon or the Quick Access Toolbar and select Customize Quick Access Toolbar. This displays the PowerPoint Options dialog box. Find the command that includes the icon you want and display the command’s tooltip. You will find the id in parentheses (see Figure 4).

Figure 4. You can use the Options dialog to find the ids for built-in Office images.

Another easy way is to use the VSTO Ribbon IDs Power Tool. You can download and install this from https://www.microsoft.com/downloads/details.aspx?FamilyId=46B6BF86-E35D-4870-B214-4D7B72B02BF9&displaylang=en. This tool adds an ImageMso Window item to the Tools menu in Visual Studio. Select that menu item to display the ImageMso Values dialog box. Type print in the search box and click the magnifying glass. The buttons with print in the id name appear (see Figure 5). When you click the image you want, the tool copies the id to the clipboard.

Figure 5. You can use the VSTO Ribbon IDs Power Tool to find the ids for built-in Office images.

Return to the Ribbon Designer. Name the second button print6Button. Set the ControlSize property to RibbonControlSizeLarge, the Label property to Print 6, the SuperTip property to Print handouts with 6 slides per page and the OfficeImageId property to FilePrintQuick.

Name the third button saveAsPPTButton. Set the ControlSize property to RibbonControlSizeLarge, the Label property to Save As PPT, the SuperTip property to Save this presentation as a PPT and the OfficeImageId property to FileSaveAsOtherFormats.

Name the fourth button saveAsPPTXButton. Set the ControlSize property to RibbonControlSizeLarge, the Label property to Save As PPTX, the SuperTip property to Save this presentation as a PPTX and the OfficeImageId property to FileSaveAsOtherFormats. The Ribbon Designer should look like Figure 6.

Figure 6. The Ribbon Designer should look like this.

Save your changes and build the solution. To see what this looks like in PowerPoint, press F5. The Common Tasks group is added to the Add-Ins tab on the Ribbon and the four buttons appear with the desired images (see Figure 7).

Figure 7. The Common Tasks group is added to the Add-Ins tab of the Ribbon.

Write Code That Runs When You Click a Button

Your next step is to write the code that will perform a task when you click each of these buttons. Quit PowerPoint and return to Visual Studio.

In the Ribbon Designer, double-click the Print 3 button. Add the following code to the print3Button_Click event handler:

With Globals.ThisAddIn.Application.ActivePresentation
  .PrintOptions.OutputType = _
    PowerPoint.PpPrintOutputType.ppPrintOutputThreeSlideHandouts
  .PrintOptions.PrintColorType = _
    PowerPoint.PpPrintColorType.ppPrintPureBlackAndWhite
  .PrintOptions.FrameSlides = Office.MsoTriState.msoTrue
  .PrintOut()
End With

This AddIn represents the add-in itself. This AddIn Application represents the application in which the add-in runs, in this case PowerPoint. ActivePresentation represents the presentation that is currently open. To reference the This AddIn class, you would typically create an instance of it. You can’t do that here because the Visual Studio Tools for Office runtime creates an instance of the class when the add-in starts. Instead, use Globals. ThisAddIn to refer to the existing instance.

The code above is the programmatic equivalent of making selections in the Print dialog box, as shown in Figure 8, and then clicking OK to print.

Figure 8. The code above makes these selections.

Select View | Designer. Double-click the Print 6 button. Add the following code to the print6Button_Click event handler:

With Globals.ThisAddIn.Application.ActivePresentation
  .PrintOptions.OutputType = _
    PowerPoint.PpPrintOutputType.ppPrintOutputSixSlideHandouts
  .PrintOptions.PrintColorType = _
    PowerPoint.PpPrintColorType.ppPrintPureBlackAndWhite
  .PrintOptions.FrameSlides = Office.MsoTriState.msoTrue
  .PrintOut()
End With

Select View | Designer. Double-click the Save As PPT button. Add the following code to the saveAsPPTButton_Click event handler:

SavePresentation("PPT")

Select View | Designer. Double-click the Save As PPTX button. Add the following code to the saveAsPPTXButton_Click event handler:

SavePresentation("PPTX")

Add the following method to save the presentation as either a PPT or a PPTX:

Private Sub SavePresentation(ByVal format As String)
  Dim pres As PowerPoint.Presentation
  pres = Globals.ThisAddIn.Application.ActivePresentation
  If pres.Path = String.Empty Then
    ' The file has not been previously saved.
    ' It will be saved in the current folder,
    ' which is the location of the add-in assembly.
    If format = "PPT" Then
      pres.SaveAs(pres.Name, _
        PowerPoint.PpSaveAsFileType.ppSaveAsPresentation)
    ElseIf format = "PPTX" Then
      pres.SaveAs(pres.Name, _
        PowerPoint.PpSaveAsFileType.ppSaveAsOpenXMLPresentation)
    End If
  Else
    ' The file has been previously saved as a ppt or a pptx.
    Dim presName As String = pres.FullName
    If presName.Contains(".pptx") Then
      presName = presName.Remove(presName.Length - 5)
    Else
      presName = presName.Remove(presName.Length - 4)
    End If
    If format = "PPT" Then
      pres.SaveAs(presName, _
        PowerPoint.PpSaveAsFileType.ppSaveAsPresentation)
    ElseIf format = "PPTX" Then
      pres.SaveAs(presName, _
        PowerPoint.PpSaveAsFileType.ppSaveAsOpenXMLPresentation)
    End If
  End If
End Sub

The code listings above use the PowerPoint object model to automate the steps involved in creating, printing and saving a presentation. If you have automated PowerPoint in the past, either with VBA or external Automation, most of the code above is probably familiar to you. Very little of it is new to PowerPoint 2007.

Save your changes and build the solution. To test these buttons in PowerPoint, press F5. Open an existing presentation. Click the Print 3 button to print handouts with 3 slides on each page. Click the Print 6 button to print handouts with 6 slides on each page. Click the Save As PPT button to save a copy of the presentation that is compatible with PowerPoint 97 – 2003. Click the Save As PPTX button to save a copy of the presentation in PowerPoint 2007 format.

Get the Most Out of the Ribbon

Adding buttons to the Ribbon is a common scenario and a great way to add functionality to Office applications. The Ribbon supports a number of additional controls you can use, including drop-down lists, menus and galleries. In the next few sections of this tutorial, you will see how to use these controls.

Use a Drop-down List

The buttons in a gallery perform actions. In contrast, you would typically use the items in a drop-down list to make a selection before taking an action. Rather than have two buttons for saving the presentation, you can select how you want it saved from a drop-down list and then click a Save button.

Exit PowerPoint and return to the Ribbon Designer in Visual Studio. From the Toolbox, drag a Group control onto the Ribbon to the right of the existing group. Set the Label property to Common Tasks 2. From the Toolbox, drag a DropDown control into the new group. Name the control saveAsDropDown. Set the Label property to Save as. Click the ellipsis associated with the Items property. Click Add to add a new item. Set the Label property of the first item to PPT. Click Add to add a new item. Set the Label property of the first item to PPTX. Click OK to close the dialog.

From the Toolbox, drag a Button control into the Common Tasks 2 group. The button appears under the drop-down list. Set the ControlSize property to RibbonControlSizeLarge and the button moves to the right of the drop-down list. Name the button saveButton. Set the Label property to Save, the SuperTip property to Save this presentation and the OfficeImageId property to FileSaveAsOtherFormats.

Double-click the Save button. Add the following code to the saveButton_Click event handler:

SavePresentation(saveAsDropDown.SelectedItem.Label)

Save your changes and build the solution. To see what this looks like in PowerPoint, press F5. The Common Tasks 2 group is added to the Add-Ins tab on the Ribbon (see Figure 9).

Figure 9. Use a drop-down list to present the user with choices.

Buttons are the most visual way to display choices in the Ribbon, but they can also take up the most screen real estate. One way to save space is to use a menu. A Ribbon menu is actually a drop-down list and can contain buttons, check boxes, galleries, other menus, seperators, split buttons and toggle buttons.

Figure 10. Add menu items to the menu design surface.

From the Toolbox, drag a button into the menu design surface. Name the button newUrbanButton. Set the Label property to New deck using Urban theme. Double-click the button. Add the following code to the newUrbanButton_Click event handler:

With Globals.ThisAddIn.Application
  .Presentations.Add(Office.MsoTriState.msoTrue)
  .ActiveWindow.View.GotoSlide( _
    .ActivePresentation.Slides.Add(Index:=1, _
    Layout:=PowerPoint.PpSlideLayout.ppLayoutTitle).SlideIndex)
  .ActivePresentation.ApplyTemplate( _
    "C:\Program Files\Microsoft Office\" & _
    "Document Themes 12\Urban.thmx")
  If CBool(.ActivePresentation.HasTitleMaster) Then
    With .ActivePresentation.TitleMaster.HeadersFooters
      .Footer.Visible = Office.MsoTriState.msoTrue
      .SlideNumber.Visible = Office.MsoTriState.msoTrue
    End With
  End If
  With .ActivePresentation.SlideMaster.HeadersFooters
    .Footer.Visible = Office.MsoTriState.msoTrue
    .SlideNumber.Visible = Office.MsoTriState.msoTrue
  End With
  With .ActivePresentation.Slides.Range.HeadersFooters
    .Footer.Visible = Office.MsoTriState.msoTrue
    .SlideNumber.Visible = Office.MsoTriState.msoTrue
  End With
  .ActiveWindow.View.GotoSlide( _
    .ActivePresentation.Slides.Add(Index:=2, _
    Layout:=PowerPoint.PpSlideLayout.ppLayoutText).SlideIndex)
  .ActiveWindow.View.GotoSlide( _
    .ActivePresentation.Slides.Add(Index:=2, _
    Layout:=PowerPoint.PpSlideLayout.ppLayoutText).SlideIndex)
  .ActiveWindow.View.GotoSlide(Index:=1)
End With

This code first adds a new presentation. It then adds a title slide and sets the template for the presentation to Urban. The code uses the default installation folder for Office 2007 themes. The code then turns adds slide numbers to the footer of each slide. Finally, the code adds two content slides.

Select View | Designer. From the Toolbox, drag a menu into the menu design surface below the button. Set the Label property to Print Handouts and the OfficeImageId property to FilePrint. Click the right arrow in the menu control to display the menu design surface. Here you will add the menu items that appear when you click Print.

From the Toolbox, drag two buttons into the menu design surface. Name the first button print3FromMenuButton. Set the Label property to 3 slides per page. Name the second button print6FromMenuButton. Set the Label property to 6 slides per page. The menu designer should look like Figure 11. Click the left arrow to return to the main menu designer.

Figure 11. The menu designer should look like this.

Double-click the 3 slides per page button. Either copy the code from the print3Button_Click method or move that code into a method and call the method from both the print3Button_Click and print3FromMenuButton_Click methods.

Select View | Designer. Double-click the 6 slides per page button. Either copy the code from the print6Button_Click method or move that code into a method and call the method from both the print6Button_Click and print6FromMenuButton_Click method.

Select View | Designer. From the Toolbox, drag a menu into the menu design surface below the Print handouts button. You may find it easiest to click the up arrow next to Tasks first and then click the down arrow to reenter the menu design surface. Set the Label property to Save presentation and the OfficeImageId property to FileSave. Click the right arrow in the menu control to display the menu design surface.

From the Toolbox, drag two buttons into the menu design surface. Name the first button saveAsPPTFromMenuButton. Set the Label property to Save as PPT. Name the second button saveAsPPTXFromMenuButton. Set the Label property to Save as PPTX.

Double-click the Save As PPT button. Add the following code to the saveAsPPTFromMenuButton_Click event handler:

SavePresentation("PPT")

Select View | Designer. Double-click the Save As PPTX button. Add the following code to the saveAsPPTXFromMenuButton_Click event handler:

SavePresentation("PPTX")

Save your changes and build the solution. To see what this looks like in PowerPoint, press F5. The Common Tasks 3 group is added to the Add-Ins tab on the Ribbon (see Figure 12).

Figure 12. Use a menu to present the user with choices.

Select New deck using Urban theme. PowerPoint creates a new presentation using the Urban theme. Test the two print and save menu items and confirm they behave as expected.

Use a Gallery

The gallery control is similar to a menu, in that it is a drop-down list that contains other controls. A primary difference between a gallery and a menu is that a gallery can only contain buttons.

Exit PowerPoint and return to the Ribbon Designer in Visual Studio. From the Toolbox, drag a Gallery control into Common Tasks 3 group. Set the ControlSize property to RibbonControlSizeLarge, the Label property to Print handouts and the OfficeImageId property to FilePrint.

Click the ellipsis associated with the Items property. Click Add to add a new item. Set the Label property of the item to 3 per page, the SuperTip property to Print handouts with 3 slides per page and the OfficeImageId property to FilePrintQuick.

Click Add to add a second item. Set the Label property of the item to 6 per page, the SuperTip property to Print handouts with 6 slides per page and the OfficeImageId property to FilePrintQuick. Click OK to close the dialog.

Double-click the gallery control. Add the following code to the Gallery1_Click event handler:

If CType(CType(sender, RibbonGallery).SelectedItem,  _
  RibbonDropDownItem).Label = "3 per page" Then
  Print3()
Else
  Print6()
End If

In this code, sender represents the gallery. To know which button was clicked in the gallery, the code casts the SelectedItem to a RibbonDropDownItem to retrieve the Label property.

Save your changes and build the solution. To see what this looks like in PowerPoint, press F5. The Common Tasks 3 group is added to the Add-Ins tab on the Ribbon (see Figure 13). Test the two print gallery buttons and confirm they behave as expected.

Figure 13. Use a gallery to present the user with choices.

Conclusion

In this tutorial, you saw how a variety of ways you can customize the Ribbon in an Office application and add to it shortcuts to perform common tasks. In your daily life, there are a number of tasks you perform over and over, whether you are printing, saving, formatting, etc. By adding controls to the Ribbon, you can now perform these tasks in a handful of mouse clicks.

Once you learn the object models of the various Office applications, you can easily create Ribbon customizations in Visual Studio that can save you time and effort.

About Robert Green

Robert Green is a Senior Consultant with MCW Technologies. He is a Microsoft MVP for Visual Studio Tools for Office. Robert has written or co-authored AppDev’s Visual Studio, LINQ, Windows Communication Foundation and Windows Workflow Foundation courseware, and appears in the video training for these courses, as well. Robert is a member of the INETA Speaker Bureau and has been a frequent speaker at technology conferences. Before joining MCW, Robert worked at Microsoft for 8 years, as a Program Manager on the Visual Basic product team and as a Product Manager for Visual Studio, Visual Basic, Visual Studio Tools for Office and Visual FoxPro.