Excel - Workbook

Card Puncher Data Processing

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

Workbooks.Open Method (Excel)

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

Documentation / Reference





Discover More
Card Puncher Data Processing
Excel - Object

Members: Properties are object variable (in java a public field) (Example: count the number of element in a collection) Methods are function attached to the object Complete...



Share this page:
Follow us:
Task Runner