The Workbook Object

A Workbook object represents an open Excel workbook. As we have discussed, Workbook objects are stored in a Workbooks collection.

The Workbook object has a total of 103 properties and methods, as shown in Table 17-1.

Table 17-1. Members of the Workbook object

_CodeName	FullName	RefreshAll
_PrintOut<v9>	FullNameURLEncoded<v10>	RejectAllChanges
_Protect<v10>	HasMailer	ReloadAs<v9>
_ReadOnlyRecommended<v10>	HasPassword	RemovePersonalInformation<v10>
_SaveAs<v10>	HasRoutingSlip	RemoveUser
AcceptAllChanges	HighlightChangesOnScreen	Reply
AcceptLabelsInFormulas	HighlightChangesOptions	ReplyAll
Activate	HTMLProject<v9>	ReplyWithChanges<v10>
ActiveChart	IsAddin	ResetColors
ActiveSheet	IsInplace	RevisionNumber
AddToFavorites	KeepChangeHistory	Route
Application	Keywords	Routed
Author	LinkInfo	RoutingSlip
AutoUpdateFrequency	LinkSources	RunAutoMacros
AutoUpdateSaveChanges	ListChangesOnNewSheet	Save
BreakLink<v10>	Mailer	SaveAs
BuiltinDocumentProperties	MergeWorkbook	SaveCopyAs
CalculationVersion<v9>	Modules	Saved
CanCheckIn<v10>	MultiUserEditing	SaveLinkValues
ChangeFileAccess	Name	sblt<v9>
ChangeHistoryDuration	Names	SendForReview<v10>
ChangeLink	NewWindow	SendMail
Charts	OnSave	SendMailer
CheckIn<v10>	OnSheetActivate	SetLinkOnData
Close	OnSheetDeactivate	SetPasswordEncryptionOptions<v10>
CodeName	OpenLinks	Sheets
Colors	Parent	ShowConflictHistory
CommandBars	Password<v10>	ShowPivotTableFieldList<v10>
Comments	PasswordEncryptionAlgorithm<v10>	SmartTagOptions<v10>
ConflictResolution	PasswordEncryptionFileProperties<v10>	Styles
Container	PasswordEncryptionKeyLength<v10>	Subject
CreateBackup	PasswordEncryptionProvider<v10>	TemplateRemoveExtData
Creator	Path	Title
CustomDocumentProperties	PersonalViewListSettings	Unprotect
CustomViews	PersonalViewPrintSettings	UnprotectSharing
Date1904	PivotCaches	UpdateFromFile
DeleteNumberFormat	PivotTableWizard	UpdateLink
DialogSheets	Post	UpdateLinks<v10>
DisplayDrawingObjects	PrecisionAsDisplayed	UpdateRemoteReferences
Dummy16<v10>	PrintOut	UserControl
Dummy17<v10>	PrintPreview	UserStatus
EnableAutoRecover<v10>	Protect	VBASigned<v9>
EndReview<v10>	ProtectSharing	VBProject
EnvelopeVisible<v9>	ProtectStructure	WebOptions<v9>
Excel4IntlMacroSheets	ProtectWindows	WebPagePreview<v9>
Excel4MacroSheets	PublishObjects<v9>	Windows
ExclusiveAccess	PurgeChangeHistoryNow	Worksheets
FileFormat	ReadOnly	WritePassword<v10>
FollowHyperlink	ReadOnlyRecommended	WriteReserved
ForwardMailer	RecheckSmartTags<v10>	WriteReservedBy

Several of the members listed in Table 17-1 exist solely to return the children of the Workbook object. The children are shown in Figure 17-4.

Figure 17-4. Children of the Workbook object

Table 17-2 gives the members of the Workbook object that return children.

Table 17-2. Members of Workbook that return children

Name	ReturnType
ActiveChart	Chart
Application	Application
Charts	Sheets
CustomViews	CustomViews
DialogSheets	Sheets
Excel4IntlMacroSheets	Sheets
Excel4MacroSheets	Sheets
Mailer	Mailer
Modules	Sheets
Names	Names
NewWindow	Window
PivotCaches	PivotCaches
PublishObjects	PublishObjects
RoutingSlip	RoutingSlip
Sheets	Sheets
SmartTagOptions	SmartTagOptions
Styles	Styles
WebOptions	WebOptions
Windows	Windows
Worksheets	Sheets

There are a few items worth noting about Table 17-2. First, the ActiveSheet property may return either a Chart object or a Worksheet object, depending upon what type of object is currently active.

Second, the Charts, Sheets, and Worksheets properties all return a (different) Sheets collection. In particular, the Charts object returns the Sheets collection that contains all of the chart sheets in the workbook. (This does not include charts that are embedded in worksheets.) The Worksheets property returns the Sheets collection of all worksheets in the workbook. Finally, the Sheets property returns the Sheets collection of all worksheets and chart sheets. This is a relatively rare example of a collection that contains objects of more than one type. Note that there is no Sheet object in the Excel object model.

Let us look at a few of the more commonly used members from Table 17-1.

Activate Method

This method activates the workbook. The syntax is straightforward, as in:

Workbooks("MyWorkBook").Activate

Note that Workbooks is global, so we do not need to qualify it with the Application keyword.

Close Method

The Close method closes the workbook. Its syntax is:

                  WorkbookObject.Close(SaveChanges, FileName, RouteWorkbook)

Note that the Close method of the Workbook object has three parameters, unlike the Close method of the Workbooks object, which has none.

The optional SaveChanges parameter is used to save changes to the workbook before closing. In particular, if there are no changes to the workbook, the argument is ignored. It is also ignored if the workbook appears in other open windows. On the other hand, if there are changes to the workbook and it does not appear in any other open windows, the argument takes effect.

In this case, if SaveChanges is True, the changes are saved. If there is not yet a filename associated with the workbook (that is, if it has not been previously saved), then the name given in FileName is used. If FileName is also omitted, Excel will prompt the user for a filename. If SaveChanges is False, changes are not saved. Finally, if the SaveChanges argument is omitted, Excel will display a dialog box asking whether the changes should be saved. In short, this method behaves as you would hope.

The optional RouteWorkbook refers to routing issues; we refer the interested reader to the Excel VBA help file for more information.

It is important to note that the Close method checks the Saved property of the workbook to determine whether or not to prompt the user to save changes. If we set the Saved property to True, then the Close method will simply close the workbook with no warning and without saving any unsaved changes.

DisplayDrawingObjects Property

This property returns or sets a value indicating how shapes are displayed. It can be one of the following XlDisplayShapes constants:

Enum XlDisplayShapes
     XlDisplayShapes = -4104
     xlPlaceholders = 2
     xlHide = 3
End Enum

FileFormat Property (Read-Only Long )

This property returns the file format or type of the workbook. It can be one of the following XlFileFormat constants:

Enum XlFileFormat
    xlAddIn = 18
    xlCSV = 6
    xlCSVMac = 22
    xlCSVMSDOS = 24
    xlCSVWindows = 23
    xlCurrentPlatformText = -4158
    xlDBF2 = 7
    xlDBF3 = 8
    xlDBF4 = 11
    xlDIF = 9
    xlExcel2 = 16
    xlExcel2FarEast = 27
    xlExcel3 = 29
    xlExcel4 = 33
    xlExcel4Workbook = 35
    xlExcel5 = 39
    xlExcel7 = 39
    xlExcel9795 = 43
    xlHtml = 44
    xlIntlAddIn = 26
    xlIntlMacro = 25
    xlSYLK = 2
    xlTemplate = 17
    xlTextMac = 19
    xlTextMSDOS = 21
    xlTextPrinter = 36
    xlTextWindows = 20
    xlUnicodeText = 42
    xlWebArchive = 45
    xlWJ2WD1 = 14
    xlWJ3 = 40
    xlWJ3FJ3 = 41
    xlWK1 = 5
    xlWK1ALL = 31
    xlWK1FMT = 30
    xlWK3 = 15
    xlWK3FM3 = 32
    xlWK4 = 38
    xlWKS = 4
    xlWorkbookNormal = -4143
    xlWorks2FarEast = 28
    xlWQ1 = 34
    xlXMLSpreadsheet = 46
End Enum

Name, FullName, and Path Properties

The Name property returns the name of the workbook, the Path property returns the path to the workbook file, and FullName returns the fully qualified (path and filename) of the workbook file. All of these properties are read-only.

Note that using the Path property without a qualifier is equivalent to:

Application.Path

and thus returns the path to Excel itself (rather than to a workbook).

HasPassword Property (Read-Only Boolean)

This read-only property is True if the workbook has password protection. Note that a password can be assigned as one of the parameters to the SaveAs method.

PrecisionAsDisplayed Property (R/W Boolean)

When this property is True, calculations in the workbook will be done using only the precision of the numbers as they are displayed, rather than as they are stored. Its default value is False; calculations are based on the values of numbers as they are stored.

PrintOut Method

The PrintOut method prints an entire workbook. (This method applies to a host of other objects as well, such as Range, Worksheet, and Chart.) The syntax is:

                  WorkbookObject.PrintOut(From, To, Copies, _
   Preview, ActivePrinter, PrintToFile, Collate)

Note that all of the parameters to this method are optional.

The From parameter specifies the page number of the first page to print, and the To parameter specifies the last page to print. If omitted, the entire object (range, worksheet, etc.) is printed.

The Copies parameter specifies the number of copies to print. The default is 1.

Set Preview to True to invoke print preview rather than printing immediately. The default is False.

ActivePrinter sets the name of the active printer. On the other hand, setting PrintToFile to True causes Excel to print to a file. Excel will prompt the user for the name of the output file. (Unfortunately, there is no way to specify the name of the output file in code.)

The Collate parameter should be set to True to collate multiple multipage copies.

PrintPreview Method

This method invokes Excel's print preview feature. Its syntax is:

                  WorkbookObject.PrintPreview

Note that the PrintPreview method applies to the same set of objects as the PrintOut method.

Protect Method

This method protects a workbook so that it cannot be modified. Its syntax is:

                  WorkbookObject.Protect(Password, Structure, Windows)

The method also applies to charts and worksheets, with a different syntax.

The optional Password parameter specifies a password (as a case-sensitive string). If this argument is omitted, the workbook will not require a password to unprotect it.

Set the optional Structure parameter to True to protect the structure of the workbook—that is, the relative position of the sheets in the workbook. The default value is False.

Set the optional Windows parameter to True to protect the workbook windows. The default is False.

ReadOnly Property (Read-Only Boolean)

This property is True if the workbook has been opened as read-only.

RefreshAll Method

This method refreshes all external data ranges and pivot tables in the workbook. The syntax is:

                  WorkbookObject.RefreshAll

Save Method

This method simply saves any changes to the workbook. Its syntax is:

                  WorkbookObject.Save

SaveAs Method

This method saves changes to a workbook in the specified file. The syntax is:

                  expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, _
   ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, _
   AddToMru, TextCodePage, TextVisualLayout)

The Filename parameter specifies the filename to use for the newly saved disk file. If a path is not included, Excel will use the current folder.

The FileFormat parameter specifies the file format to use when saving the file. Its value is one of the XlFileFormat constants described in our discussion of the FileFormat property.

The Password parameter specifies the password to use when saving the file and can be set to any case-sensitive string of up to 15 characters.

The WriteResPassword is a string that specifies the write-reservation password for this file. If a file is saved with a write-reservation password and this password is not supplied when the file is next opened, the file will be opened as read-only.

We can set the ReadOnlyRecommended parameter to True to display a message when the file is opened, recommending that the file be opened as read-only.

Set the CreateBackup parameter to True to create a backup file.

The AccessMode and ConflictResolution parameters refer to sharing issues. We refer the interested reader to the Excel VBA help file for details.

Set the AddToMru parameter to True to add the workbook to the list of recently used files. The default value is False.

The remaining parameters are not used in the U.S. English version of Excel.

SaveCopyAs Method

This method saves a copy of the workbook to a file but does not modify the open workbook itself. The syntax is:

                  WorkbookObject.SaveCopyAs(Filename)

where Filename specifies the filename for the copy of the original file.

Saved Property (R/W Boolean)

This property is True if no changes have been made to the specified workbook since it was last saved. Note that this property is read/write, which means we can set the property to True even if the workbook has been changed since it was last saved. As discussed earlier, we can set this property to True, then close a modified workbook without being prompted to save the current changes.