WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Add-ins provide extended features or services for an application that hosts it. Add-ins can automate certain tasks in its host application, such as a Microsoft Office product. And, add-ins can customize the user interface (UI) of its host application.
Visual Studio Tools for Office
Visual Studio Tools for Office (VSTO) is a Visual Studio add-in that exposes a set of tools, plus a runtime that allows MS Office applications (from 2003 upwards) to host the .NET Framework Common Language Runtime to expose their functionality via .NET. This runtime is known as VSTO Loader. VSTO Loader has no managed dependency and is the only component of the VSTO Runtime engine that is always running.
By creating a VSTO add-in, you create a managed code assembly that is loaded by a Microsoft Office application. The add-in then can respond to events that are raised in the application. The add-in is also able to make use of any of the classes in the .NET Framework.
VSTO Add-in Components
There are several components that enable Microsoft Office applications discover and load VSTO Add-ins. These are:
MS Office applications discover the VSTO add-ins by looking for a set of registry entries. These entries are as follows:
32-bit: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\application name\Addins\add-in ID
64-bit: HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Office\application name\Addins\add-in ID
32-bit: HKEY_CURRENT_USER\Software\Microsoft\Office\application name\Addins\add-in ID
64-bit: HKEY_CURRENT_USER\Software\Wow6432Node\Microsoft\Office\application name\Addins\add-in ID
Deployment Manifest: Points to the current application manifest.
Application Manifest: Points to the add-in's assembly as well as specify the entry point to execute from the assembly.
Visual Studio Tools for Office Runtime: End-user computers must have the Visual Studio Tools for Office runtime installed.
You will be creating three very basic VSTO add-ins today, one each for MS Word, MS Excel, and MS PowerPoint. Let's get started.
Creating a Word VSTO Add-in
Start Visual Studio and click File, New Project.
- Select Visual Basic under Templates.
- Select Office/SharePoint.
- Select Word version Add-In.
Figure 1 displays the New Project dialog box, showing some of the available VSTO add-in templates.
Figure 1: VSTO add-in templates
You will notice that your Solution Explorer looks a bit different, as shown in Figure 2. This is obviously because you are creating a whole different type of project. Double-click the ThisAddIn Class and add the following code into it:
Public Class ThisAddIn Private Sub ThisAddIn_Startup() Handles Me.Startup End Sub Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown End Sub Private Sub Application_DocumentBeforeSave(ByVal Doc As Word.Document, _ ByRef SaveAsUI As Boolean, _ ByRef Cancel As Boolean) Handles Application.DocumentBeforeSave Doc.Paragraphs(1).Range.InsertParagraphAfter() Doc.Paragraphs(1).Range.Text = "Entered After Save" 'Doc.Paragraphs(1).Range.InsertBreak()' 'Doc.Paragraphs(2).Range.InsertDateTime()' End Sub End Class
Figure 2: Solution Explorer
This is very basic. A new event named Application_DocumentBeforeSave is added. This event fires as soon as a Save method has been invoked inside the host. The preceding code simply inserts a paragraph after the first paragraph and adds a little text. The commented lines demonstrate how easily you can add a separate page break or the automatic date and time into the document that is being saved. Here is more information on the Word.Document.
This will not work yet.
You can run your application and it will open up MS Word where you could test the functionality. To add the add-in to Word, follow these steps:
- Click File.
- Click Options.
- Click Add-Ins.
In the Manage drop-down menu, select COM Add-Ins and click Go. The name of your Add-in should be listed, as shown in Figure 3. Then, you click OK.
Figure 3: COM add-in
Creating an Excel VSTO Add-in
Creating an Excel add-in is also straightforward. You could simply repeat the steps I listed earlier and select an Excel add-in instead. Once loaded, add the following code:
Private Sub ThisAddIn_Startup() Handles Me.Startup End Sub Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown End Sub Private Sub Application_WorkbookBeforeSave(ByVal Wb As _ Microsoft.Office.Interop.Excel.Workbook, _ ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) _ Handles Application.WorkbookBeforeSave Dim ExcelWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, _ Excel.Worksheet) Dim ExcelRow As Excel.Range = ExcelWorksheet.Range("B9") ExcelRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow ) Dim InsertedRow As Excel.Range = activeWorksheet.Range("B15") InsertedRow.Value2 = "Text" End Sub End Class
This code inserts a row underneath Row 9 and it keeps the formatting; then, it simply adds a little text to Cell B15. Here is more information on the Excel Worksheet object.
Let MS Excel recognize the add-in in the same manner that you added the MS Word add-in.
Creating a PowerPoint VSTO Add-in
Add the following code into your PowerPoint add-in project:
Public Class ThisAddIn Private Sub ThisAddIn_Startup() Handles Me.Startup End Sub Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown End Sub Private Sub Application_PresentationNewSlide(ByVal Sld As _ PowerPoint.Slide) _ Handles Application.PresentationNewSlide Dim TextBox As PowerPoint.Shape = Sld.Shapes.AddTextbox( _ Office.MsoTextOrientation. msoTextOrientationDownward, _ 0, 0, 500, 50) TextBox.TextFrame.TextRange.InsertAfter("TextBox Text.") End Sub End Class
This code simply adds a Textbox to a new slide when a New slide event has been fired and then adds some text in a downwards direction into the Textbox. Here is more information on the PowerPoint.Slide object.
Knowing how to create Microsoft Office add-ins is quite handy. Although this is just the tip of the iceberg, it is good place to start your adventures with add-ins.