Office 2003
Bring the Power of Visual Studio .NET to Business Solutions Built with Microsoft Office
Ken Getz and Brian A. Randell
Code download available at:MicrosoftOffice2003.exe(152 KB)
This article assumes you're familiar with Office, C# or Visual Basic .NET
Level of Difficulty123
SUMMARY
Microsoft Visual Studio Tools for the Microsoft Office System is a new technology that brings the advanced features of Visual Studio .NET and the .NET Framework to apps built on Microsoft Word 2003 and Excel 2003. Now you can use Visual Basic .NET and C# to write document-centric, managed code solutions that run in-process with Word 2003 or Excel 2003, taking advantage of the rich object models they expose. Along the way you get the benefits of the managed environment in which a fully compiled .NET-based application executes, including code access security.
Contents
Benefits
Getting Started
Investigating the Plumbing
Completing the Demonstration
Microsoft® Office provides a rich environment for creating business applications. With the COM-based object models exposed by Office applications, you can use Visual Basic® for Applications (VBA) code, out-of-process COM Automation, and COM add-ins to create unmanaged apps that target Office.
You can run VBA code directly in the Office application itself. This code runs in process, and the code and compiled bits are generally stored with the document (all Office applications except Outlook® store code within the document). Keep in mind that hosted VBA code is compiled to p-code, not native code, and this incurs a slight performance penalty, although running in process enables optimized method calls to Office objects.
You can also write applications in Visual Basic or other COM-aware languages that interact with the publicly exposed interfaces provided by the Office applications. In general, these applications run out of process, requiring calls through the COM proxy/stub layer. Automation applications are often compiled to native code and therefore execute faster than VBA code. Mitigating this benefit, however, is their decreased performance because of cross-application method calls.
Starting with Office 2000, Office applications search for registered add-in components that implement the IDTExtensibility2 interface, and load the add-ins either at startup or on demand, depending on how the add-in was configured. Using Visual Basic and other COM-aware languages and VBA, you can create applications that implement this interface—even using the Office Developer product and VBA, you can create COM add-ins. This level of flexibility provides a good compromise, allowing fully compiled applications to run in process. This technique does require registration of the components so that Office applications are aware of the add-in's existence. You can create a single COM add-in application that will load within multiple Office applications, and the COM add-in itself provides a cross-document toolset—its behavior doesn't inherently change from one document to the next.
There may be times when you find yourself in the world of managed code, and you'd like to interact with Office in a secure, garbage-collected world. It turns out that due to the wonders of COM interop and the common language runtime's (CLR) runtime-callable wrapper (RCW), you can still call out-of-process to methods within an Office application, and with a little effort, you can create COM add-ins using managed code. VBA, however, remains unmanaged and is likely to remain so in the foreseeable future.
Writing Framework-based code that uses Automation to control Office applications is fine if your intent is to create a front-end application yourself; users will need to start your application, which might then load Word or another application either visibly or invisibly. If you need to create a solution that can run in multiple Office hosts and is document agnostic, you can create a COM add-in using managed code.
It's possible that neither of these models fits your needs. Perhaps your users are used to loading a Word document and having your document management application run, or opening an Excel spreadsheet and performing statistical analysis on the contents of the workbook. Perhaps you want to use managed code to respond to Office application events. Maybe you want to be able to use Visual Basic .NET or C# code to react to a button or menu click that occurred within a Word application. Perhaps you want to display a Windows® Form created in Visual Studio® .NET in response to a request for input on an Excel spreadsheet. To satisfy these requirements, Microsoft has released Visual Studio Tools for Microsoft Office, an add-in for Visual Studio .NET 2003 that lets you create document-centric, managed applications that run in process with either Word 2003 or Excel 2003.
Benefits
There are many benefits to Visual Studio Tools for Office. You can use all the features of Visual Studio .NET to create Office-based applications. You can use either Visual Basic .NET or C# to create your applications. Both languages are more powerful than VBA. You can leverage all the features of the .NET Framework, from XML readers to ADO.NET to Web Services, from within Office applications.
You can also take advantage of the code access security that's built into the CLR. Unlike VBA macros, managed solutions using Visual Studio Tools for Office simply won't run unless they're explicitly trusted. The default settings used by the Visual Studio Tools for Office loader do not permit any assembly to run, which protects users from viruses and other malicious code. Before users can load a document with managed code extensions, the administrator must explicitly grant full trust to an assembly or location. Visual Studio Tools for Office allows you to take advantage of no-touch deployment. Users can automatically receive updates to the application with no intervention on their part.
Getting Started
In order to get your feet wet, we'll look at installation, then walk you through the process of creating a simple Excel application using Visual Basic .NET or C# code that will retrieve data and chart it.
Installing Visual Studio Tools for Office adds a set of project templates to Visual Studio .NET 2003, enabling you to create Word documents, template projects, or Excel worksheets using Visual Basic .NET or C# code to react to events and automate the products. Once you've installed Visual Studio .NET 2003, followed by Microsoft Office 2003, and finally, Visual Studio Tools for the Microsoft Office System (in that order), you'll be ready to create applications that take advantage of the Microsoft Office 2003 Primary Interop Assemblies (PIAs) stored in the Global Assembly Cache (GAC). You'll be able to create Word or Excel projects from within Visual Studio .NET that use Visual Basic .NET or C# code to automate Office in process. (And no, you haven't missed anything here—Visual Studio Tools for Office only allows you to create Word and Excel projects from within Visual Studio .NET at this time. You may see expanded support for other Office products as time goes on.)
To get started with the sample app, you need to create an Excel project in Visual Studio .NET. First, verify that you've installed Visual Studio .NET 2003, Office 2003, and Visual Studio Tools for Office. When installing Office 2003, it's imperative that you either perform a full installation or that you individually select .NET support for each of the Office products so that the appropriate PIAs are installed in the GAC. It's easiest to install the full Office product, but you can also individually install the PIAs for Word, Excel, Microsoft Forms, and Microsoft Graph.
Next, in Visual Studio .NET, use the File | New | Project menu item to select the project and template type. First select Microsoft Office 2003 Projects. Select either Visual Basic Projects or C# Projects, and then select Excel Workbook as the template type. Enter a project name and click OK when you're finished.
Finally, the second page of the Microsoft Office Project Wizard allows you to choose whether to use an existing document or to create a new one. You can also use the Security Settings link on this page to bypass the wizard's default offer to set up the necessary code access security for your project. For now, select the Create New Document option, choosing a convenient location for the project. Click Finish to complete the wizard's steps and create the new project. (Don't change the default security settings now. Wait until you're an expert with code access security and with the settings used by Visual Studio Tools for Office. Changing security settings at this point might make it impossible to run or debug your applications.)
Once you've created the project, you'll find that the Solution Explorer window includes references to the appropriate Office application, along with a file containing a class named OfficeCodeBehind that enables you to hook up event handlers for the document's events.
At the top of the code file (ThisDocument or ThisWorkbook, with the appropriate file extension for your selected language), you'll find Imports or using statements that simplify typing references to the objects within your host's object model:
' Visual Basic .NET Imports Excel = Microsoft.Office.Interop.Excel // C# using Excel = Microsoft.Office.Interop.Excel;
Next, you'll find an attribute describing the class:
' Visual Basic .NET ' Office integration attribute. Identifies the startup class ' for the document. Do not modify. <Assembly: System.ComponentModel.DescriptionAttribute( _ "OfficeStartupClass, Version=1.0, _ Class=ChartDemo.OfficeCodeBehind")> // C# // Office integration attribute. Identifies the startup class // for the document. Do not modify. [assembly:System.ComponentModel.DescriptionAttribute( "OfficeStartupClass, Version=1.0, Class=ChartDemo.OfficeCodeBehind")]
Although the included comment indicates that you shouldn't modify this attribute, there are occasions in which you must. For example, if you modify the class name or namespace, you'll need to manually update this attribute to match. The Visual Studio Tools for Office loader uses this attribute to determine the name of the class it should load as users open the host document—more on that later in this article.
Next, you'll find declarations for variables that correspond to the application and main document objects (ThisWorkbook and ThisApplication for Excel, and ThisDocument and ThisApplication for Word), as shown in Figure 1. In addition, the classes contain event handlers for basic events (Workbook.Open and Workbook.BeforeClose in Excel, Document.Open and Document.Close in Word), as shown in Figure 2.
Figure 2 Event Handlers
Visual Basic .NET
Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open End Sub Private Sub ThisWorkbook_BeforeClose(ByRef Cancel As Boolean) _ Handles ThisWorkbook.BeforeClose Cancel = False End Sub
C#
private Excel.WorkbookEvents_OpenEventHandler openEvent; private Excel.WorkbookEvents_BeforeCloseEventHandler beforeCloseEvent; protected void ThisWorkbook_Open() { } protected void ThisWorkbook_BeforeClose(ref bool Cancel) { Cancel = false; }
Figure 1 Declarations
Visual Basic .NET
Friend WithEvents ThisWorkbook As Excel.Workbook Friend WithEvents ThisApplication As Excel.Application
C#
internal Excel.Application ThisApplication { get { return thisApplication;} } internal Excel.Workbook ThisWorkbook { get { return thisWorkbook;} } private Excel.Application thisApplication = null; private Excel.Workbook thisWorkbook = null;
There is a bit more going on and if you expand the region named Generated initialization code within your class you'll find a few more procedures, which we'll discuss later in this article. For now, scroll down within the class and locate the ThisWorkbook_Open procedure. Add code so that the procedures look like the examples in Figure 3. Note the differences between the Visual Basic and C# code here. For example, in Visual Basic .NET, you can simply call the Application.Range property, passing a single parameter. The Application.Range property has been defined to accept two optional parameters, but C# supports neither parameterized properties nor optional parameters. Therefore, when creating the PIA for Excel, the team added the get_Range method, one of several accessor methods that make it possible for C# developers to work with the Excel object model. The same issue applies to the Range.Value property, which accepts a single parameter. In C#, you must use the Value2 property, which doesn't accept any parameters. In Visual Basic you can also use Value2, but there's no advantage to doing so.
Figure 3 Set Properties
Visual Basic .NET
Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open Dim rng As Excel.Range = ThisApplication.Range("A1") rng.Value = DateTime.Now.ToString() rng.Font.Name = "Verdana" rng.Font.Size = 16 End Sub
C#
protected void ThisWorkbook_Open() { Excel.Range rng = ThisApplication.get_Range("A1", Type.Missing); rng.Value2 = DateTime.Now.ToString(); rng.Font.Name = "Verdana"; rng.Font.Size = 16; }
Select Debug | Start to run the application. Visual Studio .NET compiles and runs the application, which loads Excel. If you've followed the steps carefully, you should see a spreadsheet appear with the correctly formatted text in cell A1. When you're finished admiring your work, close Excel and return to design mode.
To see what you've done, save your project, close Visual Studio .NET, and use Windows Explorer to browse to the folder containing your project and the Excel workbook file. Double-click the XLS file to load Excel, and verify that the appropriate text appears in the worksheet. Reload Visual Studio .NET and your sample project to continue the walkthrough.
As trivial as it may seem, you've accomplished something that would be quite tricky to tackle on your own—you've written managed code that reacts to an Excel event by interacting with the Excel object model from within Visual Basic .NET or C# code.
Investigating the Plumbing
When you created your project, you specified the location of the Word or Excel document that you wanted associated with the project. When Visual Studio .NET created the project, it set various properties associated with the project, including AssemblyLinkLocation and OfficeDocument properties (see Figure 4). The AssemblyLinkLocation property indicates where the Office document expects to find the assembly file needed to run your code, and the OfficeDocument property indicates where the Visual Studio .NET solution can find the associated Office document.
Figure 4** Project Properties **
In addition, creating the project sets up some debugging capabilities. Starting the project from within Visual Studio .NET loads Excel or Word along with your Office document, and runs your code as necessary. In order for Visual Studio to be able to debug your application, it has to know what application to load. When you create your project, Visual Studio .NET retrieves the location of the appropriate Office application and stores that location along with necessary command-line parameters. If you take a look at Figure 5 you'll see the project property pages, including both the application location and command-line arguments.
Figure 5** Set Location Properties **
Once you've started the debugging session (or simply loaded the Office document from Windows Explorer), how does the document know which assembly to load and what code to run? Unbeknownst to users, Visual Studio .NET has inserted two custom properties into the document you specified when you created the project. With your document loaded in Excel, you can select File | Properties to display the dialog box shown in Figure 6. On the Custom tab you'll find two properties named _AssemblyName0 and _AssemblyLocation0. These properties contain the path and file name of the associated assembly. By default, the assembly will be located in a folder with the same name as the project, with "_bin" appended. For example, if your project name is ChartDemo, the default location for the assembly will be the ChartDemo_bin folder.
Figure 6** ChartDemo Dialog **
Clearly, there's some piece of code that causes Excel or Word to load and run the assembly specified in the custom document properties. At document load time, if Excel (or Word) finds these special document properties, it fires up the unmanaged loader DLL for Visual Studio Tools for Office, OTKLOADR.DLL, which loads and links your managed assembly.
Once the host Office application has loaded the assembly you've created, how does it know what code to run? That's where the assembly attribute discussed previously comes into play—this attribute describes the fully qualified name of the class containing your Visual Basic .NET or C# code.
Once it has loaded the appropriate assembly, OTKLOADR. DLL passes the procedure in your class named _Startup to the CLR for execution. You don't create this procedure—the project template creates it and places it in the Generated initialization code region. Although you could modify this procedure, you probably should not. The loader also passes to _Startup a reference to the application (either the Word or Excel Application object) and a reference to the document itself (an instance of either Excel.Workbook or Word.Document). The _Startup method copies this information into local variables and hooks up any necessary event handling, as shown in Figure 7.
Figure 7 _Startup Method
Visual Basic .NET
' Required procedure. Do not modify. Public Sub _Startup(ByVal application As Object, _ ByVal workbook As Object) ThisApplication = CType(application, Excel.Application) ThisWorkbook = CType(workbook, Excel.Workbook) End Sub
C#
// Required procedure. Do not modify. public void _Startup(object application, object workbook) { this.thisApplication = application as Excel.Application; this.thisWorkbook = workbook as Excel.Workbook; openEvent = new Excel.WorkbookEvents_OpenEventHandler(ThisWorkbook_Open); thisWorkbook.Open += openEvent; beforeCloseEvent = new Excel.WorkbookEvents_BeforeCloseEventHandler( ThisWorkbook_BeforeClose); thisWorkbook.BeforeClose += beforeCloseEvent; }
The region also contains a public procedure named _Shutdown, which gets called automatically by the loader as the application closes the document:
' Visual Basic .NET ' Required procedure. Do not modify. Public Sub _Shutdown() ThisApplication = Nothing ThisWorkbook = Nothing End Sub // C# // Required procedure. Do not modify. public void _Shutdown() { thisApplication = null; thisWorkbook = null; }
From then on, your assembly uses the standard COM event marshaling mechanisms to react to events raised by the Word and Excel objects. Note that there's no defined interaction between VBA event handlers and managed event handlers. If your existing Word or Excel document includes VBA code that handles an event, adding Visual Basic .NET or C# code that handles the same event will work just fine, but you can't be sure of the order in which the event handlers will run. Make sure your applications don't rely on a particular order.
Of course, one of the main benefits of using managed code is the support for code access security, and code you write in your assembly takes advantage of the user's security policies. Although Visual Studio .NET created an appropriate security policy for your assembly when you created your project (granting full rights), this won't be the case when you deploy your solution. You have many options when distributing solutions created using Visual Studio Tools for Office. You can deploy both the Office document and the assembly to the client computer. You can deploy the Office document to the local computer and host the assembly on a shared location. You can even deploy both the Office document and the assembly to a shared location.
Each of these options has its pros and cons, including the ability to update the document and/or assembly when they're hosted in a shared location. In any case, when deploying solutions created using Visual Studio Tools for Office, you'll need to take into account the requirements of code access security, and allot time in your deployment schedule for setting up and testing various security scenarios.
Completing the Demonstration
Follow these seven steps to retrieve data from SQL Server™, add it to a worksheet, and then chart the data:
- Run the project. Within Excel, right-click on the main menu bar, and select the Control Toolbox toolbar.
- From this toolbar, select and draw a CommandButton control on the worksheet, covering cells A3 to A4.
- On the Control Toolbox, select the Properties button. In the Properties window, set the CommandButton control's Name property to cmdChart and its Caption property to Create Chart.
- On the Control Toolbox, deselect the Design Mode tool, placing the control in run mode. Close the Control Toolbox window. When you're finished, the worksheet should look like Figure 8.
- Close Excel, saving the changes to your workbook.
- Back in Visual Studio .NET, scroll to the top of the current file, and add the appropriate statement:
' Visual Basic .NET Imports System.Data Imports System.Data.SqlClient // C# using System.Data using System.Data.SqlClient;
- At the bottom of the current class, add the procedure that is shown in Figure 9. Then modify the value of the connectionInfo variable to reflect the location and security settings of your SQL Server installation.
Figure 9 RetrieveData
Visual Basic .NET
Private Sub RetrieveData() ' Modify this string for your own situation. Dim connectionInfo As String = _ "Server=.;Database=Northwind;Integrated Security=True" Dim commandText As String = "[Ten Most Expensive Products]" Dim connection As SqlConnection Dim command As SqlCommand Dim dataReader As SqlDataReader Try connection = New SqlConnection(connectionInfo) command = New SqlCommand(commandText, connection) command.CommandType = CommandType.StoredProcedure connection.Open() dataReader = _ command.ExecuteReader(CommandBehavior.CloseConnection) Dim rng As Excel.Range = ThisApplication.Range("A5") Dim i As Integer rng.CurrentRegion.Clear(); While dataReader.Read rng.Offset(i, 0).Value = dataReader(0) rng.Offset(i, 1).Value = dataReader(1) i += 1 End While Catch ex As Exception MessageBox.Show(ex.Message) Finally If Not dataReader Is Nothing Then dataReader.Close() End If If Not command Is Nothing Then command.Dispose() End If If Not connection Is Nothing Then connection.Dispose() End If End Try End Sub
C#
private void RetrieveData() { // Modify this string for your own situation. string connectionInfo = "Server=.;Database=Northwind;Integrated Security=true"; string commandText = "[Ten Most Expensive Products]"; SqlDataReader dataReader = null; using (SqlConnection connection = new SqlConnection(connectionInfo)) { using (SqlCommand command = new SqlCommand(commandText, connection)) { try { command.CommandType = CommandType.StoredProcedure; connection.Open(); dataReader = command.ExecuteReader(CommandBehavior.CloseConnection); Excel.Range rng = ThisApplication. get_Range("A5", Type.Missing); int i = 0; rng.CurrentRegion.Clear(); while (dataReader.Read()) { rng.get_Offset(i, 0).Value2 = dataReader[0]; rng.get_Offset(i, 1).Value2 = dataReader[1]; i += 1; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (dataReader != null) { dataReader.Close(); } } } } }
Figure 8** CommandButton **
Besides the standard ADO.NET code, the RetrieveData procedure clears all cells around cell A5, then loops through the rows returned by the SqlDataReader object, and inserts each of the two columns into cells in the worksheet. Note again the difference between the Visual Basic .NET and C# code here—the Offset property expects two parameters, and because C# cannot handle parameterized properties, the Excel PIA provides the get_Offset method that C# developers must use.
Your next task is to hook up an event handler for the CommandButton control embedded on the worksheet. That control is part of the Microsoft Forms package—a COM-based set of controls used by most of the Office products. In order to make it easier for you to hook up event handlers to the controls in this package, the Visual Studio .NET project template includes two overloaded versions of a FindControl method (one version accepts just the control name; the other accepts the control name and a document reference, in case the control isn't on the current sheet). You'll call this method to retrieve a reference to a control on a document.
Although the final release of Visual Studio Tools for Office will take care of the following details for you, if you're working with the public beta of the product, you may need to take a few extra steps in order to work with controls provided by the Microsoft Forms package. If you see an Imports/using statement referring to Microsoft Forms at the top of your module (indicating that you're working with a late beta or final release of the product), skip ahead a bit to the steps that describe adding a variable named button. Otherwise, add a reference to the Microsoft Forms 2.0 COM library in the Visual Studio .NET Solution Explorer window. (You may find two references to the library in the list. If so, select the first one.) Click Select and then click OK to dismiss the dialog box.
Scroll to the top of the code file, and add the following statement, depending on your chosen language (Visual Basic .NET or C#):
' Visual Basic .NET Imports MSForms = Microsoft.Vbe.Interop.Forms // C# using MSForms = Microsoft.Vbe.Interop.Forms;
Add the following declaration immediately following the declarations for the ThisApplication and ThisWorkbook variables:
' Visual Basic .NET Private WithEvents button As MSForms.CommandButton // C# private MSForms.CommandButton button;
Next, add the following procedure to the class:
' Visual Basic .NET Private Sub button_Click() Handles button.Click RetrieveData() End Sub // C# private void button_Click() { RetrieveData(); }
Add this code to the class's ThisWorkbook_Open procedure:
' Visual Basic .NET button = DirectCast(FindControl("cmdChart"), _ MSForms.CommandButton) // C# button = (MSForms.CommandButton) FindControl("cmdChart"); button.Click +=new MSForms.CommandButtonEvents_ClickEventHandler(button_Click);
Save and then run the project. Verify that clicking the button on the worksheet loads data from SQL Server and displays it on the worksheet, as shown in Figure 10. When you're finished, close Excel. You can save the workbook if you like.
Figure 10** Display Data **
Back in Visual Studio .NET, add the procedure in Figure 11 to the class, which will delete the chart if it already exists. As you can see by investigating this code, many methods in Excel (and Word) return Object types, meaning that you'll need to carefully cast the return values to the correct types. (You can avoid this hassle in Visual Basic .NET by not using Option Strict On, but that's not recommended. It makes it just too easy to create code that compiles but doesn't run correctly.)
Figure 11 Delete Chart
Visual Basic .NET
Private Sub DeleteExistingChart() Try Dim ws As Excel.Worksheet = _ DirectCast(ThisApplication.Worksheets(1), Excel.Worksheet) Dim charts As Excel.ChartObjects = _ DirectCast(ws.ChartObjects, Excel.ChartObjects) If charts.Count > 0 Then DirectCast(charts.Item(1), Excel.ChartObject).Delete() End If Catch ex As Exception MessageBox.Show(ex.Message, ex.Source) End Try End Sub
C#
private void DeleteExistingChart() { try { Excel.Worksheet ws = (Excel.Worksheet)ThisApplication.Worksheets[1]; Excel.ChartObjects charts = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); if (charts.Count > 0) { ((Excel.ChartObject)charts.Item(1)).Delete(); } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.Source); } }
Next, add the procedure in Figure 12 to the class, which will create the chart. Note how different the C# code is from the corresponding Visual Basic .NET code. Several of the Excel methods accept optional parameters which, as we discussed earlier, C# can't support. To work around this limitation, the C# code must pass the Type.Missing field for each optional parameter that's a reference type. (For value-type optional parameters, C# code must pass the actual default value.) In the DeleteExistingChart method, the WorkSheet.ChartObjects method lets you specify either an index of the chart you want to retrieve or no parameter to retrieve a ChartObjects collection. In C#, to retrieve the entire collection, you must pass Type.Missing for the optional parameter. The same rule applies when calling the Charts.Add method in the CreateChart method. In general, for any method that requires optional reference-type parameters, C# developers can pass Type.Missing.
Figure 12 Create Chart
Visual Basic .NET
Private Sub CreateChart() Try DeleteExistingChart() Dim cht As Excel.Chart = _ DirectCast(ThisApplication.Charts.Add(), Excel.Chart) cht.ChartType = Excel.XlChartType.xl3DBarClustered cht.Location(Excel.XlChartLocation.xlLocationAsObject, _ "Sheet1") cht = ThisApplication.ActiveChart cht.HasLegend = False cht.SetSourceData( _ ThisApplication.Range("A5").CurrentRegion, _ Excel.XlRowCol.xlColumns) DirectCast(cht.ChartGroups(1), Excel.ChartGroup). _ VaryByCategories = True Catch ex As Exception MessageBox.Show(ex.Message, ex.Source) End Try End Sub
C#
private void CreateChart() { try { DeleteExistingChart(); Excel.Chart cht = (Excel.Chart)ThisApplication.Charts.Add( Type.Missing, Type.Missing, Type.Missing, Type.Missing); cht.ChartType = Excel.XlChartType.xl3DBarClustered; cht.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1"); cht = ThisApplication.ActiveChart; cht.HasLegend = false; cht.SetSourceData(ThisApplication. get_Range("A5", Type.Missing).CurrentRegion, Excel.XlRowCol.xlColumns); ((Excel.ChartGroup)cht.ChartGroups(1)).VaryByCategories = true; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.Source); } }
Modify the Click event handler for the CommandButton control, adding a call to the CreateChart procedure:
' Visual Basic .NET Private Sub button_Click() Handles button.Click RetrieveData() CreateChart() End Sub // C# private void button_Click() { RetrieveData(); CreateChart(); }
Finally, save and run the project, click the button, and verify that you've managed to load the data and create the chart. Quit Excel when you're finished, saving the workbook if you like.
If you followed this procedure carefully, you should have created a chart that looks like Figure 13. Along the way, you saw how to take advantage of several of the Excel objects and how to hook up events to controls embedded on documents. You also saw some of the differences involved in coding Office objects from Visual Basic .NET and from C#. Although this example didn't take advantage of menus and toolbars available in Office, you can work with these items programmatically from within your managed code.
Figure 13** Bar Chart **
Of course, you haven't seen how code access security affects your project's ability to run. When you created your project, Visual Studio .NET added a code access policy that allowed your assembly to run from its current location. Copying it anywhere else on your hard drive should cause the code to fail. To verify the behavior of code access security, go to Visual Studio .NET and change the Assembly Link Location property to any other location. Now rebuild your application and run the project. You'll find that the code compiles and Visual Studio .NET loads Excel along with your workbook, but the code won't run. Figure 14 shows the alert you'll receive. Reset the Assembly Link Location property and repeat the previous steps to verify that your code runs again once the folder name has been reset.
Figure 14** Alert Message **
Code access security ensures that you cannot run your assembly from any location other than those that have been granted permissions to run. By default, Visual Studio .NET sets up a code access policy that allows your code to run from its development location. Change the location and your code won't run.
A full discussion of the deployment and security options, which are inexorably intertwined, would take a complete article on its own. For now, be aware that in order to deploy your applications to other users' computers, you'll need to define a security policy that allows your code to run. The behavior is the same for assemblies created using Visual Studio Tools for Office as it is for assemblies downloaded from the Internet or an intranet—the assembly is downloaded to the user's download cache and run from there. The same security issues apply for both types of assemblies.
Using Visual Studio Tools for Office will require some knowledge of the Office object models, a good knowledge of .NET security and deployment issues, and a business need. Finally, developers will be able to take advantage of all the power of the CLR and the .NET Framework from within Office applications. These new applications will look to users as if they were simply running Word or Excel. Updates can be performed automatically using no-touch deployment, and untrusted code can't run without an administrator's knowledge. These are just some of the features that provide the power, security, and flexibility of Visual Studio Tools for the Microsoft Office System.
For related articles see:
Installation and New Project QuickStarts for Visual Studio Tools for Office Beta
Visual Studio Tools for Office Frequently Asked Questions
Visual Studio Tools for Office
Understanding the Excel Object Model from a .NET Developer's Perspective
Understanding the Word Object Model from a .NET Developer's Perspective
For background information see:
Programming Microsoft Word 2002 and Excel 2002 with Microsoft Visual C#
Working with the Office XP Primary Interop Assemblies
Managed COM Add-In Shim
Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developer's Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001). Reach him at keng@mcwtech.com.
Brian A. Randell is a senior consultant with MCW Technologies and an instructor for DevelopMentor. Brian speaks regularly worldwide at VSLive! and Tech•Ed. He is the coauthor of Effective Visual Basic (Addison-Wesley, 2001).