Manipulating Workbook Objects

Workbook objects appear directly below the Application object in Excel's object hierarchy. You can use VBA to create new workbooks, open or delete existing workbooks, save and close open workbooks, and much more. The next section takes you through various techniques for specifying workbooks in your VBA code; then you'll look at some Workbook object properties and methods.

Specifying a Workbook Object

If you need to perform some action on a workbook, or if you need to work with an object contained in a specific workbook (such as a worksheet), you need to tell Excel which workbook you want to use. VBA gives you no fewer than three ways to do this:

Use the Workbooks object-The Workbooks object is the collection of all the open workbook files. To specify a workbook, either use its index number (where 1 represents the first workbook opened) or enclose the workbook name in quotation marks. For example, if the Budget.xlsx workbook was the first workbook opened, the following two statements would be equivalent:
```
Workbooks(1)
Workbooks("Budget.xlsx")
```
Use the ActiveWorkbook object-The ActiveWorkbook object represents the workbook that currently has the focus.
Use the ThisWorkbook object-The ThisWorkbook object represents the workbook where the VBA code is executing. If your code deals only with objects residing in the same workbook as the code itself, you can use the ActiveWorkbook object. However, if your code deals with other workbooks, use ThisWorkbook whenever you need to make sure that the code affects only the workbook containing the procedure.

Opening a Workbook

To open a workbook file, use the Open method of the Workbooks collection. The Open method has a dozen arguments you can use to fine-tune your workbook openings, but only one of these is mandatory. Here's the simplified syntax showing the one required argument (for the rest of the arguments, look up the Open method in the VBA Help system):

Workbooks.Open(FileName)

FileName
The full name of the workbook file, including the drive and folder that contain the file.

For example, to open a workbook named Data.xlsx in your user profile's Documents folder, you would use the following statement:

Workbooks.Open Environ("UserProfile") & "\Documents\Data.xlsx"

Creating a New Workbook

If you need to create a new workbook, use the Workbooks collection's Add method:

Workbooks.Add(Template)

Template is an optional argument that determines how the workbook is created. If Template is a string specifying an Excel file, VBA uses the file as a template for the new workbook. You also can specify one of the following constants:

xlWBATWorksheet
Creates a workbook with a single worksheet.

xlWBATChart
Creates a workbook with a single chart sheet.

Here's a sample statement that uses the Add method to open a new workbook based on Excel's ExpenseReport.xltx template file:

Workbooks.Add "C:\Program Files\Microsoft Office" & _
"\Templates\1033\ExpenseReport.xltx"

Specifying the Number of Sheets in a New Workbook

When you create a new workbook in Excel, the file comes with three worksheets by default. Most people just use one worksheet, but leave the other two sheets in the workbook, just in case. If you use several sheets in many or all of your workbooks, you should consider increasing the default number of sheets that Excel includes in new workbooks. Follow these steps:

Choose Office, Excel Options to open the Excel Options dialog box.
Click Popular.
Use the Include This Many Sheets spin box to set the number of sheets you want by default.
Click OK.

That's fine if you always use lots of sheets, but what if you use lots of sheets only occasionally? In that case, it would be nice to be able to specify the number of sheets you want as you're creating a new workbook. The macro in Listing show enables you to do just that.

A Procedure That Prompts You to Specify the Number of Sheets You Want in a New Workbook

Sub NewWorkbookWithCustomSheets()
    Dim currentSheets As Integer
    With Application
        '
        ' Save the current value of SheetsInNewWorkbook
        '
        currentSheets = .SheetsInNewWorkbook
        '
        ' Ask how many sheets to include in the new workbook
        ' and store the result in SheetsInNewWorkbook
        '
        .SheetsInNewWorkbook = InputBox( _
            "How many sheets do you want " & _
            "in the new workbook?", , 3)
        '
        ' Create the new workbook
        '
        Workbooks.Add
        '
        ' Restore the original value of SheetsInNewWorkbook
        '
        .SheetsInNewWorkbook = currentSheets
    End With
End Sub

The value of the Include This Many Sheets setting is given by the Application object's SheetsInNewWorkbook property. The macro first stores the current SheetsInNewWorkbook value in the currentSheets variable. Then the macro runs the InputBox function to get the number of required sheets (with a default value of 3), and this value is assigned to the SheetsInNewWorkbook property. Then the Workbooks.Add statement creates a new workbook (which will have the specified number of sheets) and the SheetsInNewWorkbook property is returned to its original value.

Saving Every Open Workbook

If you often work with multiple workbooks at once, you may find yourself moving from one workbook to another, making changes to each one as you go. Unless you remember to save all along, you probably end up with some or all of your open workbooks with unsaved changes. Unfortunately, Excel doesn't tell you which workbooks have unsaved changes, so you have no choice but to trudge through each open workbook and run the Save command. You can avoid this drudgery by using the SaveAll macro shown in Listing below.

A Procedure That Saves Every Open Workbook

Sub SaveAll()
    Dim wb As Workbook
    Dim newFilename As Variant
    '
    ' Run through all the open workbooks
    '
    For Each wb In Workbooks
        '
        ' Has the workbook been saved before?
        '
        If wb.Path <> "" Then
            '
            ' If so, save it
            '
            wb.Save
        Else
            '
            ' If not, display the Save As dialog box
            ' to get the workbook's path & filename
            '
            With Application

                    FileFilter:="Microsoft Office " & _
                    "Excel Workbook " & _
                    "(*.xlsx), *.xlsx")
            End With
            '
            ' Did the user click Cancel?
            '
            If newFilename <> False Then
                '
                ' If not, save the workbook using the
                ' specified path and filename
                '
                wb.SaveAs fileName:=newFilename
            End If
        End If
    Next 'wb
End Sub

The main loop in the SaveAll macro uses the Workbooks collection and a For Each...Next loop to run through all the open workbooks. For each workbook (given by the wb Workbook variable), the loop first checks the Path property to see whether it returns the null string (""). If not, it means the workbook has been saved previously, so the macro runs the Save method to save the file. If Path does return the null string, it means you're saving the workbook for the first time. In this case, the macro runs the GetSaveAsFilename method, which displays the Save As dialog box so that you can select a save location and filename, which are stored in the newFilename variable. If this variable's value is False, it means you clicked Cancel in the Save As dialog box, so the macro skips the file; otherwise, the macro uses the SaveAs method to save the workbook, using the specified path and filename.

Closing a Workbook

To close a Workbook object, use the Close method, which uses the following syntax:

Workbook.Close([SaveChanges][, FileName][, RouteWorkbook])

Workbook
The Workbook object you want to close.

SaveChanges
If the workbook has been modified, this argument determines whether or not Excel saves those changes:

SaveChanges	Action
True	Saves changes before closing.
False	Doesn't save changes.
Omitted	Asks the user whether changes should be saved.

FileName
Save the workbook under this filename.

RouteWorkbook
Routes the workbook according to the following values:

SaveChanges	Action
True	Sends the workbook to the next recipient.
False	Doesn't send the workbook.
Omitted	Asks the user whether the workbook should be sent.

[Previous] [Contents] [Next]