Chapter 17. The Workbook Object

In this chapter, we discuss the Workbook object and the Workbooks collection. Figure 17-1 shows the portion of the Excel object model that relates directly to workbooks.

The Workbook object

Figure 17-1. The Workbook object

The Workbooks Collection

The Application object has a Workbooks property that returns a Workbooks collection, which contains all of the Workbook objects for the currently open instance of Excel. For instance, the following code displays the number of open workbooks: 

Dim wbs As Workbooks
Set wbs = Application.Workbooks
MsgBox wbs.Count

Let us look at a few of the properties and methods of the Workbooks collection.

The Add method creates a new workbook, which is then added to the Workbooks collection. The new workbook becomes the active workbook. The syntax is: 

                  WorkbooksObject.Add(Template)

where the optional Template parameter determines how the new workbook is created. If this argument is a string specifying the name of an existing Excel template file, the new workbook is created with that file as a template.

As you may know, a template is an Excel workbook that may contain content (such as row and column labels), formatting, and macros and other customizations (menus and toolbars, for instance). When you base a new workbook on a template, the new workbook receives the content, formatting, and customization from the template.

The Template argument can also be one of the following constants: 

Enum XlWBATemplate
     xlWBATWorksheet = -4167
     xlWBATChart = -4109
     xlWBATExcel4MacroSheet = 3
     xlWBATExcel4IntlMacroSheet = 4
End Enum

In this case, the new workbook will contain a single sheet of the specified type. If the Template argument is omitted, Excel will create a new workbook with the number of blank sheets set by the Application object's SheetsInNewWorkbook property.

The Close method closes all open workbooks. The syntax is simply: 

                  WorksbooksObject.Close

Most collection objects have a Count property, and the Workbooks collection is no exception. This property simply returns the number of currently open workbooks.

The Item property returns a particular workbook in the Workbooks collection. For instance: 

Workbooks.Item(1)

returns the Workbook object associated with the first workbook in the Workbooks collection. Since the Item property is the default property, we can also write this as: 

Workbooks(1)

Note that we cannot rely on the fact that a certain workbook will have a certain index. (This applies to all collections.) Thus, to refer to a particular workbook, you should always use its name, as in: 

Workbooks("Book1.xls")

It is important to note that if a user creates a new workbook named, say, Book2, using the New menu item on the File menu, then we may refer to this workbook in code by writing: 

Workbooks("Book2")

but the code:

Workbooks("Book2.xls")

will generate an error (subscript out of range) until the workbook is actually saved to disk.

This method opens an existing workbook. The rather complex syntax is:

                  WorkbooksObject.Open(FileName, UpdateLinks, ReadOnly, _
   Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, _
   Origin, Delimiter, Editable, Notify, Converter, AddToMRU)

Most of these parameters are rarely used (several of them relate to opening text files, for instance). We discuss the most commonly used parameters and refer the reader to the help files for more information. Note that all of the parameters are optional except FileName.

FileName is the file name of the workbook to be opened. To open the workbook in read-only mode, set the ReadOnly parameter to True.

If a password is required to open the workbook, the Password parameter should be set to this password. If a password is required but you do not specify the password, Excel will ask for it.

The AddToMru parameter should be set to True to add this workbook to the list of recently used files. The default value is False.

This method will load a text file as a new workbook. The method will parse the text data and place it in a single worksheet. The rather complex syntax is: 

                  WorkbooksObject.OpenText(Filename, Origin, StartRow, _
   DataType, TextQualifier, ConsecutiveDelimiter, Tab, _
   Semicolon, Comma, Space, Other, OtherChar, FieldInfo)

Note first that all of the parameters to this method are optional except the FileName parameter.

The Filename parameter specifies the filename of the text file to be opened.

The Origin parameter specifies the origin of the text file and can be one of the following XlPlatform constants: 

Enum XlPlatform
     xlMacintosh = 1
     xlWindows = 2
     xlMSDOS = 3
End Enum

Note that the xlWindows value specifies an ANSI text file, whereas the xlMSDOS constant specifies an ASCII file. If this argument is omitted, the current setting of the File Origin option in the Text Import Wizard will be used.

The StartRow parameter specifies the row number at which to start parsing text from the text file. The default value is 1.

The optional DataType parameter specifies the format of the text in the file and can be one of the following XlTextParsingType constants: 

Enum XlTextParsingType
     xlDelimited = 1         ' Default
     xlFixedWidth = 2
End Enum

The TextQualifier parameter is the text qualifier. It can be one of the following XlTextQualifier constants: 

Enum XlTextQualifier 
     xlTextQualifierNone = -4142
     xlTextQualifierDoubleQuote = 1   ' Default
     xlTextQualifierSingleQuote = 2
End Enum

The ConsecutiveDelimiter parameter should be set to True for Excel to consider consecutive delimiters as one delimiter. The default value is False.

There are several parameters that require that DataType be xlDelimited. When any one of these parameters is set to True, it indicates that Excel should use the corresponding character as the text delimiter. They are described here (all default values are False):

Tab

Set to True to use the tab character as the delimiter.

Semicolon

Set to True to use a semicolon as the delimiter.

Comma

Set to True to use a comma as the delimiter.

Space

Set to True to use a space as the delimiter.

Other

Set to True to use a character that is specified by the OtherChar argument as the delimiter.

When Other is True, OtherChar specifies the delimiter character. If OtherChar contains more than one character, only the first character is used.

The FieldInfo parameter is an array containing parse information for the individual source columns. The interpretation of FieldInfo depends on the value of DataType.

When DataType is xlDelimited, the FieldInfo argument should be an array whose size is the same as or smaller than the number of columns of converted data. The first element of a two-element array is the column number (starting with the number 1), and the second element is one of the following numbers that specifies how the column is parsed:

Value

Description

1

General

2

Text

3

MDY date

4

DMY date

5

YMD date

6

MYD date

7

DYM date

8

YDM date

9

Skip the column

If a two-element array for a given column is missing, then the column is parsed with the General setting. For instance, the following value for FieldInfo causes the first column to be parsed as text and the third column to be skipped: 

Array(Array(1, 2), Array(3, 9))

All other columns will be parsed as general data.

To illustrate, consider a text file with the following contents:

"John","Smith","Serial Record",1/2/98
"Fred","Gwynn","Serials Order Dept",2/2/98
"Mary","Davis","English Dept",3/5/98
"David","Johns","Chemistry Dept",4/4/98

The code:

Workbooks.OpenText _
  FileName:="d:\excel\temp.txt", _
  Origin:=xlMSDOS, _
  StartRow:=1, _
  DataType:=xlDelimited, _
  TextQualifier:=xlTextQualifierDoubleQuote, _
  ConsecutiveDelimiter:=True, _
  Comma:=True, _
  FieldInfo:=Array(Array(1, 2), _
     Array(2, 2), Array(3, 2), Array(4, 6))

produces the worksheet shown in Figure 17-2. Note that the cells in column D are formatted as dates.

A comma-delimited text file opened in Excel

Figure 17-2. A comma-delimited text file opened in Excel

On the other hand, if DataType is xlFixedWidth, the first element of each two-element array specifies the starting character position in the column (0 being the first character) and the second element specifies the parse option (1-9) for the resulting column, as described earlier.

To illustrate, consider the text file whose contents are as follows:

0-125-689
2-523-489
3-424-664
4-125-160

The code:

Workbooks.OpenText _
  FileName:="d:\excel\temp.txt", _
  Origin:=xlMSDOS, _
  StartRow:=1, _
  DataType:=xlFixedWidth, _
  FieldInfo:=Array(Array(0, 2), _
    Array(1, 9), Array(2, 2), Array(5, 9), _
    Array(6, 2))

produces the worksheet in Figure 17-3. (Note how we included arrays to skip the hyphens.)

A fixed-width text file opened in Excel

Figure 17-3. A fixed-width text file opened in Excel

Finally, it is important to observe that the text file is opened in Excel, but not converted to an Excel workbook file. To do so, we can invoke the SaveAs method, as in: 

Application.ActiveSheet.SaveAs _
   FileName:="d:\excel\temp.xls", _
   FileFormat:=xlWorkbookNormal