Objects Workbook, Workbooks and ActiveWorkbook

Object Workbook follows right after object Application in the object hierar-chy. It is easy to understand the following properties of the Workbook object.

 Name — the workbook name with its extension.

The possible extensions of the name follow:

.xlsm if the workbook contains macros;

.xlsx if macros are absent in the workbook, etc.

The file name together with its extension frequently is also called the file name.

As a rule, it does not lead to any confusion.

 Path — the path to the workbook in the file system of Windows.

 FullName — the workbook name with its path and extension, i.e., the workbook full name.

Let us consider the following three methods of the Workbook object:

 Close — closing the workbook;

 Save, SaveAs — saving the workbook.

The SaveAs method differs from the Save method in that SaveAs has a list of optional parameters, which includes FileName, FileFormat and Password.

Examples of using the properties and methods of the Workbook object are given below, when considering objects of the Workbook data type. Here, “data type” has the same sense as in expression “variables (records) of the Session data type” on p. 91.

Objects of the Workbook type (“data” is omitted for brevity) have the proper-ties and methods of the Workbook object.

The Workbooks object is an object containing all open Excel workbooks.

This object is also named as the Workbooks collection.

In VBA,

Workbooks("Personnel_department")

is the Workbook type object corresponding to the open workbook by name Personnel_department.

Let us consider the main methods of the Workbooks object.

 Activate — activation of the specified workbook (from a number of the open Excel workbooks) when its first worksheet becomes active.

As an example of applying the Activate method, let us consider the following operator of activating the above Personnel_department workbook:

Workbooks("Personnel_department").Activate

 Add — the creation of a new workbook, which becomes active at once.

Applying the Add method is accompanied by return (into the program) of the created workbook, which is the Workbook type object. As an example, see operator 2 in the NewBook macro (p. 164).

Note that all collections have the Add method. Its application adds (to the collection) a new object of the corresponding type. In particular, a new object of the Workbook type is added to the Workbooks collection.

All collections, in particular Workbooks, have useful property by name Count that allows determining the quantity of objects in the collection.

An example of returning the Count property is in the following program:

Listing 2.6 Sub NumberofBooks()

MsgBox Str(Workbooks.Count) End Sub

The window, containing the number of open workbooks and the OK button, is displayed when executing the NumberofBooks macro. To terminate the execution, we must click on OK.

It was mentioned in the previous section that ActiveWorkbook is a property of the Application object, corresponding to the active workbook. As the Work-book type object, the ActiveWorkWork-book property has the properties and methods of the Workbook object.

To obtain examples of returning properties Name and FullName of the Workbook object and of applying the Save method, let us fulfill the following:

1) in the Excel window with the BookMacrorecorder workbook (p. 147), click on the Enable Content button of the Security Warning panel to allow the MR macro to work;

2) Developer (or View) > Macros > line MR > Edit;

3) put operator block 1: Dim S As String

2: S = ActiveWorkbook.Name 3: MsgBox S

4: MsgBox ActiveWorkbook.FullName 5: ActiveWorkbook.Save

2.9. Objects Workbook, Workbooks and ActiveWorkbook above the last line of the MR macro (Fig. 2.15);

4) start the MR macro execution, for example, by clicking on arrow ► in the VB window.

Fig. 2.15. The VB window with the macro text after inserting the additional operator block

Because of executing operator 2, the S string has a value that is equal to the name of the active workbook. Operator 3 displays this string (Fig. 2.16).

Let us click on the OK button (in the window depicted in Fig. 2.16) to con-tinue executing the MR macro. Operator 4 displays the active workbook’s full name (Fig. 2.17). After clicking on the OK button, operator 5 saves the active workbook.

To obtain an example of returning the Path property of the Workbook object, we will execute the MR macro with the following form of the operator intended for displaying the full name:

4: MsgBox ActiveWorkbook.Path & "\" & _ ActiveWorkbook.Name

The window, depicted in Fig. 2.17, appears during the execution, as in the case of the previous version of operator 4.

Fig. 2.16. The window with the active workbook’s name

Fig. 2.17. The window with the full name of the active workbook In the following example, a workbook is created, a numerical value is entered into it, this workbook is saved and closed, and the Excel window is closed too.

1. Insert a module into a blank workbook, for example Book1, and put the following text into the code window (Fig. 2.18):

Listing 2.7 Sub NewBook()

1: Dim wbNewWorkbook As Workbook 2: Set wbNewWorkbook = Workbooks.Add

3: wbNewWorkbook.Worksheets("Sheet1").Range("A1"). _ Value = 100

4: wbNewWorkbook.SaveAs _

"c:\Users\usr\Hour0.xlsx"

5: wbNewWorkbook.Close

2.9. Objects Workbook, Workbooks and ActiveWorkbook 6: MsgBox "Workbook is closed"

7: Application.Quit End Sub

2. Start the NewBook macro execution.

3. When the window with message Workbook is closed appears (Fig. 2.19), click on the OK button in it. At that, the NewBook macro closes the Excel win-dow. When closing the Excel window, we may disagree with the offer to save Book1.

4. Open the Hour0 workbook in folder c:\Users\usr

Number 100 is in cell A1 on worksheet Sheet1.

Fig. 2.18. The VB window with the NewBook macro text

The NewBook macro begins with the declaration of the wbNewWorkbook variable (operator 1). Because of executing operator 2, a workbook is created and assigned to the wbNewWorkbook variable. Operator 3 puts number 100 into the A1 cell on the Sheet1 worksheet of this workbook.

When creating a workbook, tabular processor Excel gives it a default name, for example, Book2. Because the workbook name is known inexactly, the wbNewWorkbook variable is used instead of Workbooks("Book2") in the macro. Operator 4 saves this workbook under the following full name:

c:\Users\usr\Hour0.xlsx.

Operator 5 closes the Hour0 workbook, and operator 6 displays the message about it (Fig. 2.19). Operator 7 performs exit from Excel.

In operator 1, we may replace the Workbook type by the Object data type (Appendix 1) similar to the Variant data type familiar to us.

Fig. 2.19. The window with the OK button for terminating the NewBook macro execution

2.10. Objects Worksheet, Worksheets and ActiveSheet

2.10. Objects Worksheet, Worksheets