Excel - Workbook
Table of Contents
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.
Open
When opening the workbook, it becomes a member of the Workbooks collection
Sub OpenUp()
Workbooks.Open("C:\MyFolder\MyBook.xls")
End Sub
Close
Use the Workbooks property to return the Workbooks collection. The following example closes all open workbooks.
Workbooks.Close
Create
- 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"
Get
Indexing
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)
Naming
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