Walkthrough: Creating Your First Document-Level Customization for Excel

Artikel
11/16/2012

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Microsoft Office version Excel 2007 Excel 2003 For more information, see Features Available by Application and Project Type.

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Project type

Document-level projects

Microsoft Office version

Excel 2007
Excel 2003

For more information, see Features Available by Application and Project Type.

This introductory walkthrough shows you how to create a document-level customization for Microsoft Office Excel. The features that you create in this kind of solution are available only when a specific workbook is open. You cannot use a document-level customization to make application-wide changes, for example, displaying a new Ribbon tab when any workbook is open.

This walkthrough illustrates the following tasks:

Creating an Excel workbook project for Excel 2003 or Excel 2007.
Adding text to a worksheet that is hosted in the Visual Studio designer.
Writing code that uses the object model of Excel to add text to the customized worksheet when it is opened.
Building and running the project to test it.
Cleaning up the completed project to remove unnecessary build files and security settings from your development computer.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Prerequisites

You need the following components to complete this walkthrough:

Visual Studio Tools for Office (an optional component of Visual Studio 2008 Professional and Visual Studio Team System).
Excel 2003 or Excel 2007.

By default, Visual Studio Tools for Office is installed with the listed versions of Visual Studio. To check whether it is installed on your computer, see Installing Visual Studio Tools for Office.

Creating the Project

To create a new Excel workbook project in Visual Studio

Start Visual Studio.
On the File menu, point to New, and then click Project.
In the Project Types pane, expand Visual C# or Visual Basic, and then expand Office.
Select the 2007 folder if you are developing an Excel 2007 customization, or select the 2003 folder if you are developing an Excel 2003 customization.

Note

If you are developing a document-level customization for a particular version of Excel, that version must be installed on your development computer.
In the Templates pane, select Excel 2003 Workbook or Excel 2007 Workbook.
In the Name box, type FirstWorkbookCustomization.
Click OK.

The Visual Studio Tools for Office Project Wizard opens.
Select Create a new document, and click OK.
- Visual Studio creates the FirstWorkbookCustomization project, and adds the following files to the project.
- FirstWorkbookCustomization.xlsx - Represents the Excel workbook in the project. Contains all the worksheets and charts.
- Sheet1 (.vb file for Visual Basic or .cs file for Visual C#) - A worksheet that provides the design surface and the code for the first worksheet in the workbook. For more information, see Worksheet Host Item.
- Sheet2 (.vb file for Visual Basic or .cs file for Visual C#) - A worksheet that provides the design surface and the code for the second worksheet in the workbook.
- Sheet3 (.vb file for Visual Basic or .cs file for Visual C#) - A worksheet that provides the design surface and the code for the third worksheet in the workbook.
- ThisWorkbook (.vb file for Visual Basic or .cs file for Visual C#) - Contains the design surface and the code for workbook-level customizations. For more information, see Workbook Host Item.
The Sheet1 code file is opened automatically in the designer.

Closing and Reopening Worksheets in the Designer

If you deliberately or accidentally close a workbook or a worksheet in the designer while you are developing your project, you can reopen it.

To close and reopen a worksheet in the designer

Close the workbook by clicking the Close button (X) for the designer window.
In Solution Explorer, right-click the Sheet1 code file, and click View Designer.

- or -

In Solution Explorer, double-click the Sheet1 code file.

Adding Text to a Worksheet in the Designer

You can design the user interface (UI) of your customization by modifying the worksheet that is open in the designer. For example, you can add text to cells, apply formulas, or add Excel controls. For more information about how to use the designer, see Office Documents in the Visual Studio Environment Overview.

To add text to a worksheet by using the designer

In the worksheet that is open in the designer, select cell A1, and then type the following text.

This text was added by using the designer.

Warning

If you add this line of text to cell A2, it will be overwritten by other code in this example.

Adding Text to a Worksheet Programmatically

Next, add code to the Sheet1 code file. The new code uses the object model of Excel to add a second line of text to the workbook. By default, the Sheet1 code file contains the following generated code:

A partial definition of the Sheet1 class, which represents the programming model of the worksheet and provides access to the object model of Excel. For more information, Worksheet Host Item and Word Object Model Overview. The remainder of the Sheet1 class is defined in a hidden code file that you should not modify.
The Sheet1_Startup and Sheet1_Shutdown event handlers. These event handlers are called when Excel loads and unloads your customization. Use these event handlers to initialize your customization when it is loaded, and to clean up resources used by your customization when it is unloaded. For more information, see Visual Studio Tools for Office Project Events.

To add a second line of text to the worksheet by using code

In Solution Explorer, right-click Sheet1, and then click View Code.

The code file opens in Visual Studio.

Replace the Sheet1_Startup event handler with the following code. When Sheet1 is opened, this code adds a second line of text to the worksheet.

Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    Dim nr As Microsoft.Office.Tools.Excel.NamedRange = _
        Me.Controls.AddNamedRange(Me.Range("A2"), "NamedRange1")
    nr.Value2 = "This text was added by using code" 
End Sub

private void Sheet1_Startup(object sender, System.EventArgs e)
{
    Microsoft.Office.Tools.Excel.NamedRange nr =
        this.Controls.AddNamedRange(this.Range["A2", missing], "NamedRange1");
    nr.Value2 = "This text was added by using code";
}

Testing the Project

To test your workbook

Press F5 to build and run your project.

When you build the project, the code is compiled into an assembly that is associated with the workbook. Visual Studio puts a copy of the workbook and the assembly in the build output folder for the project, and it configures the security settings on the development computer to enable the customization to run. For more information, see Office Solution Build Process Overview.
In the workbook, verify that you see the following text.

This text was added by using the designer.

This text was added by using code.
Close the workbook.

Cleaning up the Project

When you finish developing a project, you should remove the files in the build output folder and the security settings created by the build process.

To clean up the completed project on your development computer

In Visual Studio, on the Build menu, click Clean Solution.

Next Steps

Now that you have created a basic document-level customization for Excel, you can learn more about how to develop customizations from these topics:

General programming tasks that you can perform in document-level customizations: Programming Document-Level Customizations.
Programming tasks that are specific to document-level customizations for Excel: Excel Document-Level Customization Development
Using the object model of Excel: Excel Object Model Overview.
Customizing the UI of Excel, for example, by adding a custom tab to the Ribbon or creating your own actions pane: Office UI Customization.
Using extended Excel objects provided by Visual Studio Tools for Office to perform tasks that are not possible by using the Excel object model (for example, hosting managed controls on documents and binding Excel controls to data by using the Windows Forms data binding model): Automating Excel by Using Extended Objects.
Building and debugging document-level customizations for Excel: Building and Debugging Office Solutions.
Deploying document-level customizations for Excel: Deploying Office Solutions.

Change History

Date	History	Reason
July 2008	Added topic.	Information enhancement.

Dela via