Creating, Saving, Opening, and Closing Workbook Objects

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

You create a new Workbook object by using the Workbooks collection's Add method. The Add method not only creates a new workbook, but also immediately opens the workbook as well. The Add method also returns an object variable that represents the new workbook just created. The new workbook will contain the number of worksheets specified in the Sheets In New Workbook dialog box on the General tab of the Options dialog box (Tools menu). You can also specify the number of sheets a new workbook will have by using the Application object's SheetsInNewWorkbook property.

You can save a new workbook by using the Workbook object's SaveAs method and specifying the name of the workbook you want to save. If a workbook by that name already exists, an error occurs. When a workbook has been saved by using the SaveAs method, additional changes are saved by using the Workbook object's Save method. You can also save a copy of an existing workbook with a different file name by using the SaveCopyAs method. You can supply a file name to be used with the SaveAs or SaveCopyAs method, or you can use the Application object's GetSaveAsFileName method to let the user supply the name to be used to save the workbook. If the user clicks Cancel in the Save As dialog box, the GetSaveAsFileName method returns False.

Before you save a new workbook by using the SaveAs method, the Workbook object's Name property setting is a value assigned by Excel, such as Book1.xls. After you save the workbook, the Name property contains the name you supplied in the Filename argument of the SaveAs method. The Name property is read-only; to change the name of a workbook, you must use the SaveAs method again, and pass a different value in the Filename argument.

Note   A Workbook object's FullName property contains the object's path and file name, whereas the Path property contains only the saved path to the current workbook. Before a new workbook is saved, the FullName property has the same as the Name property, and the Path property has no value.

The Workbooks collection's Open method opens an existing workbook. When you open a workbook by using the Open method, it also becomes the active workbook. You can supply a file name to be used with the Open method, or you can use the Application object's GetOpenFileName method to let the user select the workbook to open. If the user clicks Cancel in the Open dialog box, the GetOpenFileName method returns False.

You use a Workbook object's Close method to close an open workbook. To specify whether pending changes to the workbook should be saved before the object is closed, you use the SaveChanges argument. If the SaveChanges argument is omitted, the user is prompted to save pending changes. You can also use the Close method of the Workbooks object to close all open workbooks. If there are unsaved changes to any open workbook when this method is used, the user is prompted to save changes. If the user clicks Cancel in this Save dialog box, an error occurs. You can suppress this Save dialog box by setting the Application object's DisplayAlerts property to False before executing the Close method. When you use the Workbooks object's Close method in this manner, any unsaved changes to open workbooks are lost. After the Close method has run, remember to set the DisplayAlerts property to True.

Note   The Auto_Open and Auto_Close procedures are ignored when a workbook is opened or closed by using the Open or Close methods. You can force these procedures to run by using the Workbook object's RunAutoMacros method. The Microsoft® Visual Basic® for Applications (VBA) code in a workbook's Open and BeforeClose event procedures will be executed when the workbook is opened or closed by using the Open or Close methods.

The following example illustrates how to create a new workbook and specify the number of worksheets it will have:

Function CreateNewWorkbook(Optional strBookName As String = "", _
      Optional intNumSheets As Integer = 3) As Workbook
   ' This procedure creates a new workbook file and saves it by using the path
   ' and name specified in the strBookName argument. You use the intNumsheets
   ' argument to specify the number of worksheets in the workbook;
   ' the default is 3.
   Dim intOrigNumSheets      As Integer
   Dim wkbNew                As Excel.Workbook
   
   On Error GoTo CreateNew_Err
   
   intOrigNumSheets = Application.SheetsInNewWorkbook
   If intOrigNumSheets <> intNumSheets Then
      Application.SheetsInNewWorkbook = intNumSheets
   End If
   Set wkbNew = Workbooks.Add
   If Len(strBookName) = 0 Then strBookName = Application.GetSaveAsFilename
   wkbNew.SaveAs strBookName
   Set CreateNewWorkbook = wkbNew
   Application.SheetsInNewWorkbook = intOrigNumSheets
   
CreateNew_End:
   Exit Function
CreateNew_Err:
   Set CreateNewWorkbook = Nothing
   wkbNew.Close False
   Set wkbNew = Nothing
   Resume CreateNew_End
End Function

Note   A Workbook object's Saved property is a Boolean value indicating whether the workbook has been saved. The Saved property will be True for any new or opened workbook where no changes have been made and False for a workbook that has unsaved changes. You can set the Saved property to True. Doing this prevents the user from being prompted to save changes when the workbook closes but does not actually save any changes made since the last time the workbook was saved by using the Save method.

A Note About Working with Workbooks Through Automation

When you are using Automation to edit an Excel workbook, keep the following in mind.

Creating a new instance of Excel and opening a workbook results in an invisible instance of Excel and a hidden instance of the workbook. Therefore, if you edit the workbook and save it, the workbook is saved as hidden. The next time the user opens Excel manually, the workbook is invisible and the user has to click Unhide on the Window menu to view the workbook.

To avoid this behavior, your Automation code should unhide the workbook before editing it and saving it. Note that this does not mean Microsoft® Excel itself has to be visible.

See Also

Working with Microsoft Excel Objects | Understanding the Workbook Object