Walkthrough: Creating Your First Application-Level Add-in 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 Application-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

Application-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 an application-level add-in for Microsoft Office Excel. The features that you create in this kind of solution are available to the application itself, regardless of which workbooks are open.

This walkthrough illustrates the following tasks:

Creating an Excel add-in project for Excel 2003 or Excel 2007.
Writing code that uses the object model of Excel to add text to a workbook when it is saved.
Building and running the project to test it.
Cleaning up the completed project so that the add-in no longer runs automatically on 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 add-in 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 add-in, or select the 2003 folder if you are developing an Excel 2003 add-in.

Note

If you are developing an add-in for a particular version of Excel, that version must be installed on your development computer.
In the Templates pane, select Excel 2003 Add-in or Excel 2007 Add-in.
In the Name box, type FirstExcelAddIn.
Click OK.

Visual Studio creates the FirstExcelAddIn project and opens the ThisAddIn code file in the editor.

Writing Code to Add Text to the Saved Workbook

Next, add code to the ThisAddIn code file. The new code uses the object model of Excel to insert boilerplate text in the first row of the active worksheet. The active worksheet is the worksheet that is open when the user saves the workbook. By default, the ThisAddIn code file contains the following generated code:

A partial definition of the ThisAddIn class. This class provides an entry point for your code and provides access to the object model of Excel. For more information, see AddIn Host Item. The remainder of the ThisAddIn class is defined in a hidden code file that you should not modify.
The ThisAddIn_Startup and ThisAddIn_Shutdown event handlers. These event handlers are called when Excel loads and unloads your add-in. Use these event handlers to initialize your add-in when it is loaded, and to clean up resources used by your add-in when it is unloaded. For more information, see Visual Studio Tools for Office Project Events.

To add a line of text to the saved workbook

In the ThisAddIn code file, add the following code to the ThisAddIn class. The new code defines an event handler for the WorkbookBeforeSave event, which is raised when a workbook is saved.

When the user saves a workbook, the event handler adds new text at the start of the active worksheet.

Private Sub Application_WorkbookBeforeSave(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeSave
    Dim activeWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, Excel.Worksheet)
    Dim firstRow As Excel.Range = activeWorksheet.Range("A1")
    firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
    Dim newFirstRow As Excel.Range = activeWorksheet.Range("A1")
    newFirstRow.Value2 = "This text was added by using code" 
End Sub

void Application_WorkbookBeforeSave(Microsoft.Office.Interop.Excel.Workbook Wb, bool SaveAsUI, ref bool Cancel)
{
    Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
    Excel.Range firstRow = activeWorksheet.get_Range("A1", missing);
    firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
    Excel.Range newFirstRow = activeWorksheet.get_Range("A1", missing);
    newFirstRow.Value2 = "This text was added by using code";
}

If you are using C#, add the following required code to the ThisAddIn_Startup event handler. This code is used to connect the Application_WorkbookBeforeSave event handler with the WorkbookBeforeSave event.
```
this.Application.WorkbookBeforeSave += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookBeforeSaveEventHandler(Application_WorkbookBeforeSave);
```

To modify the workbook when it is saved, the previous code examples use the following objects:

The Application field of the ThisAddIn class. The Application field returns a Application object, which represents the current instance of Excel.
The Wb parameter of the event handler for the WorkbookBeforeSave event. The Wb parameter is a Workbook object, which represents the saved workbook. For more information, see Excel Object Model Overview.

Testing the Project

To test the project

Press F5 to build and run your project.

When you build the project, the code is compiled into an assembly that is included in the build output folder for the project. Visual Studio also creates a set of registry entries that enable Excel to discover and load the add-in, and it configures the security settings on the development computer to enable the add-in to run. For more information, see Office Solution Build Process Overview.
In Excel, save the workbook.
Verify that the following text is added to the workbook.

This text was added by using code.
Close Excel.

Cleaning up the Project

When you finish developing a project, remove the add-in assembly, registry entries, and security settings from your development computer. Otherwise, the add-in will continue to run every time that you open Excel on your development computer.

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 application-level add-in for Excel, you can learn more about how to develop add-ins from these topics:

General programming tasks that you can perform in add-ins: Programming Application-Level Add-Ins.
Programming tasks that are specific to Excel add-ins: Excel Application-Level Add-in Development.
Using the object model of Excel: Excel Object Model Overview.
Customizing the user interface (UI) of Excel, for example, by adding a custom tab to the Ribbon or creating your own custom task pane: Office UI Customization.
Building and debugging application-level add-ins for Excel: Building and Debugging Office Solutions.
Deploying application-level add-ins for Excel: Deploying Office Solutions.

Change History

Date	History	Reason
July 2008	Added topic.	Information enhancement.

Dela via