Walkthrough: Creating Your First Application-Level Add-in for Excel

Artikel
02/04/2013

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.

Applies to: The information in this topic applies to application-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

This walkthrough illustrates the following tasks:

Creating an Excel add-in project for Excel.
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:

An edition of Visual Studio 2010 that includes the Microsoft Office developer tools. For more information, see [Configuring a Computer to Develop Office Solutions](bb398242\(v=vs.100\).md).

Microsoft Office Excel 2007 or Excel 2010.

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 templates pane, expand Visual C# or Visual Basic, and then expand Office.
Under the expanded Office node, select the 2007 if you have Excel 2007 installed, or select the 2010 node if you have Excel 2010 installed.
In the list of project templates, select Excel 2007 Add-in or Excel 2010 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 Programming Application-Level Add-Ins. 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 Events in Office Projects.

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 Microsoft.Office.Interop.Excel.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 Microsoft.Office.Interop.Excel.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 Solutions.
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.

Dela via