One Step Further: Visual Basic for Applications Collections


One Step Further: Visual Basic for Applications Collections

If you decide to write Visual Basic macros for Microsoft Office applications in the future, you'll find that collections play a big role in the object models of Microsoft Word, Microsoft Excel, Microsoft Access, Microsoft PowerPoint, and several other applications that support the Visual Basic for Applications programming language. In Word, for example, all the open documents are stored in the Documents collection, and each paragraph in the current document is stored in the Paragraphs collection. You can manipulate these collections with a For Each…Next loop just as you did the collections in the preceding exercises.

For example, the following sample code comes from a Word 2003 Visual Basic for Applications macro that uses a For Each…Next loop to search each open document in the Documents collection for a file named MyLetter.doc. If the file is found in the collection, the macro saves the file by using the Save method. If the file isn't found in the collection, the macro attempts to open the file from the c:\vb05sbs\chap12 folder.

Dim aDoc As Document Dim docFound As Boolean Dim docLocation As String docFound = False docLocation = "c:\vb05sbs\chap12\myletter.doc" For Each aDoc In Documents     If InStr(1, aDoc.Name, "myletter.doc", 1) Then         docFound = True         aDoc.Save         Exit For     End If Next aDoc If docFound = False Then     Documents.Open FileName:=docLocation End If

The macro begins by declaring three variables. The aDoc object variable represents the current collection element in the For Each…Next loop. The docFound Boolean variable assigns a Boolean value of True if the document is found in the Documents collection. The docLocation string variable contains the path of the MyLetter.doc file on disk. (This routine assumes that the MyLetter.doc file is with your book sample files in c:\vb05sbs\chap12.)

The For Each…Next loop cycles through each document in the Documents collection, searching for the MyLetter file. If the file is detected by the InStr function (which detects one string in another), the file is saved. If the file isn't found, the macro attempts to open it by using the Open method of the Documents object.

Also note the Exit For statement, which I use to exit the For Each…Next loop when the My Letter file has been found and saved. Exit For is a special program statement that you can use to exit a For…Next loop or a For Each…Next loop when continuing will cause unwanted results. In this example, if the MyLetter.doc file is located in the collection, continuing the search is fruitless, and the Exit For statement affords a graceful way to stop the loop as soon as its task is completed.

Entering the Sample Code

I've included this sample Word 2003 macro to show you how you can use collections in Visual Basic for Applications, but the source code is designed for Word, not the Visual Studio IDE. To try it, you'll need to start Word, click the Macros command on the Macro submenu of the Tools menu, create a new name for the macro (I used OpenMyDoc), and then enter the code by using the Word Visual Basic Editor. (If you aren't working in Word, the Documents collection won't have any meaning to the compiler.)

The completed macro looks as shown in the following Word screen, and you can run it by clicking the Run Sub/UserForm button on the toolbar, just as you would run a program in the Visual Studio IDE.

graphic



Microsoft Visual Basic 2005 Step by Step
Microsoft Visual Basic 2005 Step by Step (Step by Step (Microsoft))
ISBN: B003E7EV06
EAN: N/A
Year: 2003
Pages: 168

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net