Understanding the Excel Object Model from a .NET Developer's Perspective
MCW Technologies, LLC
Microsoft® Visual Studio® Tools for the Microsoft Office System
Microsoft Office Excel 2003
Microsoft Visual Studio® .NET 2003
Summary: Discusses some of the objects provided by Microsoft Office Excel 2003 and explains how you can use them to create managed code solutions with Microsoft Visual Studio Tools for the Microsoft Office System. The focus is mainly on the Application, Workbook, Worksheet, and Range objects. Visual Basic .NET and Visual C# code examples demonstrate some of the properties, methods, and events of each object. (98 printed pages)
Download the ExcelObj.exe from the Microsoft Download Center.
Introduction The Application Object Members That Control the State and Display in Excel Members That Return Objects Members That Execute Actions Members That Handle File Manipulation Other Useful Members Application Events The Workbook Class Properties of the Workbook Class Working with Document Properties Working with Styles Working with Sheets Methods of the Workbook Class The Worksheet Class There's No Sheet Class Working with Protection Object Properties The Range Object Managing the Selection Referring to a Range in Code Using the Technology Working with Ranges Sorting Data within a Range What's Next?
Developers intending to take advantage of Microsoft® Visual Studio Tools for the Microsoft Office System and those wishing to simply use COM Automation to control Microsoft Office Excel 2003 applications will need to be able to interact with the objects provided by the Excel object model. Excel provides hundreds of objects with which you might want to interact, but you can get a good start on the object model by focusing on a very small subset of the available objects. These include:
Although it's impossible to quantify concretely, a large percentage of the work you'll do with Excel centers around these four classes and their members. In this document, you'll learn how to take advantage of each of these classes, and will be introduced to some of the properties, methods, and events of each. You'll also find examples you can try, demonstrating some of the features of each object.
Tip In general, developers who use Microsoft Visual Basic® .NET have an easier time working with Microsoft Office objects than do developers who use Microsoft Visual C#® for one important reason: Visual Basic for Applications (VBA) methods often include optional parameters, and Visual Basic .NET supports optional parameters. C# developers will find that they must supply a value for each and every optional method parameter, whereas Visual Basic .NET developers can simply used named parameters to supply only the values they need. In addition, C# doesn't support properties with parameters other than indexers, yet many Excel properties accept parameters. You'll find that properties such as the Application.Rangeproperty, available in VBA and Visual Basic .NET, require separate accessor methods for C# developers (the get_Range method replaces the Range property.) Watch for differences between the languages like these throughout this document.
For the most part, you'll find that the Excel object model directly emulates its user interface. It wouldn't be too hard to guess that the Application object provides a wrapper around the entire application, and each Workbook object contains a collection of Worksheet objects. From there, the major abstraction representing cells is the Range object, which allows you to work with individual cells or groups of cells.
Each of the following sections describes one of the major Excel objects, picking specific members of the object for demonstration. With hundreds of objects to explore, it's impossible to dig into all of them here: you'll get enough of the flavor of the object models to be able to get started, and to use the Excel online help for more details.
Tip Throughout this article, you'll see many uses of the DirectCast and CType methods. The reason for this is that the sample project has its Option Strict setting on—this means that Visual Basic .NET requires strict type conversions. Many Excel methods and properties return Object types or rely on late binding: For example, the Application.ActiveSheet property returns an Object, as opposed to a Worksheet, as you might expect. Therefore, to be as rigorous about conversions as possible, the sample has enabled Option Strict, and handles each type conversion explicitly. (Without using Option Strict in Visual Basic .NET, it's possible that you'll write code that compiles fine, but fails at run time. That's the point of Option Strict—it makes it much less likely that an invalid conversion at run time will cause an exception.) If you're a C# developer reading this document, you'll likely appreciate this decision.
This white paper references the sample project, ExcelObjectModel.sln. This project contains an Excel workbook and associated Visual Basic .NET code. Not every sample shown in this paper appears in the example project, but any that involve more than a line or two of code have been placed into the workbook, with a hyperlink within the project set up to call the code.
Tip In an article of this limited size, it's not possible to document each and every object or member. It's not even possible to mention a small fraction of the classes. Your best tool for investigating any large object model is the Object Browser window, where you can find a list of each of the classes, along with the members of that class. You'll find that many of the members discussed in this document apply to many different classes: for example, the PrintOut method discussed in the context of the Sheets collection applies just as well to the Chart, Worksheet, Range, and other objects. The point of this document is to give you an idea of what's available, and leave the rest to your inquisitive nature.
The Application Object
The Excel Application object represents the Excel application itself. That may sound obvious, but the Application object exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance. The Application object provides many members, many of which you'll never need to investigate, and others of which will be crucial to the correct behavior of your application. You can break these members down into the following categories:
Members that control the state and display in Excel
Members that return objects
Members that execute actions
Members that handle file manipulation
The following sections introduce each of these groups, along with code examples demonstrating of some of the members.
Members That Control the State and Display in Excel
The Application object provides a large set of properties that control the general state of Excel. Table 1 lists a subset of the state-related Application object properties.
Table 1. Some of the Application properties that control the state of Excel
Gets or sets the ability to edit cells directly in place. If False, you can only edit cells in the formula bar.
If True, all numeric values use the FixedDecimalPlaces property to determine the number of decimal places; otherwise, FixedDecimalPlaces property is ignored (the default value is False).
Determines the number of decimal places to be used for numeric data if the FixedDecimal property is True.
Gets or sets the ability of the user to interact with Excel via the keyboard and mouse; if you set this property to False, make absolutely sure you set it back to True in your exception handler. Excel won't reset it for you.
If True, the selection moves to the next cell when you press Enter; the default value is True.
xlDirection (xlDown, xlToLeft, xlToRight, xlUp)
Indicates the direction to move after pressing Enter, if the MoveAfterReturn property is True. The default value is xlDown.
If True, Excel updates its screen after each method call. To save time, and to make your application look more professional, you can turn off the display while your code is running. Make sure you reset this property to True again once you're done. Excel won't reset it for you.
Gets or sets the number of sheets Excel automatically places in new workbooks
Gets or sets the name of the default font in Excel; doesn't take effect until you restart Excel.
Gets or sets the size of the default font in Excel; doesn't take effect until you restart Excel.
Returns the complete path of the folder containing the Excel startup add-ins.
Returns the complete path of the folder containing templates; this value represents one of the Windows special folders.
Of all the properties listed in Table 1, the one you're most likely to use is the ScreenUpdating property. By taking advantage of this property, you can not only make your Excel applications look more professional, you can make them run faster—updating the display after each modification can exact a huge toll on your code, especially when programmatically filling in a large range. It's important, however, that you always set this property when you're done with your work, because Excel won't reset it for you. Therefore, you'll need to always use code similar to the following fragment when using the ScreenUpdating property, taking advantage of .NET exception handling to ensure that screen updating resumes:
' Visual Basic
ThisApplication.ScreenUpdating = False
' Do your work that updates the screen.
ThisApplication.ScreenUpdating = True
ThisApplication.ScreenUpdating = false;
// Do your work that updates the screen.
ThisApplication.ScreenUpdating = true;
The Application object also provides a group of properties that control the display in Excel. You can modify any of these to change what users see on the screen. Table 2 lists a subset of the available display options.
Table 2. Some of the Application properties that control the appearance of Excel
If True (the default value), Excel displays warning messages while your code runs, as necessary--when deleting a sheet, for example. Set to False to bypass warnings. Excel acts as if you had selected the default value for each alert.
If True (the default value), Excel displays the standard formula bar for editing cells; set to False to hide the editing bar.
If True, Excel runs in full-screen mode (which has a different effect from simply maximizing the Excel window); the default value is False.
Tip Just as with the ScreenUpdating property, it's important to reset the DisplayAlerts property. Because Excel won't reset this property for you, and with it set to False, Excel won't prompt you to save workbooks before you close them; not carefully resetting the DisplayAlerts property can cause you to lose data if you're not careful.
Members That Return Objects
Many of the Application object's properties return other objects. Because the standard Microsoft Office project template supplied by Visual Studio® .NET contains only the ThisApplication and ThisWorkbook objects, you'll generally need to take advantage of the object members of the Application class to reference the other objects provided by Excel. You can use these members to retrieve a reference to a particular child object using a property like ActiveWindow, or to a collection of available objects using a property like Charts. Table 3 lists a subset of the object-returning properties of the Application object.
Table 3. A subset of the available object-returning properties of the Application object.
Returns a reference to the currently active cell in the active window (the window that's on top). If there's no active window, this property raises an error.
Returns a reference to the currently active chart. An embedded chart is only considered active when it's selected or activated.
Returns a reference to the active sheet in the active workbook.
Returns a reference to the active window (the window that's on top); returns Nothing if there are no active windows.
Returns a collection of Sheet objects (the parent for both Chart and Worksheet objects) containing references to each of the charts in the active workbook.
Returns the selected object within the application. Might be a Range, a Worksheet, or any other object—also applies to the Window class, in which case the selection is generally a Range object. If no object is currently selected, returns Nothing.
Returns a collection of Sheet objects containing references to each of the sheets in the active workbook.
Returns a collection of Workbook objects containing references to all the open workbooks.
You'll most often interact with the Workbooks property of the Application class. This property allows you to iterate through the open workbooks, open or create a new workbook. The following section describes the behavior of this property.
The Workbooks Collection
The Workbooks collection makes it possible to work with all the open workbooks, create a new workbook, and import data into a new workbook. The following list works through some of the main uses you'll find for the Workbooks collection:
Create a new workbook using code like the following (you can also specify the name of a workbook template as a parameter to the Add method):
' Visual Basic
Dim wb As Excel.Workbook = ThisApplication.Workbooks.Add()
Close all open workbooks. Unlike most collections, this one allows you to close all its members at once. The following method call closes all open workbooks:
' Visual Basic
Open an existing workbook, using the Open method of the Workbooks collection. In its simplest form, you'll use the Open method like you'll see in the following fragment. The Open method supplies a large number of optional parameters that affect its behavior in specific circumstances, but you generally won't need to use the optional parameters:
Tip If you're a C# developer, get used to seeing references to the Type.Missing value in method calls. Because the Excel object model was written with VBA in mind, many of its methods accept optional parameters—sometimes up to 30 optional parameters. You'll need to use many instances of the Type.Missing value, or supply the specific default value for each individual parameter.
Open a text file, database, or XML file as a workbook using the OpenText, OpenDatabase or OpenXml method. These methods provide a great deal of flexibility, and even simple coverage would take more space than can be allotted here. Your knowledge of the existence of these methods is enough for now—if you need to load any of these items into Excel, you can investigate these items in more detail. You can use code like the following to load a text file as a workbook, using commas as delimiters, starting on the third row in the text file:
Refer to individual workbooks. You can index into the Workbooks collection using either integers (indicating position within the collection) or workbook names. If you want to refer to a workbook by name, however, you'll need to be careful how you refer to it: you must use the name you see in the title bar, which doesn't include the ".xls" extension until you've saved the file:
' Visual Basic
Dim wb As Excel.Workbook = ThisApplication.Workbooks(1)
' Before Book1 is saved:
wb = ThisApplication.Workbooks("Book1")
' After Book1 is saved:
wb = ThisApplication.Workbooks("Book1.xls")
Excel.Workbook wb = ThisApplication.Workbooks;
// Before Book1 is saved:
wb = ThisApplication.Workbooks["Book1"];
// After Book1 is saved:
wb = ThisApplication.Workbooks["Book1.xls"];
Tip When you refer to a particular workbook, you're taking advantage of the default indexer, the Item property. In addition to the Item property, the Workbooks collection, like all collections provided by Microsoft Office, includes a Count property that returns the number of items, (Workbooks in this case) in the collection.
Members That Execute Actions
The Application object provides a number of methods that allow you to execute actions from recalculating the current data to undoing changes to data. The following list enumerates some of the Application object's methods and describes each with a small example. The samples for this section appear on the Application Object sheet of the sample workbook: