Lesson 21: The Workbook Object

¡¡[Lesson 20] [Home] [Lesson 22]



In previous lesson, we have learned to write code associated with the worksheet object in Excel VBA . In this lesson, we shall learn about the Workbook object . The Workbook object at the top of the hierarchy of the Excel VBA objects. We will deal with properties and methods associated the Workbook object.

21.1 The Workbook Properties.

When we write Excel VBA code involving the Workbook object, we use Workbooks. The reason is that we are dealing with a collection of workbooks most of the time, so using Workbooks enables us to manipulate multiple workbooks at the same time. When will deal with multiple workbooks, we can use indices to denote different workbooks that are open, using the syntax Workbooks (i), where i is an index. For example, Workbooks (1) denotes Workbook1, Workbooks (2) denotes Workbook2 and more. Workbooks have a number of properties. Some of the common properties are Name, Path and FullName Let's look at the following example:

Example 21.1

  Private Sub CommandButton1_Click()

    MsgBox Workbooks(1).Name

  End Sub

The program will cause a message dialog box to pop up and displays the first workbook name, i.e. Book1 as shown in Figure 21.1 below:

vba2010_figure20.1

Figure 21.1


If we have only one open workbook, we can also use the syntax ThisWorkbook in place of Workbook (1), as follows:

  Private Sub CommandButton1_Click ()

    MsgBox ThisWorkbook.Name

  End Sub

Example 21.2

  Private Sub CommandButton1_Click ()

    MsgBox ThisWorkbook.Path

  End Sub

Or you can use the following code:

  Private Sub CommandButton1Click ()

    MsgBox Workbooks ("Book1").Path

  End Sub

The Output is shown in Figure 21.2

vba2010_figure20.2

Figure 21.2


Example 21.3

This example will display the path and name of the opened workbook. The code is:

  Private Sub CommandButton1_Click ()

    MsgBox ThisWorkbook.FullName

  End Sub

Or

  Private Sub CommandButton1Click()

    MsgBox Workbooks("Book1").Fullname

  End Sub


The output is shown in Figure 21.3.

vba2010_figure20.3

Figure 21.3


21.2 The Workbook Methods

There are a number of methods associated with the workbook object. These methods are Save, SaveAs, Open, Close and more.

Example 21.4

In this example, when the user clicks on the command button, it will open up a dialog box and ask the user to specify a path and type in the file name, and then click the save button, not unlike the standard windows SaveAs dialog, as shown in Figure 21.4.

  Private Sub CommandButton1_Click()

    fName = Application.GetSaveAsFilename

    ThisWorkbook.SaveAs Filename:=fName

  End Sub

vba2010_figure20.4

Figure 21.4

Another method associated with the workbook object is open. The syntax is

  Workbooks.Open ("File Name")


Example 21.5

In this example, when the user click on the command button, it wil open the file book1.xls under the path C:\Users\Toshiba\Documents\Liew Folder\VBA\vba2010\

  Private Sub CommandButton1_Click()

    Workbooks.Open ("C:\Users\Toshiba\Documents\Liew Folder\VBA\vba2010\book1.xlsx")

  End Sub

The close method is the command that closes a workbook. The syntax is

  Workbooks (i).Close


Example 21.6

In this example, when the user clicks the command button, it will close Workbooks (1).

  Private Sub CommandButton1_Click()

    Workbooks (1).Close

  End Sub



  Bookmark and Share



¡¡[Lesson 20] [Home] [Lesson22]

Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved 

Contact: admin@excelvbatutor.com [Privacy Policy]