Excel VBA made Easy: -Liew Voon Kiong by Mark Allen

Author:Mark Allen [Allen, Mark]
Language: eng
Format: epub
Publisher: nguyenminh
Published: 2016-12-05T08:00:00+00:00

Chapter 9 Excel VBA Objects Part 2 –The Workbook Object

In the previous chapter, we have learned about Excel VBA objects and their properties and methods. In this chapter, we shall learn specifically about the Workbook object as it is one of the most important Excel VBA objects. It is also at the top of the hierarchy of the Excel VBA objects. We will deal with properties and methods associated the Workbook object.

9.1 The Workbook Properties. When we write 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.

A workbook has a number of properties. Some of the common properties are Name, Path and FullName Let’s look at the following example:

Example 9.1

Private Sub CommandButton1_Click() MsgBox Workbooks(1).Name The program will cause a message dialog box to pop up and displays the first workbook name, i.e. workbook_object1.xls as shown in Figure 9.1 below:

Figure 9.1: The name of the Excel workbook

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 9.2 Private Sub CommandButton1_Click () MsgBox ThisWorkbook.Path

End Sub

Or you can use the following code

Private Sub CommandButton1Click ()

MsgBox Workbooks ("workbook_object1.xls").Path

End Sub

The output is shown below:

Figure 9.2: The path of the opened workbook

Example 9.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("workbook_object1.xls").Fullname

End Sub

The output is shown in Figure 9.3.

Figure 9.3

9.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 9.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 9.5.

Private Sub CommandButton1_Click()

fName = Application.GetSaveAsFilename ThisWorkbook.SaveAs Filename:=fName

End Sub

Figure 9.4: The SaveAs dialog

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

Workbooks.Open ("File Name")

Example 9.5

In this example, when the user click on the command button, it wil open the file workbook_object1.xls under the pathC:\Users\liewvk\Documents\ Private Sub CommandButton1_Click() Workbooks.Open ("C:\Users\liewvk\Documents\workbook_object1.xls")

End Sub

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

Workbooks (i).Close

Example 9.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

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.