Chapter 7 - Excel Basics

Chapter 7 - Excel Basics

Excel has some capabilities that aren’t found in FoxPro. Want a tabular report that the average end user can manipulate? Try Excel. Need some mathematical calculations that FoxPro doesn’t provide? Excel can do it. Want some graphs? Excel has an excellent graphing engine.

Working with Excel through Automation has a lot in common with automating Word—chalk one up for polymorphism. Unfortunately, there are more differences than similarities, since the two servers have different abilities and purposes.

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved

The object model

Like Word, Excel’s top-level object is the Application object, which has properties and methods that relate to Excel as whole. A number, like StartupPath, Version, and WindowState, are the same, while others, like Calculation, which determines when calculations occur, are specific to Excel. The Application object also provides access to all other objects in Excel.

The fundamental object in Excel is a Workbook. This corresponds to an XLS file. The Excel Application object keeps track of all its open workbooks in a Workbooks collection, and uses the ActiveWorkbook property to return a reference to the active Workbook object.

Each Workbook has two main collections, Worksheets and Charts, which represent the pages of the workbook and the graphs it contains, respectively. Workbook has ActiveSheet and ActiveChart properties containing references to the current Worksheet and Chart objects. Excel also provides a shortcut by offering ActiveSheet, ActiveChart, and ActiveCell properties at the Application level.

As with Word, the Excel Visual Basic Help file contains a live diagram of the object model. Figure 1 shows the portion of the object model diagram that describes the Worksheet object.

 

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved

Dj vu

If you’re reading this book from start to finish, as you read this chapter, you’ll say to yourself, "Gosh, I’ve read this before!" Office 2000 is object-oriented, and it’s polymorphic. Polymorphism literally means "many forms," and when applied to OOP, it means that different objects have properties and methods that behave consistently. To write a file to disk, you use the Save method, whether you are in Word, Excel, PowerPoint, or Outlook. This does not mean that the Save commands do exactly the same thing—there are different things that need to happen when a Word document is saved than when an Excel spreadsheet is saved. There may even be different parameters for each. However, you can be sure that the Save command will save your work to disk.

Figure 1 . The Excel object model. This diagram is available in the Help file and shows the hierarchy of available objects.

The benefit of polymorphism is that once you know how to do something in one tool, you know how to do it in the rest. In Office, this is usually true. However, polymorphism does include the ability for methods to accept different parameters, because different objects are exactly that—different. So you need to be aware that syntax can (and does) change between Office applications.

There are so many similarities between the Office applications that we could have written this chapter to say things like, "Just use the CreateObject() function as explained in the Word chapter, but use "Excel.Application" instead." We felt that it would be better to have a complete explanation for each application to keep you from having to flip back and forth in the book. It also makes it easier to explain the subtle syntax differences that exist between applications. After we get past the basics of opening and saving files, we get into enough application-specific features that it will seem less repetitive.

The benefit of polymorphism is that once you know how to do something in one object, you know how to do it in all of them. However, explaining similar concepts for each object makes for redundant text. Just think of this redundant text as a "feature."

 

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved