WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
One of the big reasons for this is that Office 2003 comes with Primary Interop Assemblies (PIAs) that allow managed code (VB.NET, C#, or managed C++) to call Office code. Just about every aspect of the object model for Word and Excel (and to a lesser extent, other Office 2003 products) is exposed through these assemblies. You don't have to deal with COM to control Office products, and unlike a Runtime Callable Wrapper that you could generate for yourself, the interop assemblies provided with Office are tweaked a little to make them more efficient and performant. All you need to do to use them is make sure they're installed on your machine (and the machines where you plan to deploy the solutions you're creating). To do that, you need to know the secret code phrase ".NET programmability support." Installing that (while installing Office products or later, through the Add/Remove programs component in Control Panel) will put these interop assemblies into the GAC. Make sure you install the PIAs for each of the Office products.
Visual Studio Tools for Office
Once you've got the interop assemblies on your machine, you could just write any managed application you want, and use the interop assemblies to leverage Office products—Word as a spell checker, Excel as a chart-creator—in your everyday applications. But you don't have to stop there. Visual Studio Tools for Office, or VSTO (Visto seems to be the most popular pronunciation) serves much the same purpose as VBA, but I find it much more developer-friendly. It works with Visual Studio to let you create assemblies that are linked to Word or Excel documents. The code is not inside the document, but it's connected: in fact, a Word document on a user's desktop can use an assembly on a departmental server, which can make upgrading that assembly remarkably simple.
VSTO is not a free download, though it is available online for MSDN Subscribers. The retail price is $499, and if you have a developer edition of an earlier version of Office, or you have Visual Studio or Visual Basic 2002 or earlier, you can upgrade to VSTO for $199 retail. Installation order matters: You want Visual Studio.NET 2003 first, so that you have the framework and the GAC. Then, install Office 2003 (or adjust your installation) so that the interop assemblies are put into the GAC. Finally, install VSTO.
Once you've installed VSTO, you have extra choices available to you in the New Project dialog of Visual Studio 2003: a new node called Microsoft Office System projects. You can work in VB.NET or C#, and each language supports the same three projects types: Excel workbook, Word document, and Word template.
A First VSTO Project
Let me show you what happens when you create a VSTO Excel project. It's very similar when you're working with Word. You're asked whether you need a new companion document or if you want to attach this assembly to an existing document. By default, a document is created for you in the project folder. You're also given a ThisWorkbook.vb file with one class defined, called OfficeCodeBehind. Take care if you change that name, because the name is used in this attribute:
<Assembly: System.ComponentModel.DescriptionAttribute( "OfficeStartupClass, Version=1.0, Class=First_Excel.OfficeCodeBehind")>
This attribute is what tells Office the name of the class that implements the various event handlers forming the core of a VSTO project. You're given two: ThisWorkbook_Open() and ThisWorkbook_BeforeClose(). You're also given two variables: ThisWorkbook and ThisApplication. From here you can build whatever you need in the application.
Tapping the Power of .NET
For example, you can add lines to ThisWorkbook_Open() that inject content into the workbook, using any of the power of a .NET application to get the content. Call a Web service, look in a database, make whatever calculations you like. Consider this code:
Dim sheet As Excel.Worksheet = ThisWorkbook.ActiveSheet sheet.Cells(4, 1).CurrentRegion.ClearContents() sheet.Cells(4, 1) = "First Name" sheet.Cells(4, 2) = "Last Name" sheet.Cells(4, 3) = "Extension" Dim conn As New SqlConnection("initial catalog=Northwind; data source=(local); Integrated Security=SSPI") conn.Open() Dim sql As String = "select firstname, lastname, extension from employees" Dim cmd As New SqlCommand(sql, conn) Dim dr As SqlDataReader = cmd.ExecuteReader() Dim row As Integer = 5 While dr.Read sheet.Cells(row, 1) = dr("firstname") sheet.Cells(row, 2) = dr("lastname") sheet.Cells(row, 3) = dr("extension") row += 1 End While dr.Close() conn.Close() sheet.Cells(4, 1).CurrentRegion.Columns.AutoFit()
(I added an Imports System.Data.SqlClient statement to make this code simpler to read. The reference to System.Data was added by the wizard that generated the project.) This code uses ADO.NET to go into the Northwind database and get some employee information, then writes that information into the spreadsheet to which this assembly is attached. This is quite simple to read: ThisWorkbook represents the workbook associated with the running code. ThisWorkbook.ActiveSheet is the active sheet within the workbook. The Cells property of a worksheet represents the cells, and it's indexed by two integers representing the row and the column, respectively. To test the code, you simply run the project in Visual Studio: your document is loaded for you. Here's how the sheet looks after that code runs:
At this point, the user is in Excel and has all the power of Excel available. Want to sort that information? Use the Data, Sort command. Want to make a chart of it? Not sure why you'd want to chart phone numbers, but Excel makes it simple to do. Want to print it, or save it and e-mail it to someone else? Go right ahead. The wonderful thing about this from a developer's point of view is that I don't have to write any of the code for it, nor teach the user how to do it, nor document it in any way. I just write my 25 lines of code to get the information from the database into the spreadsheet, and then I can walk away. Excel is the user interface, and it's already written for me.
Tapping the Power of Office
Does this mean VBA is history? No, in fact VSTO works nicely with VBA. There are a lot of advantages to VSTO: You're working in managed code, you can use ADO.NET and Web services, and you have all the power of Visual Studio, including debugging in a familiar environment. But, VBA will still be the right choice for some pieces of the application. And, VSTO code can interact with VBA; for example, with VBA buttons.
I added these variables to the OfficeCodeBehind class:
Private WithEvents btnLastName As MSForms.CommandButton Private WithEvents btnFirstName As MSForms.CommandButton
Then, I added these lines to the end of ThisWorkbook_Open():
btnLastName = FindControl("Sort by Last Name") If btnLastName Is Nothing Then btnLastName = DirectCast(sheet.OLEObjects, _ Excel.OLEObjects).Add(ClassType:="Forms.CommandButton.1", _ Link:=False, DisplayAsIcon:=False, Left:=10, Top:=5, _ Width:=100, Height:=20).Object() btnLastName.Caption = "Sort by Last Name" End If btnFirstName = FindControl("Sort by First Name") If btnFirstName Is Nothing Then btnFirstName = DirectCast(sheet.OLEObjects, _ Excel.OLEObjects).Add(ClassType:="Forms.CommandButton.1", _ Link:=False, DisplayAsIcon:=False, Left:=115, Top:=5, _ Width:=100, Height:=20).Object() btnFirstName.Caption = "Sort by First Name" End If
This adds two VBA buttons to the surface of the Excel sheet, above the data injected from the Northwind database. The code grabs the collection of OLE objects (such as VBA buttons) already in the sheet and adds a command button to the collection.
Because the buttons were added with the VB WithEvents keyword, I can catch and handle their events, such as being clicked. The dropdowns at the top of the code editing window provide the easiest way to do this: select the variable name from the left dropdown and the event from the right.
The handler code can leverage the power of Excel:
Private Sub btnFirstName_Click() Handles btnFirstName.Click ThisWorkbook.ActiveSheet.Cells(4, _ 1).CurrentRegion.sort(Key1:=ThisWorkbook.ActiveSheet. Cells(4,_ 1).CurrentRegion.Columns(1),_ Order1:=Excel.XlSortOrder.xlAscending, _ Orientation:=Excel.XlSortOrientation.xlSortColumns, _ Header:=Excel.XlYesNoGuess.xlYes) End Sub Private Sub btnLastName_Click() Handles btnLastName.Click ThisWorkbook.ActiveSheet.Cells(4, _ 1).CurrentRegion.sort(Key1:=ThisWorkbook.ActiveSheet. Cells(4, _ 1).CurrentRegion.Columns(2), _ Order1:=Excel.XlSortOrder.xlAscending, _ Orientation:=Excel.XlSortOrientation.xlSortColumns, _ Header:=Excel.XlYesNoGuess.xlYes) End Sub
Both handlers do essentially the same thing. The work starts at Cells(4,1), which is the top left corner of the table of names and extensions. The CurrentRegion method selects the filled-in cells all around the starting point, using the same rules that apply when you hold Ctrl and click. Once you have that region, the sort method, not surprisingly, sorts it. The arguments to the sort method are the same things you fill out on the dialog when you sort interactively in Excel: the key (column 1 of the selected area for first name, column 2 of the selected area for last name), the sort order (note the handy enumeration), whether you're sorting by rows or columns, and whether or not there's a header row.
Run the application and click the button of your choice and you'll see the data sorted on the fly. Not by reissuing the SQL Select statement, but just by using what Excel already knows how to do. That's a hallmark of the best VSTO applications: Use the power of managed code and Visual Studio .NET for what it does best, don't forget all the things the Office products already know how to do, and try not to sound too smug about all the code you didn't have to write.
Security and Deployment
One more thing: Yhen you're ready to give your solution to a user, there are four things you need to do: give them the Office document, give them the assembly, connect the document to the assembly, and change the security settings on their machine to permit the assembly to load. By default, Office won't load any assemblies; you must trust them one-by-one. When you create the solution, the wizard adjusted permissions for you; you'll have to do that by hand when you deploy the solution. You can simulate this by simply moving the assembly on your own machine.
Open the document—you don't need to run the project in Visual Studio, you can just browse to the .xls file and double-click it. Choose File, Properties. On the Custom tab you will find _AssemblyLocation0 and _AssemblyName0. Change _AssemblyLocation0 to another path, such as C:\, and click Modify. Dismiss the property dialog and save the document, then close it. Open the document immediately and you'll get an error message; not surprisingly, the assembly cannot be found. Copy the DLL from the project's bin folder up to C:\ and try opening the document again. Now you get a security error. The error urges you not to change security settings, but because you are the "developer who wrote the custom macros," you are going to change the security settings.
From the Start menu, get to Administrative Tools (the route there is different for XP and 2000), then Microsoft .NET Framework 1.1 Configuration. On the left, expand Runtime Security Policy. Expand the User node, then Code Groups, then Office Projects. The wizard puts all the Office projects under this node, and you might as well tag along. Right-click Office projects and choose New. Give your code group a name; the wizard uses the full path to the folder as the name, but you can choose something more intelligent. Click Next. For the condition, choose URL: you're going to identify the assembly by its location. Enter "file://c:/*" to identify the root of C. Click Next and use the existing permission set called Execution to indicate that this assembly may execute. Click Next and then Finish. You're not quite finished: having given execution permission to the folder, you need to give more permissions to the assembly itself. For example, this column's sample needs to make a database connection.
Still in the Configuration tool, right-click the code group you added to represent the folder, and choose New. I usually give this child group the same name as its parent with DLL tacked on the end. The condition type is URL, and you enter the full path to the DLL itself. Give the existing permission set FullTrust to this assembly, and finish the wizard.
Now, you're in a position to deploy your solutions elsewhere, and you know how to adjust security to make sure they will run in the new location. So go ahead, get yourself a copy of VSTO and start letting Word and Excel be your user interface. If your users are already using Office products, you can save an enormous amount of development time. And, as you've seen, you can tap that power from within your own code as well. So why not give it a try?
About the Author
Kate Gregory is a founding partner of Gregory Consulting Limited (www.gregcons.com). In January 2002, she was appointed MSDN Regional Director for Toronto, Canada. Her experience with C++ stretches back to before Visual C++ existed. She is a well-known speaker and lecturer at colleges and Microsoft events on subjects such as .NET, Visual Studio, XML, UML, C++, Java, and the Internet. Kate and her colleagues at Gregory Consulting specialize in combining software develoment with Web site development to create active sites. They build quality custom and off-the-shelf software components for Web pages and other applications. Kate is the author of numerous books for Que, including Special Edition Using Visual C++ .NET.