Understanding the Excel Object Model from a. Net developer's Perspective

Understanding the Excel Object Model from a .NET Developer's Perspective

 

Ken Getz

MCW Technologies, LLC

April 2003

Applies to:
    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.

Contents

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?

Introduction

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:

• 
  Application
  
• 
  Workbook
  
• 
  Worksheet
  
• 
  Range
  

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.Range property, 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
  
• 
  Other members
  

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

Property	Type	Description
Cursor	XlMousePointer (xlDefault, xlIBeam, xlNorthwestArrow, xlWait)	Gets or sets the appearance of the mouse pointer.
EditDirectlyInCell	Boolean	Gets or sets the ability to edit cells directly in place. If False, you can only edit cells in the formula bar.
FixedDecimal	Boolean	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).
FixedDecimalPlaces	Long	Determines the number of decimal places to be used for numeric data if the FixedDecimal property is True.
Interactive	Boolean	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.
MoveAfterReturn	Boolean	If True, the selection moves to the next cell when you press Enter; the default value is True.
MoveAfterReturnDirection	xlDirection (xlDown, xlToLeft, xlToRight, xlUp)	Indicates the direction to move after pressing Enter, if the MoveAfterReturn property is True. The default value is xlDown.
ScreenUpdating	Boolean	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.
SheetsInNewWorkbook	Long	Gets or sets the number of sheets Excel automatically places in new workbooks
StandardFont	String	Gets or sets the name of the default font in Excel; doesn't take effect until you restart Excel.
StandardFontSize	Long	Gets or sets the size of the default font in Excel; doesn't take effect until you restart Excel.
StartupPath (read-only	String	Returns the complete path of the folder containing the Excel startup add-ins.
TemplatesPath (read-only)	String	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

Try

ThisApplication.ScreenUpdating = False

' Do your work that updates the screen.

Finally

ThisApplication.ScreenUpdating = True

End Try

// C#

try

{

ThisApplication.ScreenUpdating = false;

// Do your work that updates the screen.

}

finally

{

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

Property	Type	Description
DisplayAlerts	Boolean	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.
DisplayFormulaBar	Boolean	If True (the default value), Excel displays the standard formula bar for editing cells; set to False to hide the editing bar.
DisplayFullScreen	Boolean	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.

Property	Type	Description
ActiveCell	Range	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.
ActiveChart	Chart	Returns a reference to the currently active chart. An embedded chart is only considered active when it's selected or activated.
ActiveSheet	Object	Returns a reference to the active sheet in the active workbook.
ActiveWindow	Window	Returns a reference to the active window (the window that's on top); returns Nothing if there are no active windows.
Charts	Sheets	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.
Selection	Object	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.
Sheets	Sheets	Returns a collection of Sheet objects containing references to each of the sheets in the active workbook.
Workbooks	Workbooks	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()
  
• 
  
  
• 
  // C#
  
• 
  Excel.Workbook wb = ThisApplication.Workbooks.Add(Type.Missing);
  
• 
  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
  
• 
  ThisApplication.Workbooks.Close()
  
• 
  
  
• 
  // C#
  
• 
  ThisApplication.Workbooks.Close();
  
• 
  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:
  
• 
  ' Visual Basic
  
• 
  Dim wb As Excel.Workbook = _
  
• 
  ThisApplication.Workbooks.Open("C:\YourPath\YourWorkbook.xls")
  
• 
  
  
• 
  // C#
  
• 
  Excel.Workbook wb = ThisApplication.Workbooks.Open(
  
• 
  "C:\\YourPath\\Yourworkbook.xls",
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing);
  

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:
  
• 
  ' Visual Basic
  
• 
  Dim wb as Excel.Workbook = _
  
• 
  ThisApplication.Workbooks.OpenText("C:\Test.txt", StartRow:=3, _
  
• 
  DataType:=xlDelimited, Comma:=True)
  
• 
  
  
• 
  // C#
  
• 
  Excel.Workbook wb =
  
• 
  ThisApplication.Workbooks.OpenText("C:\\Test.txt",
  
• 
  Type.Missing, 3, Excel.XlTextParsingType.xlDelimited,
  
• 
  Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
  
• 
  Type.Missing, Type.Missing, Type.Missing, True,
  
• 
  Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing);
  
• 
  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")
  
• 
  
  
• 
  // C#
  
• 
  Excel.Workbook wb = ThisApplication.Workbooks[1];
  
• 
  // 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:

•