Overview of Code Snippets Using Visual Studio 2005 Tools for Office
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
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)
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.
To use these snippets, you must install the following software, in order:
Microsoft Windows 2000 or later
Microsoft Office Professional 2003 SP1 (complete installation)
Microsoft Office Excel 2003 and Microsoft Office Word 2003 with SP1
Microsoft Visual Studio 2005 Tools for the Microsoft Office System
To install the snippets, follow these steps:
Close any instance of Visual Studio 2005 that is running.
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.
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.
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")
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.
The exact same set of code snippets exist for Visual Basic and C#. The example is shown in Visual Basic for simplicity.
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.
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.
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.
Visual Studio 2005 Tools for Office
Office Developer Center
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.