Overview of Code Snippets Using Visual Studio 2005 Tools for Office

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Kevin Schultz, NuSoft Solutions, Inc.

Pam Davis, NuSoft Solutions, Inc.

Matt Hessinger, Hessinger Consulting, Inc.

Published: December 2004

Revised: January 2006

Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Office Professional 2003, Microsoft Office Excel 2003, Microsoft Office Word 2003

Download OfficeVSTOCodeSnippets.msi.

Summary: IntelliSense code snippets for Visual Studio 2005 Tools for Office provide a quick start for Word and Excel development tasks in Visual Studio 2005, including data import and export, collaboration, and integration with Excel and Word controls. (10 printed pages)

Contents

• Overview

• Installing Snippets

• Snippet Categories for Visual Studio 2005 Tools for Office

• Sample Scenario Using Excel Code Snippets

• Sample Scenario Using Word Code Snippets

• Conclusion

• Additional Resources

• About the Authors

Overview

Microsoft IntelliSense code snippets enable developers to easily create and distribute their own customized code libraries. Using the Microsoft Visual Studio Integrated Development Environment (IDE) and the power of IntelliSense, inserting these commonly-used pieces of code is an effective way to enhance your productivity. You can save time because you no longer need to search countless sources of code examples just to find a similar piece of code to copy and paste into your solution code. Simply use a context menu to insert a code snippet, as shown in Figure 1, and then modify its highlighted custom parameters using annotated tooltips, if required, as shown in Figure 2.

Figure 1. Inserting the code snippet

Figure 2. Modifying custom parameters

IntelliSense code snippets are a valuable asset to the managed code developer who is writing applications for Microsoft Office. Microsoft Office provides a tremendous amount of functionality to the application developer. Almost any function performed by a user can be automated in code. But this much power imposes a sizeable learning curve, requiring detailed knowledge of special types, operations, and concepts.

Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office) merges the rich capabilities of Microsoft Office Word and Microsoft Office Excel into the Visual Studio IDE. Now, many of the same Visual Studio tools that make Microsoft Windows Form and Microsoft WebForm applications easy to create are available for Word and Excel applications. Using Visual Studio 2005 Tools for Office, your Office development process is simplified. For example, you can open Word or Excel for editing, drag and drop controls (either standard Windows controls or Visual Studio 2005 Tools for Office view controls), and create events, all within the IDE. This level of integration with the IDE enables the Office developer to use powerful Visual Studio features, including IntelliSense code snippets.

This article provides an overview of the Excel and Word code snippets created for Visual Studio 2005 Tools for Office, which are included in the OfficeVSTOCodeSnippets.msi download package. The snippets include both commonly used code and code that reveals some less well-known, yet highly useful, features. All of the code snippets are of the Method Body snippet type, which means they are structured to be inserted into an existing method. Also, a sample scenario for Excel and Word is described below, to illustrate the use of these code snippets.

Software RequirementsH8

To use these snippets, you must install the following software, in order:

• Microsoft Windows 2000 or later

• Microsoft Office Professional 2003 SP1 (complete installation)

  -or-

  Microsoft Office Excel 2003 and Microsoft Office Word 2003 with SP1

• Microsoft Visual Studio 2005 Tools for the Microsoft Office System

Installing Snippets

To install the snippets, follow these steps:

1. Close any instance of Visual Studio 2005 that is running.

2. Download and run the installer associated with this article, OfficeVSTOCodeSnippets.msi.

   By default, this installer places the C# snippets in the My Documents\Visual Studio 2005\Code Snippets\Visual C#\My Code Snippets\Office Development folder and the Visual Basic snippets in the My Documents\Visual Studio 2005\Code Snippets\Visual Basic\My Code Snippets\Office Development folder.

3. Start Visual Studio.

You can now view the code snippets in the Code Snippets Manager, as shown in Figure 3. The snippets that you just downloaded appear in an Office Development folder under My Code Snippets. There is another set of snippets in the top level Office Development folder that are included with Visual Studio 2005 Tools for Office.

Figure 3. Code Snippets Manager

Snippet Categories for Visual Studio 2005 Tools for Office

You can find the code snippets for Visual Studio 2005 Tools for Office by right-clicking in the code editor, and then selecting Insert Snippet. The Visual Studio 2005 Tools for Office code snippets are located under the My Code Snippets folder in the Office Development group, which has three main groupings of Excel, Office, and Word.

Sample Scenario Using Excel Code Snippets

This sample scenario steps through two code snippets that combine to create a new ListObject control at a user-selected cell, and then bind a data source to the list.

Note

The exact same set of code snippets exist for Visual Basic and Visual C#. The example is shown only in Visual Basic for simplicity.

The sample assumes that you used Visual Studio 2005 Tools for Office to create an Excel application written in Visual Basic. You can add code to any method, such as a class function or an event handler. This example assumes that you already added the Customers table in the Northwind database as a data source in the project.

From the Toolbox, drag a BindingSource control onto Sheet1. Set the DataSource property to NorthwindDataSet and the DataMember property to Customers. Drag a button onto Sheet1, and then double-click it to create the event handler. This creates a place for inserting the snippets that are described in this section. Your code on Sheet1 should look like the following:

Public Class Sheet1

    Private Sub Sheet1_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup
        'TODO: Delete this line of code to remove the default AutoFill for 
        'NorthwindDataSet.Customers'.
        If Me.NeedsFill("NorthwindDataSet") Then
            Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)
        End If
    End Sub

    Private Sub Sheet1_Shutdown(ByVal sender As Object, ByVal e As _
    System.EventArgs) Handles Me.Shutdown

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    End Sub
End Class

Creating the ListObjectH9

To insert a ListObject control, place your cursor in the function where you want to insert the code, for example, in the Button1_Click event procedure. Right-click where you want to insert the code and select Insert Snippets. Select My Code Snippets, then select Office Development, point to Excel, and then select the Create ListObject control by range snippet.

Figure 4. Inserting the Create ListObject control by range code snippet

The following code snippet is inserted:

'Creates a new ListObject control at the selected range.
Dim  As Microsoft.Office.Interop.Excel.Range
 = TryCast(.Application.Selection, _
   Microsoft.Office.Interop.Excel.Range)
If  IsNot Nothing Then
    Dim  As Microsoft.Office.Tools.Excel.ListObject _
       = .Controls.AddListObject(, "")
End If

When you insert code snippets, the Visual Studio IDE highlights items that you should replace. Eight values, in the above snippet, are highlighted, indicating that you should change them. Placing the mouse cursor over each highlighted region displays a tooltip describing the value. For example, the "ListObject1" value shows the tooltip "Replace with the name of the ListObject control." In this sample, the inserted code snippet works without any changes.

Binding the ListObject to the Data SourceH9

To bind the ListObject control created above to a data source, choose My Code Snippets, select Office Development, point to Excel, and then select the Bind ListObject contents to Data Source snippet.

Figure 5. Inserting the Bind ListObject contents to Data Source code snippet

The following code snippet is inserted:

'Uses a specified table in a data source. 
'Table values are bound for population of an existing ListObject.
.SetDataBinding(, "")

Again, the replacement values are highlighted, and tooltips describe the replacement items. For this snippet, that includes the ListObject1, DataSource1, and "TableName" values. Replacing ListObject1 with the object created in the first snippet, along with the BindingSource and the Customers table, results in the following code:

'Uses a specified table in a data source. 
'Table values are bound for population of an existing ListObject.
Me.Controls("ListObject1").SetDataBinding( _
     Me.NorthwindDataSet, "Customers") 

Note

Alternatively, you can bind the ListObject control directly to the binding source and omit the table name parameter in the SetDataBinding method.

Now that the snippets contain the specific values the solution requires, execute the code to verify the implementation. In this case, you create a new ListObject control at a user-selected cell location and populate it with the Customers table values from a previously loaded data source.

Sample Scenario Using Word Code Snippets

The available snippets show how you can implement a scenario in Word. This scenario creates a new Bookmark object and imports some text to the Bookmark location.

Note

The exact same set of code snippets exist for Visual Basic and C#. The example is shown in Visual Basic for simplicity.

Note

To insert a code snippet, right-click in the code editor, and then select Insert Snippet.

The following examples assume that you have a Word Document project open that you wrote in Visual Basic using Visual Studio 2005 Tools for Office. It also assume that you have an event handler that you can add code to. It also assumes that you have a Word document, C:\in.doc, to be imported into the Word document.

In the event handler, add the following line of code. This gets a Range reference to the end of the document, which is where you insert the new table.

Dim rng As Word.Range = Me.Bookmarks("\EndOfDoc").Range

Creating the Bookmark ObjectH9

First, create a new Bookmark in the document. You could use a standard Word Bookmark, but in this case you create a Visual Studio 2005 Tools for Office Bookmark. Right-click where you want to insert the code, and then select Insert Snippets. Under My Code Snippets, select Office Development, point to Word, and then select the Add Bookmark control to document at range snippet.

Figure 6. Inserting the Add Bookmark control to document at range code snippet

The following code snippet is inserted:

Dim name As String = ""
Dim  As Bookmark = .Controls.AddBookmark(, name)

When you insert code snippets, the Visual Studio IDE highlights items that you should replace. In this case, the code inserts "NewBookmark" as a default name for the Bookmark. Replace "Range1" with the name of the Range object where you create the Bookmark. The code should now look like this:

Dim name As String = "NewBookmark"
Dim Bookmark1 As Bookmark = Me.Controls.AddBookmark(rng, name)

This code inserts a new Bookmark, named "NewBookmark", at the range reference at the end of the document.

Importing TextH9

You are now ready to import the text from C:\in.doc. Under My Code Snippets, select Office Development, point to Word, and then select the Import entire Word Document and place at existing bookmark snippet.

Figure 7. Inserting the Import entire Word Document and place at existing bookmark code snippet

The following code snippet is inserted:

Dim rng1 As Microsoft.Office.Interop.Word.Range = _
     .Bookmarks("").Range
rng1.InsertFile("")

Again, observe the highlighted placeholders that you might need to change. Once you have replaced these placeholders with actual references and values, your code should look like this:

Dim rng1 As Microsoft.Office.Interop.Word.Range = _
     Me.Bookmarks("NewBookmark").Range
rng1.InsertFile("c:\in.doc")

Now that the snippets contain the specific values the solution requires, execute the code to verify the implementation. In this case, you created a new Bookmark control and populated it with the contents of the c:\in.doc file. Once you have a reference to the range with the inserted text, you can modify it. For example, you can change its format, convert it to a table, or insert a section break before or after the inserted text.

Conclusion

This article provides an overview of the code snippet feature in Visual Studio. It also provides an example of using Visual Studio 2005 Tools for Office code snippets to simplify implementation in a Word and Excel solution. Finally, the article describes groupings of the Visual Studio 2005 Tools for Office snippets. Reviewing these code snippets shows developers new to the Visual Studio 2005 Tools for Office environment how to implement many basic Word and Excel code tasks. Reviewing the code snippets shows experienced developers how to create reusable code snippets for Word and Excel solutions.

Additional Resources

Visual Studio 2005 Tools for Office

• Microsoft Office Developer Center: Visual Studio Tools for Office Developer Portal

• Microsoft Visual Studio Developer Center

• Visual Studio Tools for Office documentation

• Microsoft Visual Studio 2005 Tools for the Microsoft Office System blog

• Visual Studio Tools for Office forum

• Visual Studio Tools for the Microsoft Office System: Office Development Samples

Office Developer Center

• Microsoft Office Developer Center

Code Snippets

• Creating and Using IntelliSense Code Snippets

Code Security

• Writing Secure Code, Second Edition

• Microsoft Security Developer Center

• Security at Home: Protect Your PC

About the Authors

Kevin Schultz has created multiple desktop and client/server applications for retail and commercial user bases over the past ten years. Along with business intelligence (data warehousing) and embedded platform (Pocket PC) experience, Visual Studio 2005 Tools for Office provides another platform that enables Kevin to bridge the gap between traditional desktop and Web development and the mobility demands of today. Kevin works for NuSoft Solutions.

Pam Davis is a senior developer at NuSoft Solutions and a contributor to this sample.

Matt Hessinger has been involved in software development and architecture for over ten years. He has recently started his own firm, Hessinger Consulting, to focus on helping emerging companies leverage solid architectural approaches for Microsoft .NET and Office development. Hessinger spent the last three years as chief architect at Econium, an early adopter of service-oriented architecture (SOA), .NET, and collaborative technologies. He and his wife Diana, a stained glass artist, live in Upper Montclair, New Jersey.