Working with the Workbook Object

When you open or save a file in Microsoft Excel, you're actually opening and saving a workbook. In Visual Basic, the methods for manipulating files are methods of the Workbook object or its collection.

Opening Workbooks

When you open a workbook, you use the Open method. The Open method always applies to the Workbooks collection, which you return using the Workbooks method. The following code opens the file BOOK1.XLS (in the current folder) and then displays the value that's in cell A1 of the first worksheet in the workbook.

Sub OpenBook1()
    Set myBook = Workbooks.Open(Filename:="BOOK1.XLS")
    MsgBox myBook.Worksheets(1).Range("A1").Value
End Sub

Notice that the return value of the Open method is a Workbook object that refers to the workbook that was just opened.

The filename in this example doesn't contain a path; therefore, the file is assumed to be in the current folder. This is guaranteed to cause a run-time error, because as soon as the user changes the current folder, Visual Basic can no longer find the file.

There are two relatively safe places to store a workbook you want to open programmatically. One place is the folder that contains the executable file for Microsoft Excel. The other place is the Library folder, which is created automatically during setup; this folder is one level down from the folder that contains the executable file.

If you want to open a workbook that's saved in the folder that contains the executable file, you can use the Path property to return a string that specifies the folder. The PathSeparator property returns the correct separator character for the current file system (for example, "\" for MS-DOS®/Windows® FAT or ":" for the Macintosh®). The following example shows file-system-independent code you can use to open BOOK1.XLS, assuming that BOOK1.XLS is saved in the executable file's folder.

Sub OpenBook1()
    EXEPath = Application.Path & Application.PathSeparator
    fName = EXEPath & "BOOK1.XLS"
    Set myBook = Workbooks.Open(Filename:=fName)
    MsgBox myBook.Worksheets(1).Range("A1").Value
End Sub

The other relatively safe place to store a workbook is in the Library folder. You can use the LibraryPath property instead of the Path property to return a string that specifies the Library folder. The following code shows how you would alter the preceding example to use the LibraryPath property.

Sub OpenBook1()
    LibPath = Application.LibraryPath & Application.PathSeparator
    fName = LibPath & "BOOK1.XLS"
    Set myBook = Workbooks.Open(Filename:=fName)
    MsgBox myBook.Worksheets(1).Range("A1").Value
End Sub

Instead of hard-coding a filename in the Open method, you may want to allow a user to select a file to open. The GetOpenFilename method displays the standard Open dialog box, but it returns a string instead of opening a file. The string contains the fully qualified path and filename. The following example demonstrates the GetOpenFilename method by displaying the return value in a message box and then opening the file.

Sub DemoGetOpenFilename()
    Do
        fName = Application.GetOpenFilename
    Loop Until fName <> False
    MsgBox "Opening " & fName
    Set myBook = Workbooks.Open(Filename:=fName)
End Sub

Creating and Saving Workbooks

You create a new workbook by applying the Add method to the Workbooks collection. Remember to set the return value of the Add method to an object variable so that you can refer to the new workbook in your code.

When you save a new workbook for the first time, use the SaveAs method. For subsequent saves, use the Save method. The GetSaveAsFilename method is very similar to the GetOpenFilename method described in the preceding section. The following code shows how to create a new workbook and then save it using the GetSaveAsFilename method.

Sub CreateAndSave()
    Set newBook = Workbooks.Add
    Do
        fName = Application.GetSaveAsFilename
    Loop Until fName <> False
    newBook.SaveAs Filename:=fName
End Sub

Closing Workbooks

To close a workbook, use the Close method of the Workbook object. You can close a workbook without saving changes, as shown in the following code.

Sub OpenChangeClose()
    Do
        fName = Application.GetOpenFilename
    Loop Until fName <> False
    Set myBook = Workbooks.Open(Filename:=fName)
    '
    ' make some changes to myBook
    '
    myBook.Close savechanges:=False
End Sub

This code uses the GetOpenFilename method to select the workbook to open, makes some changes to the workbook (indicated by the comments), and then closes the workbook without saving the changes.

When to Use the ThisWorkbook Property

Suppose that you're developing a small application using Visual Basic and that you want to save the workbook as a Microsoft Excel add-in when you finish. The macro contains a custom dialog box, Dialog1, and you display the dialog sheet using the following code.

Public DlgValue

Sub DisplayDialog()
    DlgValue = DialogSheets("Dialog1").Show
End Sub

You finish writing and debugging the application, and then you click Make Add-In on the Tools menu, creating an add-in named MYADDIN.XLA. However, when your add-in runs the DisplayDialog procedure, a run-time error occurs (error 1004, "DialogSheets method of Application class failed"). What happened?

In the DisplayDialog procedure, the DialogSheets method applies to the active workbook because you omitted the object qualifier. However, the active workbook isn't MYADDIN.XLA; it's whichever workbook is displayed by Microsoft Excel. You could try the following fix.

DlgValue = Workbooks("MYADDIN.XLA").DialogSheets("Dialog1").Show

This introduces the potential for more bugs, however, because the filename is hard-coded and must be in the current folder. You could use the folder management techniques described in "Opening Workbooks" earlier in this chapter, but there is a much safer and more robust way to fix this bug.

The ThisWorkbook property returns a Workbook object that refers to the workbook containing the code that's running. Therefore, you should use ThisWorkbook whenever you write code you intend to save as an add-in. The following example shows the robust code for the DisplayDialog procedure.

Public DlgValue

Sub DisplayDialog()
    DlgValue = ThisWorkbook.DialogSheets("Dialog1").Show
End Sub