Walkthrough: Intercept incident 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
Overview: Microsoft's Visual Studio Toos can easily integrate Events called by Microsoft's Excel 2003 in the hosted code. This exercise demonstrates the response of the SheetFollowHyperLink event called by Excel's Application object.
content
Introduction
Necessary condition
Start
Handle documentation
Handle event
Increase event code and test
in conclusion
Introduction
In this walkthrough, you will use the Tools provided by Visual Studio. Net to establish a new Excel 2003 document. You will add the code to respond to the SheetFollowHyperLink event in the Application object in Excel, and create a new worksheet when you click on this link.
Necessary condition
To complete this walkthrough, the following software and components must be installed on the 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
Start
To start, you need to build a Visual Studio .NET project with Office Excel 2003 collaboration.
Create an Excel workbook project
1. Start Visual Studio .NET, click on the File menu, point to New, then click the project.
2. In the Project Type Panel, the Microsoft Office System project is extended, select the Visual Basic project or Visual C # project.
3. In the template panel, select the Excel workbook.
4. Named EXCELEVENT and then stored in the local hard disk.
5. Accept the default value of the Microsoft Office Project Wizard, click Finish to create a new Excel workbook project.
Visual Studio .NET opens thisDocument.vb in the code editor or thisDocument.cs file.
Wizard Using Two File Storage Solutions:
Ø AssemblyInfo.vb or AssemblyInfo.cs Store prefix metadata.
Ø Thisworkbook.vb or thisworkbook.cs saves the code in response to the Excel event.
Handle documentation
To demonstrate the SheetFollowHyperLink events and create a new workbook, you need to add new hyperlinks in this workbook, follow these steps:
Add a hyperlink in this document
1. Press F5 to run the project, load an Excel and a new workbook.
2. In Excel, place the cursor in the A1 cell, select the hyperlink in the insert menu.
3. Open the Insert Hyper Links dialog box, click Linked Link in the left panel to the selection placed in this article.
4. Set the display text value to create a new workbook.
5. Make sure the cell reference matches the location of your hyperlink. When you are finished, the dialog is shown in the figure:
Figure 1: Insert Super Link Dialog Box
6. Click OK to close the dialog box, you will see the newly created hyperlink in the workbook.
7. Select Save in the File menu to save your workbook.
8. Close Excel Return to Visual Studio .NET
Handle event
The OfficeCodeBehind class established by Visual Studio .Net allows you to simply add the code to the existing program to handle the Open and BeforeClose events of the document. In this exercise, you will write code to respond to events that have not been processed - SHEETFOLLOWHYPERLINK. You will add event programs, intercept event processing, and respond to events. Choose different pieces based on your programming language.
Event processing (Visual Basic)
For your code, you need to respond to Application.SheetFollow.hyperlink events. In this article you increase the code to respond to this event.
Increase Event Treatment (Visual Basic)
1. Select THISPPLICATION in the left corner class drop-down list box of the code editor.
2. Select SheetFollowHyperLink in the Method Download List of the upper right corner.
The time code segment built by Visual Studio .Net is as follows:
'Visual Basic Private
Sub thisapplication_sheetfollowhyperlink (_
Byval sh as Object, _
BYVAL TARGET AS Microsoft.Office.Interop.Excel.HyperLink)
Handles thisapplication.sheetfollowhyperlink
End Sub
Handling events (C #)
In order to be your C # code, you need to respond to Application.WindowDeactivate events, in this paragraph, you need to add the code to respond to this event, although the code created by the Visual Studio .NET template uses a slightly complicated code to intercept This event, the steps in this end use simple techniques to increase event processing.
Increase event processing (C #)
1. Add the following programs to the OfficeCodeBehind class established by Visual Studio .NET:
// c #
Protected void thisapplication_sheetfollowhyperlink (Object SH, Excel.hyperLink Target) {}
2. In the thisworkbook_open program, enter the following code. Once you have entered this paragraph code, you will see Tooltip. Press the Tab key to complete the code line according to the instructions of Tooltip:
// 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);
Increase event code and test
Once you set the event handle, you can add the code to respond to the event. The following steps will create a new workbook and keep the focus in the original workbook.
Add code to create a new workbook
1. Modify the THISPPLICATION_SHEETFOLLOWHYPERLINK program as follows: '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 that the Worksheets.Add method accepts four parameters, indicating where the new workbook is inserted, the increased workpiece number, increase the type of object. If you don't define these parameters, Excel adds a separate new worksheet as the first worksheet for this workbook. In Visual Basic for Applications and Visual Baisc .NET, you can simply pass these parameters, Excel do the rest. However, the C # does not support the option parameters. In order to indicate that you want Excel to do the right behavior, pass type.missing to each parameter.
2. Select Save all files in the File menu to save your entire program.
3. Press F5 to run this project. This will load Excel and the document you created.
4. Click on the hyperlink, and the Excel is the new worksheet. Try to build more worksheets to test the work of the event handler.
5. Exit Excel and return to Visual Studio .NET.
in conclusion
In this exercise, you will be able to build a new worksheet using the SheetFollowHyperLink events in the Excelapplication object. Visual Studio Tools using Microsoft Office System allows you to integrate events that call the Microsoft Office Excel 2003 objects and managed code.
