The Workbooks Collection

The Workbooks collection consists of all the currently open Workbook objects in memory. Members can be added to the Workbooks collection in a number of ways. You can create a new empty workbook based on the default properties of the Workbook object, or you can create a new workbook based on a template file. Finally, you can open an existing workbook file.

To create a new empty workbook based on the default workbook, use the Add method of the Workbooks collection:

Workbooks.Add

The new workbook will be the active workbook, so you can refer to it in the following code as ActiveWorkbook. If you immediately save the workbook, using the SaveAs method, you can give it a filename that can be used to refer to the workbook in later code, even if it is no longer active. Before you try the following code, make sure you have a C:\Data directory or change the directory name used in the code:

Workbooks.Add

ActiveWorkbook.SaveAs

Filename:=

="C:\Data\SalesData1

.xlsx"

Workbooks.Add

ActiveWorkbook.SaveAs

Filename:=

="C:\Data\SalesData2

.xlsx"

Workbooks("SalesData1.

.xlsx").Activate

However, a better technique is to use the return value of the Add method to create an object variable that refers to the new workbook. This provides a shortcut to refer to your workbook, and you can keep track of a temporary workbook without the need to save it:

Sub NewWorkbooks()

Dim wkbl As Workbook

Dim wkb2 As Workbook

Set wkbl = Workbooks.

.Add

Set wkb2 = Workbooks.

.Add

wkbl.Activate

End Sub

The Add method allows you to specify a template for the new workbook. The template does not need to be a file saved as a template, with an .xltx extension—it can be a normal workbook file with an .xlsx extension. The following code creates a new, unsaved workbook called SalesDataX, where X is a sequence number that increments as you create more workbooks based on the same template, in the same way that Excel creates workbooks called Bookl, Book2, and so forth when you create new workbooks through the user interface:

Set wkbl = Workbooks.Add(Template:="C:\Data\SalesData.xlsx")

To add an existing workbook file to the Workbooks collection, you use the Open method. Once again, it is a good idea to use the return value of the Open method to create an object variable that you can use later in your code to refer to the workbook:

Set wkbl = Workbooks.Open(Filename:="C:\Data\SalesData1.xlsx")

Many of the examples have data, such as filenames, hard coded. That is, data is placed inside the code instead of putting it into a variable and using the variable in the code. This is not good programming practice, in general, and isn't to be recommended. However, examples will continue in this format in order to simplify the code.

0 0

Post a comment