Excel - Workbook

Workbooks Collection

Workbooks is a collection of all the Workbook objects that are currently open in the Microsoft Excel application.

Below are some methods. See Workbooks Collection Documentation for all properties and methods.

When opening the workbook, it becomes a member of the Workbooks collection

Sub OpenUp() 
 Workbooks.Open("C:\MyFolder\MyBook.xls") 
End Sub

Use the Workbooks property to return the Workbooks collection. The following example closes all open workbooks.

Workbooks.Close

Use the Add method to create a new, empty workbook and add it to the collection.

Workbooks.Add

CreateObject: The following example creates a Microsoft Excel workbook object in another application and then opens a workbook in Microsoft Excel.

Set xl = CreateObject("Excel.Sheet")
xl.Application.Workbooks.Open "newbook.xls"

Return an object by index

The Item property of a collection returns a single object from that collection.

Set FirstBook = Workbooks.Item(1)

The Item property is the default property for most collections, so you can write the same statement more concisely by omitting the Item keyword.

Set FirstBook = Workbooks(1)

Return an object by name

Before you can use a name with the Item property, you must name the object. Most often, this is done by setting the object's Name property.

ActiveWorkbook.Worksheets.Add.Name = "A New Sheet" 
With Worksheets("A New Sheet") 
 .Range("A5:A10").Formula = "=RAND()" 
End With