7.4 Managing workbooks and worksheets | Microsoft Office Automation with Visual FoxPro

Managing workbooks and worksheets

Time for some clarification that seems to get new Automation programmers: a collection object is indicated by a plural noun, and an object in the collection is the same noun in singular form. Workbooks (with an "s") is a collection of all the open Workbook (no "s") objects. A Workbook (no "s") object corresponds to an XLS file. Each Workbook has a collection called Worksheets (with an "s"). Each Worksheet (no "s") is displayed as a tab, with the tab headings near the bottom of the window.

Opening a new workbook

Since Excel opens without an available workbook, you need to add a new one or open an existing one. To create a new Workbook object, use the Add method of the Workbooks collection. The number of Worksheets a Workbook contains when added is controlled by the Application object s SheetsInNewWorkbook property (set manually in Excel by selecting Tools|Options from the menu, choosing the General tab, and using the Sheets in New Workbook spinner). Setting this property affects all new workbooks added after the change (it does not affect those already open). Issuing the following command before opening a new workbook gives it four worksheets:

oExcel.SheetsInNewWorkbook = 4

Since users control this property, prepare your code to handle differences in this setting. Don t assume that the user hasn t played with the defaults; use the SheetsInNewWorkbook property to ensure you have enough worksheets.

Open a new worksheet using the Add method. It takes one optional parameter, which specifies a template file.

oWorkbook = oExcel.Workbooks.Add()

The variable oWorkbook points to your new workbook. There are several other ways to reference this Workbook object. One is to use the Application s ActiveWorkbook property: oExcel.ActiveWorkbook. Any time you add a new Workbook, it automatically becomes the active workbook. Another way to access this Workbook is through its index in the Workbooks collection. It is the first workbook opened, so it can be accessed as oExcel.Workbooks[1]. The Workbooks collection provides an alternate reference, which is the workbook name (appropriately stored in the Name property of a Workbook object).

The default name of a workbook is "Book" concatenated with the number corresponding to the order in which it was opened. "Book1" is the first Workbook object s Name property. The Name property is read-only. The only way to set it is to use the SaveAs method (see the section "Saving the workbook" later in this chapter).

Opening an existing workbook

There are two ways to open an existing workbook. The Open method assumes you already have a reference to the Excel Application object and will open an existing file. Visual FoxPro s GetObject() function gives you greater control over the visibility of the Workbook and does not require an Excel Application object reference.

The Open method of the Workbooks collection opens an existing workbook, given the filename:

oWorkbook = oExcel.Workbooks.Open("c:\my documents\sample.xls")

The filename is the only required parameter for the Open method. There are 12 other parameters (fortunately, all optional) to control whether links should be updated, provide a password, describe the layout of a text file to import, and other features. We invite you to check out the Help file for more information on these features.

An alternative way to open a workbook is to use the GetObject() function. GetObject() has two forms. In the first, you pass it the filename to open:

oWorkbook = GetObject("c:\my documents\sample.xls")

GetObject() returns a reference to the Workbook object (not the Application object, as with CreateObject()). If Excel was not running (or was running but not visible), you need to make the application visible to see it. To get to the Application object, use the Workbook s Application property, which references the Application object:

oWorkbook.Application.Visible = .T.

The new workbook is still not visible. Remember, Automation doesn t assume anything! The Workbook object is available to automate; its window is hidden. Again, there s a performance advantage to running all the Automation commands and then displaying it. To see the window, use the Window s Activate method (the example assumes that the window you re activating is the first window opened):

oWorkbook.Windows[1].Activate()

There are times when you want to hide the window while your Workbook is building. Assuming it is the active window, issue the oWorkbook.ActiveWindow.Close method.

The GetObject() function has an alternate syntax that allows you to prevent multiple occurrences of an object. If you are programming Office 97, this may be an important issue. You may want to use an already open instance of Excel to avoid using more memory to open another instance. In Office 2000, one of the new features is that there is only one instance of the application; the Office application itself provides better memory management. However, the GetObject() syntax still works for Excel 2000 (and opens it if it is not already open). Issuing the following opens Excel and returns a reference to the Application:

oExcel = GetObject("", 'Excel.Application')

Note that the syntax to open a filename returns a reference to the Workbook.

If there are no instances of Excel 97 running, issuing GetObject(, 'Excel.Application') produces the following error: "OLE error code 0x800401e3: Operation unavailable." See the "Print Preview" section later in this chapter for an example of detecting if Excel is already running.
Saving the workbook

As you look through the list of methods for the Workbook object, you find references to Save and SaveAs. You would think that the Save method would be used to save any old workbook, and SaveAs would save it in a different format. However, in Excel (and PowerPoint, but not Word), the Save method takes no parameters. It uses a default filename consisting of the following:

The default file location. Users can change this from the Tools|Options menu item, using the General Tab, by specifying the Default File Location. In Automation code, you check (or set) the DefaultFilePath property of the Application object. This setting is saved when you close Excel; be sure to restore the user s settings if you change it.

The Workbook s Name property. Remember, this defaults to "BookX" and is read-only.

The XLS extension.

So, by default, the Save method saves the spreadsheet as something like "C:\My Documents\Book1.XLS." To name it something meaningful, use the SaveAs method, which allows you to specify a fully qualified filename. SaveAs also sets the Name property to the name part of the filename (not the path or the extension). SaveAs takes many optional parameters for such things as passwords, adding the file to the Most Recently Used list, backups, and so on. See the Help file for those parameters.

The most important parameter is the first one, which specifies the filename. Remember to fully qualify the path; if you don t, the default file location is used.

oWorkbook.SaveAs('c:\ExcelData\FirstTest.XLS')

An optional second parameter is the file format. If it s not specified, the default is the version of Excel in use (or, if the file was saved previously, the format in which the file was saved). The Help file lists 41 types (for Office 2000), including multiple versions for Lotus, dBASE, and Excel; HTML; DIF, SYLK and CSV, and others. These files require converters installed with Office; if the user elected not to install a particular converter, your error handler should be ready to deal with the resulting error. Some file formats save only the current worksheet; others save the entire workbook. The Excel Help file has excellent information if you look up the keyword "Save_As." In particular, the topic "File format converters supplied with Microsoft Excel" has valuable information about each of the file formats. In the VBA Excel Help file, there s a particularly good topic, "Saving Documents as Web Pages."

Avoiding Excel s user dialogs

When using SaveAs, saving to a file that already exists raises a user dialog, shown in Figure 2 (of course, if the user has the Office Assistant on, it will explain the error). If the application is not Visible, the message box will pop up. If there is a visible window, both the window title bar and the button on the taskbar will flash. When the Excel window is minimized (or obscured) and the taskbar is in AutoHide mode (where you have to run the mouse down to the bottom of the screen), it appears to the user that you have a hang. If the user does see the flashing and selects Excel, then presses either "No" or "Cancel," the following error will result: "OLE IDispatch exception code 0 from Microsoft Excel: Unable to get the SaveAs property of the Workbook class." Be prepared to handle this error (#1428). Better yet, prevent this error from happening.

Figure 2. The dialog that s raised when overwriting a file. Note that the user has three choices, and only "Yes" lets your program proceed without errors.

There are a couple of steps you can take to thwart this error. First, check to see whether oWorkbook.Name is different from the first name of the file (no path, no extension). Use VFP s JUSTSTEM() function to return the first name of the file. If the two differ, use the SaveAs property to set the filename (and the Name property is then set to the first name of the file). If they are the same, use the Save command, which assumes you want to overwrite the file. Second, before you use the SaveAs feature, be sure that the filename in question doesn t exist:

MyFile = "c:\ExcelData\MyFile.XLS"

* Use JUSTFNAME() to calculate this in VFP 6.0;

* or the FoxTools function in previous versions

MyFileJustName = JustFName(MyFile)

* Determine whether to use SaveAs or Save

IF oWorkbook.Name <> MyFileJustName

* IF the file already exists, delete it

IF FILE(MyFile)

* Warning: you may be deleting a file that doesn't

* have anything to do with the situation at hand. Be

* sure your app is aware of this.

ERASE (MyFile)

ENDIF

* Save it without fear of the user dialog box

oWorkbook.SaveAs(MyFile)

ELSE

* Save it, since it's already been saved with SaveAs

oWorkbook.Save()

ENDIF

Working with worksheets

Each Workbook can contain many Worksheets in the Workbook s Worksheets collection. Worksheets store the data. The Worksheets collection object (it s plural, so it s a collection) stores as many Worksheet (singular) objects as Excel can handle (in Excel 2000, the number of worksheets is limited by available memory). Just as with the other objects in collections, there are a number of ways to return a reference to a worksheet (there must be at least one Worksheet in a Workbook). There s the ActiveSheet property of the Workbook: oWorkbook.ActiveSheet. There s the index in the collection: oWorkbook.Worksheets[1]. Then there s the worksheet name, which defaults to "Sheet" plus the number corresponding to its index when opened: oWorkbook.Worksheets["Sheet1"]. The Name property of the worksheet is read/write, so you can set the name to something that the users will appreciate, as the name shows on the tab at the bottom of the sheet.

You can also grab a reference as you add a worksheet. Polymorphism is hard at work here; I m sure you ve guessed that you use the Add method. The syntax is a little different:

oWorksheet = oWorkbook.Worksheets.Add([oBefore |, oAfter], [nCount], [nType])

oBefore	Object	The object reference to the worksheet before which the new sheet is added. (Optional)
oAfter	Object	The object reference to the worksheet after which the new sheet is added. (Optional)
nCount	Numeric	The number of sheets to be added. (Default = 1) (Optional)
nType	Numeric	One of the sheet type constants. Unless you re writing Excel 4 macros, the only applicable constant is xlWorksheet (-4167), which is the default. (Optional)

If no parameters are passed, the new sheet is placed just before the ActiveSheet. You cannot specify both an oBefore object and an oAfter object, so just pass a blank parameter for the one you re not using. VBA allows named parameters, which make this kind of syntax much easier to use, where the parameter looks something like this: Before:=oMySheet. This syntax makes it easy to remember to pass only one of the parameters. But VFP does not support named parameters, so we must give them in the order listed. The following code sample shows how to add one worksheet before sheet one, then another after sheet one:

* Get a reference to the first worksheet

* oWorkbook is assumed to point to a workbook

oWorksheet1 = oWorkbook.Worksheets[1]

* Add a sheet before sheet one.

oWorksheet2 = oWorkbook.Worksheets.Add(oWorksheet1)

* Add a sheet after sheet one. Note the use of an empty

* first parameter.

oWorksheet3 = oWorkbook.Worksheets.Add(,oWorksheet1)

Adding a third parameter tells how many sheets you want added. If you want three sheets added before sheet one, then give the following command:

oWorkbook.Worksheets.Add(oWorksheet1, , 3)

Note that the second parameter is empty. The fourth parameter, nType, is really for backward compatibility. Leave out the fourth parameter, for simplicity.

Rows, columns, cells, and ranges

A worksheet is composed of cells, which have addresses in the form X9, where X is one or two letters indicating the column, and 9 is one or more digits indicating the row. The top left cell in a worksheet is A1. The 29^th cell in the 32^nd column is AF29.

A group of cells is called a range. Generally, a range is a rectangular group of contiguous cells, such as from D2 to F8. A range can also be a single cell. Using the Union method, you can concatenate ranges. A range is actually an object, and is accessed through the worksheet s Range property.

To specify a range, you use the Range property to specify the addresses for the range boundaries. For example, to access the data in cell C5, use:

? oExcel.ActiveSheet.Range("C5").Value

To create a range containing a rectangular range of cells from D12 to F19, use:

oRange = oExcel.ActiveSheet.Range("D12:F19")

Another way to access cells is by using the Rows, Columns, and Cells properties of Worksheet and Range. These properties takes appropriate index values and return a range containing the specified cells. To check the value of all cells in the third row of a range, you can write:

FOR nColumn = 1 TO oRange.Columns.Count

? oRange.Cells[3, nColumn].Value

ENDFOR

To see the contents of all cells in a range, use:

FOR nRow = 1 TO oRange.Rows.Count

FOR nColumn = 1 TO oRange.Columns.Count

? oRange.Cells[nRow, nColumn].Value

ENDFOR

ENDFOR

Note that the indexes for Cells list the row, then the column (just as arrays in VFP do), but the addresses of cells list the column first.

Relatively speaking

So far, we ve used absolute addresses, meaning that the cell s address is relative to the first cell on the spreadsheet. We can also use relative addressing, which is based on a distance from the specified starting point. To create a range relative to another range, use the Offset property. This example creates a range 20 rows down and 30 rows to the left of oRange. The new range has the same size and shape:

oRange2 = oRange.Offset(20, 30)

Lots of ranges

Ranges don t have to consist of a single rectangle, either. Multiple groups can be listed when creating a range:

oRangeMixed = oExcel.ActiveSheet.Range("F21:F30, H21:H30")

The Union method combines several ranges into one. Here, the two ranges oRange and oRange2 are consolidated into a single range referenced by oBigRange:

oBigRange = oExcel.Union(oRange, oRange2)

Traversing a range with a loop like the preceding one doesn t work for a range composed of non-contiguous cells. The Areas collection has an entry for each rectangular portion (called an area) of the range, so to traverse all of the cells in a range, whether or not it s rectangular, you can use code like this:

FOR nArea = 1 TO oRange.Areas.Count

FOR nRow = 1 TO oRange.Areas[nArea].Rows.Count

FOR nColumn = 1 TO oRange.Areas[nArea].Columns.Count

? oRange.Areas[nArea].Cells[nRow, nColumn].Value

ENDFOR

ENDFOR

ENDFOR