Walkthrough: Hooking Events in Excel

  • [アーティクル]

 

Ken Getz
MCW Technologies

September 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Excel 2003
    Microsoft Visual Studio .NET 2003

Summary: Microsoft Visual Studio Tools for the Microsoft Office System makes it easy to integrate managed code with events raised by Microsoft Office Excel 2003 objects. This walkthrough demonstrates reacting to the SheetFollowHyperlink event raised by the Excel Application object. (6 printed pages)

Contents

Introduction
Prerequisites
Getting Started
Setting Up the Document
Setting Up the Event Handler
Adding the Event Code and Testing
Conclusion

Introduction

In this walkthrough, you'll use the tools provided by Microsoft® Visual Studio® .NET and Microsoft Visual Studio Tools for the Microsoft Office System to create a new Microsoft Office Excel 2003 document. You'll add code to react to the SheetFollowHyperlink event of the Excel Application object, creating a new worksheet when you click the link you'll create on the workbook.

Prerequisites

To complete this walkthrough, the following software and components must be installed on the development computer:

  • Microsoft Visual Studio .NET 2003 or Microsoft Visual Basic® .NET Standard 2003
  • Microsoft Visual Studio Tools for the Microsoft Office System
  • Microsoft Office Professional Edition 2003

**Tip   **This demonstration assumes that if you're a Visual Basic .NET programmer, you've set the Option Strict setting in your project to On (or have added the Option Strict statement to each module in your project), although it is not required. Setting the Option Strict setting to On requires a bit more code, as you'll see, but it also ensures that you don't perform any unsafe type conversions. You can get by without it, but in the long run, the discipline required by taking advantage of this option will far outweigh the difficulties it adds as you write code.

Getting Started

In order to get started, you'll need to create a new Visual Studio .NET project that works with Microsoft Office Excel 2003.

To create an Excel Workbook project

  1. Start Visual Studio .NET, and on the File menu, point to New, and then click Project.

  2. In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects or Visual C# Projects.

  3. In the Templates pane, select Excel Workbook.

  4. Name the project ExcelEvent, and store it in a convenient local path.

  5. Accept the defaults in the MicrosoftOffice Project Wizard, and click Finish to create the project and the new Excel workbook.

    Visual Studio .NET opens the ThisDocument.vb or ThisDocument.cs file in the Code Editor for you.

The wizard populates the solution with two files:

  • AssemblyInfo.vb or AssemblyInfo.cs, which stores assembly-level metadata.
  • ThisWorkbook.vb or ThisWorkbook.cs, which contains your code that responds to Excel events.

Setting Up the Document

In order to demonstrate the SheetFollowHyperlink event and to create a new worksheet, you'll need to add a new hyperlink within the sample workbook. Follow these steps to add the hyperlink:

To add a hyperlink to the document

  1. Press F5 to run the project, loading Excel and your new workbook.

  2. Within Excel, put the cursor in cell A1, and select Hyperlink on the Insert menu.

  3. In the Insert Hyperlink dialog box, in the Link to pane on the left side of the dialog box, select Place in This Document.

  4. Set the Text to display value to Create New Worksheet.

  5. Make sure the cell reference in the dialog box matches the location of your hyperlink. When you're done, the dialog box should look like Figure 1.

    Figure 1. The finished Insert HyperLink dialog box

  6. Click OK to dismiss the dialog box. You should see the new hyperlink within the workbook.

  7. Select Save on the File menu to save the workbook.

  8. Close Excel and return to Visual Studio .NET.

Setting Up the Event Handler

The OfficeCodeBehind class created by the Visual Studio .NET template includes event procedures that allow you to handle the Excel document's Open and BeforeClose events—simply add code to the existing procedures. In this exercise, you'll write code that reacts to an event that isn't already handled by the template code, the SheetFollowHyperlink event. You'll add the event procedure, hook up the event handling (if you're coding in Microsoft Visual C#®), and react to the event by creating a new worksheet within the active workbook. Select the appropriate section below, depending on your choice of language.

(Visual Basic Only) Set Up the Event Procedure

In order to start your code running, you'll need to react to the Application.SheetFollowHyperlink event. In this section, you'll add support for reacting to this event.

To add the event handler (Visual Basic)

  1. From the Class Name drop-down list in the upper-left corner of the Code Editor, select ThisApplication.

  2. From the Method Name drop-down list in the upper-right corner of the Code Editor, select SheetFollowHyperlink.

    Visual Studio .NET creates the event handler stub for you (wrapped to fit this space):

    ' Visual Basic
Private Sub ThisApplication_SheetFollowHyperlink( _
    ByVal Sh As Object, _
    ByVal Target As Microsoft.Office.Interop.Excel.Hyperlink) _
    Handles ThisApplication.SheetFollowHyperlink

End Sub

(C# Only) Set Up the Event Procedure

In order to start your C# code running, you'll need to react to the Application.WindowDeactivate event. In this section, you'll add support for reacting to this event. Although the code created by the Visual Studio .NET template uses a slightly more complex method to hook up the event, the steps in this section add event handlers that use a simpler technique.

To add the event handler (C#)

  1. Add the following procedure stub to the class created for you by Visual Studio .NET, OfficeCodeBehind:

    // C#
protected void ThisApplication_SheetFollowHyperlink(
    Object Sh, Excel.Hyperlink Target)
{
}

  2. In the ThisWorkbook_Open procedure, type the following code. Once you've entered the code, you'll see a ToolTip. Press the TAB key, as instructed by the ToolTip, to complete the line of code:

    // C#
thisApplication.SheetFollowHyperlink +=

// Once you've finished, the code will look like this, wrapped
// to fit this space:
ThisApplication.SheetFollowHyperlink += 
    new Microsoft.Office.Interop.Excel.
    AppEvents_SheetFollowHyperlinkEventHandler(
    ThisApplication_SheetFollowHyperlink);

Adding the Event Code and Testing

Once you've set up the event handler, you can add the code that will react to the event. Follow these steps to add code that will create a new worksheet, maintaining the focus on the original worksheet:

To add code to create a new worksheet

  1. Modify the ThisApplication_SheetFollowHyperlink procedure so the code looks like this:

    ' Visual Basic
Private Sub ThisApplication_SheetFollowHyperlink( _
    ByVal Sh As Object, _
    ByVal Target As Microsoft.Office.Interop.Excel.Hyperlink) _
    Handles ThisApplication.SheetFollowHyperlink
        Dim ws As Excel.Worksheet = _
            DirectCast(ThisApplication.ActiveSheet, Excel.Worksheet)
    ThisWorkbook.Sheets.Add()
    ws.Activate()
End Sub


// C#
protected void ThisApplication_SheetFollowHyperlink(
    Object Sh, Excel.Hyperlink Target)
{
    Excel.Worksheet ws = 
        (Excel.Worksheet)ThisApplication.ActiveSheet;
    ThisApplication.Worksheets.Add(Type.Missing, 
        Type.Missing, Type.Missing, Type.Missing);
    ws.Activate();
}

    **Note   **The Worksheets.Add method in Excel accepts up to four optional parameters, indicating where the new worksheet should be inserted, the number of sheets to add, and the type of object to add. If you don't specify these parameters, Excel adds a single new worksheet as the first worksheet in the workbook. In Visual Basic for Applications (VBA) and in Visual Basic .NET, you can simply pass no value for these parameters, and Excel does the right thing. C# doesn't support optional parameters, however. To indicate that you want Excel to infer the correct behavior for each parameter, pass the value Type.Missing for each parameter.

  2. Select Save All on the File menu to save your solution.

  3. Press F5 to run the project. This loads Excel with the document you've created.

  4. Click the hyperlink you previously created, and note that when you select the hyperlink, Excel creates a new worksheet. Try creating a few more worksheets to verify that the event-handling code is working properly.

  5. Quit Excel (saving changes if you like) and return to Visual Studio .NET.

Conclusion

In this walkthrough, you were able use the SheetFollowHyperlink event of the Excel Application object to create a worksheet in a workbook. Using Visual Studio Tools for the Microsoft Office System, you can easily integrate managed code with events raised by Microsoft Office Excel 2003 objects.