InformIT: Sams Teach Yourself Visual Basic 6 in 24 Hours

Introduction to OLE Automation

OLE automation refers to the capability of one application to declare and use ActiveX objects that are actually created by other applications.

As you learned in Hour 21, "Visual Basic and ActiveX," the overall distinction between OLE and ActiveX is becoming blurred. Nevertheless, OLE and ActiveX do work well together to support OLE automation. Although this section only scratches the OLE automation surface, you'll probably be surprised at what OLE automation can accomplish.

Suppose that your application needs to create data files for Excel or Word. Using the normal file access routines you learned in Hour 15, "Visual Basic Database Basics," makes such file creation extremely tedious and bug-prone. How can you find the data format required by Word?

With OLE automation, your Visual Basic application can actually borrow Excel or Word and, behind the user's back without ever showing the other application, make Excel or Word (or any other OLE automation-compatible application) create the data file for you. When finished, the data file will reside on the disk and no traces of the other application will be left. Your user will believe your application created the data file.

To create a Word data file using OLE automation, you must first create an object variable that can reference the Word OLE automation application. Declare such an object variable like this:

Public objWordApp As Object

objWordApp is an object variable that represents the entire Word OLE automation application. The rest of the code will use this application's reference object variable to perform the data-generation task. Nothing about objWordApp lets Visual Basic know that the object is the Word application, so the following statement will link the object variable to Word:

Set objWordApp = CreateObject("Word.Application.8")

The 8 is a property that uses Office 97's Word instead of earlier versions. Before Office 97, which technically contains Word version 8, Word used a language called WordBasic for its automation language. Word 8 uses Visual Basic, which is sometimes called Visual Basic for Applications.

Notice that this isn't a normal assignment statement. The Set keyword tells Visual Basic not to store a value in objWordApp because the Word application isn't a value that you could put into a variable. Set tells Visual Basic to reference the Word application. objWordApp works like a link to Word. Visual Basic will, through OLE automation, transfer functions you apply to objWordApp to the Word application. The CreateObject() function actually starts Word (in the background) and prepares the OLE automation link.

CAUTION

If Word is already running, CreateObject() starts another copy of Word. If you want to use the currently running Word, use GetObject() instead of CreateObject() to borrow the running copy of Word. You can test to see if Word is already running like this:

Set objWordApp = GetObject("", "Word.Application.8")
If objWordApp Is Nothing Then   ' True if not running
Set objWordApp = CreateObject("Word.Application.8")
End If

The null string at the beginning of GetObject() is necessary. If you want to open an existing Word document and work on that document inside Visual Basic, you'll insert the path and filename to that document as the first argument. If you want to use Word to create a new document, leave the null string for the first argument.

Keep in mind that OLE automation is fairly extensive and that you can, through your Visual Basic application, make Word do anything you could do at the keyboard with Word. Therefore, the OLE automation can trigger Word's menus, format text, and save files. You'll apply methods, most of which match Word's menus, to perform these tasks.

Listing 22.2 shows you a complete code set you could use to create a Word document named MyWord.Doc.

Example 22.2. OLE automation code that uses Word to create a Word document.

 1: Dim objWordApp As Object
 2:
 3: ' Create a Word document and add text to it
 4: Set objWordApp = GetObject("", "Word.Application.8")
 5: If objWordApp Is Nothing Then   ' True if not running
 6: Set objWordApp = CreateObject("Word.Application.8")
 7: End If
 8: objWordApp.Documents.Add ' Add a document to the collection
 9:
10:  ' The title will have a blank line after it
11:  ' Move the cursor to the next line (simulate the
12:  ' user pressing Enter) by sending the vbCrLf named
13:  ' literal to the document
14: objWordApp.Documents(1).Content.Font.Size = 28
15: objWordApp.Documents(1).Content.Font.Bold = True
16: objWordApp.Documents(1).Content.InsertAfter _
17: Text:="Why go to Italy?" & vbCrLf & vbCrLf
18: 
19:  ' The body of the document is next
20: objWordApp.Documents(1).Range.InsertAfter Text:= _
21: "Italy sells the best ice cream in the world." & vbCrLf
22: objWordApp.Documents(1).Range.InsertAfter Text:= _
23: "Italy has the best architecture in the world." & vbCrLf
24: objWordApp.Documents(1).Range.InsertAfter Text:= _
25: "(Oh, and did I mention the ice cream?)"
26: 
27:  'Save the document
28: objWordApp.Documents(1).SaveAs "c:\MyWord.Doc"
29:  ' Close the Word document
30: objWordApp.Documents(1).Close
31:  ' Quit the Word application
32: objWordApp.Quit

After running Listing 22.2 (perhaps from an event procedure you tie to a command button), you can open Word and load the MyWord.Doc document created from Listing 22.2. You'll see that the document is fully Word compatible; it should be because Word created it from your application's OLE automation commands. Figure 22.1 shows a Word screen with the document open.

Figure 22.1 The Word document that Visual Basic created with OLE automation.

Summary | Next Section

Previous Section