| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Although collections and the objects they contain, such as the Workbooks collection and the Workbook object, are distinct objects each with their own properties and methods, they're grouped as one unit in most object model graphics to reduce complexity. To return a single member of a collection, you usually use the Item property or method and pass the name or index number of the member as the index argument. For example, in Excel, the following expression returns a reference to an open workbook by passing its name "Sales.xls" to the Item property and then invokes the Close method to close it:
Workbooks.Item("Sales.xls").Close
The Item property or method is the default for most collections, so you can usually omit it from your expression. For example, in Excel, the following two expressions are equivalent:
Workbooks.Item("Sales.xls")
-or-
Workbooks("Sales.xls")
To reference items in a collection by using an index number, simply pass the number of the item to the Item property or method of the collection. For example, if Sales.xls is the second workbook in the Workbooks collection, the following expression will return a reference to it:
Workbooks(2)
Note Most collections used in Office applications (except Access) are one-based, that is, the index number of the first item in the collection is 1. However, the collections in Access and some components, such as ADO and DAO, are zero-based, that is, the index number of the first item is 0. For more information, refer to the Visual Basic Reference Help topic for the collection you want to work with.
You can also create new objects and add them to a collection, usually by using the Add method of that collection. The following code fragment creates a new document by using the Professional Memo.dot template and assigns it to the object variable docNew:
Const TEMPLATE_PATH As String = "c:\program files\microsoft office\templates\1033\"
Dim docNew As Word.Document
Set docNew = Documents.Add(Template:=TEMPLATE_PATH & "memos\professional memo.dot")
Note The template path in this example shows the language ID folder (1033) for templates installed by Microsoft Office for U.S. English language support. The language ID folder below C:\Program Files\Microsoft Office\Templates differs for each language. Templates you create yourself should be stored in the C:\Windows\Application Data\Microsoft\Templates folder or in a user profile-specific location. For more information about template deployment, see Chapter 2, "Designing and Deploying Office Solutions."
You can find out how many objects there are in a collection by using the Count property. The following Excel example displays a message box with the number of workbooks that are open:
MsgBox Workbooks.Count & " workbooks are open."
You can perform an operation on all the objects in a collection, or you can set or test a value for all the objects in a collection. To do this, you use a For Each…Next structure, or a For…Next structure in conjunction with the Count property to loop through all the objects in the collection.
Whenever possible, you should use a For Each…Next loop when you need to work with all the items in a collection. A For Each…Next loop generally performs faster and doesn't require you to use or test a loop counter, which can introduce errors. The following Excel example contains a For Each…Next structure that loops through the Worksheets collection of a workbook and appends " - By Automation" to the name of each worksheet:
Sub CreateExcelObjects()
Dim xlApp As Excel.Application
Dim wkbNewBook As Excel.Workbook
Dim wksSheet As Excel.Worksheet
Dim strBookName As String
' Create new hidden instance of Excel.
Set xlApp = New Excel.Application
' Add new workbook to Workbooks collection.
Set wkbNewBook = xlApp.Workbooks.Add
' Specify path to save workbook.
strBookName = "c:\my documents\xlautomation.xls"
' Loop through each worksheet and append " - By Automation" to the
' name of each sheet. Close and save workbook to specified path.
With wkbNewBook
For Each wksSheet In .Worksheets
wksSheet.Name = wksSheet.Name & " - By Automation"
Next wksSheet
.Close SaveChanges:=True, FileName:=strBookName
End With
Set wkbNewBook = Nothing
Set xlApp = Nothing
End Sub
The CreateExcelObjects procedure is available in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.
Under some circumstances, you must use a For…Next loop to work with items in a collection. For example, if you try to use a For Each…Next loop to delete all the objects in a collection, only every other object in the collection will be deleted. This is because after deleting the first item, all items in the collection are reindexed so that what was the second item is now the first. When the Next statement runs at the end of the first execution of the loop, the pointer is advanced one, skipping that item for the next iteration of the loop. For this reason, to delete all items in a collection, you must use a For…Next loop that starts from the end of the collection and works backwards. The following Binder example uses a For…Next structure to loop through the entire Sections collection of a binder starting from the end, deleting each section:
Sub DeleteDocsFromBinder()
Dim bindApp As OfficeBinder.Binder
Dim intSectionCount As Integer
Const ERR_FILE_EXISTS As Long = 6546
On Error GoTo DeleteDocsFromBinder_Err
' Create new hidden instance of Binder.
Set bindApp = New OfficeBinder.Binder
With bindApp
' Make Binder visible and open binder created with
' AddDocsToBinder procedure.
.Visible = True
.Open ("C:\My Documents\NewDocs.obd")
' Loop through each section and delete it.
For intSectionCount = .Sections.Count To 1 Step -1
.Sections(intSectionCount).Delete
Next
.Save
.Close
End With
DeleteDocsFromBinder_End:
Set bindApp = Nothing
Exit Sub
DeleteDocsFromBinder_Err:
Select Case Err.Number
Case ERR_FILE_EXISTS
MsgBox "File already exists."
Case Else
MsgBox "Error: " & Error.Number & " " & Error.Description
Resume DeleteDocsFromBinder_End
End Select
End Sub
The DeleteDocsFromBinder procedure is available in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.
Another situation that requires you to use a For…Next loop to work with items in a collection is if you need to work with only a certain number of items, say the first ten, or every tenth item.