September 1995
0995 Part No. 65283
To use this article, you need to be familiar with the Microsoft® Visual Basic® Programming System for Windows® or Visual Basic for Applications, which is included in Microsoft Project, Microsoft Excel, and Microsoft Access.
For more information about using OLE Automation, see the Microsoft Solutions Development Kit, the Microsoft Developer Network (MSDN), or product-specific documentation.
Before you start coding, you should understand the basics of object models, OLE Automation, and Microsoft Visual Basic for Applications.
You are a developer. And as a developer, you know that as businesses evolve, compete, and try harder to stay ahead, they demand better decisions, faster responses, and smarter investments from their organizations. People in these businesses are turning to you for the "custom solutions" that meet these needs—solutions that provide decision support, task automation, and integrate data and messaging services.
And if you didn't know it, they needed these solutions yesterday.
To help you build the business solutions your organizations and clients need, this guide provides information on key technologies that are included in Microsoft® Office and Microsoft BackOffice™. Together, these technologies can help you build better solutions faster. They include:
This guide also provides detailed object models and code snippets for the following products:
Finally, because this guide can be a single source for information on so many products, you may want to keep it handy as you design and develop solutions.
In Microsoft Office, each application's functionality is exposed through an object interface. For the purposes of this guide, an object is defined as an item that can be programmed or controlled, such as a document, text box, or pivot table. An object model is a representation—or conceptual map—of an application's functionality in terms of objects. By interacting with an application's object model, you can manipulate the application to add custom functionality, automate processes, or integrate applications across networks.
An application has many different objects that are organized into various levels, as shown in the object model diagrams in this article. You can think of these levels as tiers in a hierarchy. The topmost tier of each application object hierarchy is typically occupied by a single object: Application. The Application object represents the application itself, and all other objects for that application are below the Application object. The second tier consists of a high-level categorization of objects. The third, fourth, and fifth tiers include a variety of additional objects used to access functionality that the second tier objects contain. You traverse the tiers to find the object you want to use.
A group of similar objects can be combined in the hierarchy as a collection. You can work with a member of a collection as a single object or as a member of that collection. For example, suppose a boat (object) is a member of a fleet of boats (collection). You can refer to the boat as a single object or as a member of the collection.
In addition, some objects are grouped into an artificial container, called a metacollection. A metacollection is a wholly contained collection of objects that exist within a defined boundary but are independent of the application's object hierarchy.
Objects have properties and methods. (Although a collection contains groups of objects, a collection itself is also an object, and as such is associated with its own set of properties and methods.) Think of properties as adjectives that describe a characteristic of the object, and methods as verbs that describe what you want the object to do. You use objects by manipulating their properties and methods with lines of code.
To manipulate the properties and methods of an object, you must sometimes reference all objects that lie on the hierarchical path to that object. You traverse that path down to a specific object by using the dot operator (.), as shown in the following sample lines of code:
'references the Color property of the Boat object
Application.Boat.Color = "blue"
'references the Action method of the Boat object
Application.Boat.Action Go
Although these lines of code include the highest hierarchical level, Application, you don't always have to type the entire hierarchical object path to set a property of, or call a method on, a particular object. How far from the top of the hierarchy you must begin depends on the context in which the property setting or method call is made. If you specify the entire hierarchy in your call, you are calling the object explicitly. If you omit higher levels of hierarchy in your object reference, you are calling the object implicitly.
When should you call objects explicitly and when can you call them implicitly? It's usually a subjective decision, based on how certain you are of the situations in which your code may be called within the application. A good approach is to keep track of the context in which object references are made and traverse the hierarchical object path in your call accordingly. If you are unsure of the context in which the code will be executed, use explicit hierarchy paths to avoid any errors that might occur if you use implicit hierarchy paths.
If your code may be executed in a variety of situation within the application, call objects explicitly. If your code will be executed on in specific, known situations within the application, you can save programming time by calling objects implicitly.
The Object Browser, which is included in Microsoft Visual Basic for Applications and in Microsoft Visual Basic, provides information (properties and methods) about the object models of applications on your computer that support OLE Automation. When you want to add the object set of an application to the Object Browser, choose References from the Tools menu of the application you are using. (Note that you must have a Visual Basic module open to do this.) Then select the names of the application that you want to add to your Object Browser. When you add the object set of an application to the Object Browser in this way, you are establishing a reference to that application's object model.
In addition to providing information about object properties and methods, references provide the following benefits at design time (in other words, when you are programming in Visual Basic):
To take advantage of early binding, you should maintain references to the applications against which you are programming.
Note that if you try to use an application-defined constant or data type without the appropriate reference, you will get an error message—either "Variable not defined," or "User defined type not defined," or a generic "OLE Automation Error."
Used together, this guide and the Object Browser are a valuable resource that gives you the convenience of an online reference, along with a clear picture of the object model hierarchies.
Want to see some examples of how to use objects and manipulate their properties and methods? See the "Now You Try" section below.
OLE has evolved into a powerful, software Esperanto—a global language of sorts—that enables software components to work together in many ways. OLE is part of the operating system, and it consists of two major elements: the Component Object Model (COM), which is the underlying architecture, and a wide and growing range of OLE services that enable software integration.
Many OLE services are available today, including services that:
In addition, the list of OLE services available from Microsoft and other vendors continues to expand.
This guide focuses on one of the OLE services available to developers today—OLE Automation.
OLE Automation is an OLE service for integrating development tools and applications. It enables an application to expose its functionality, or to control the functionality of other applications on the same computer or across networks. (Microsoft Visual Basic version 4.0 Enterprise Edition enables Remote Automation between distributed computers.) As a result, applications can be automated and integrated with programming code, creating virtually endless possibilities.
The two key elements of OLE Automation are:
OLE Automation is the umbrella term for the process by which an OLE Automation controller sends instructions to an OLE Automation server (using the functionality exposed by the OLE Automation server), where they are run.
Some Microsoft applications (such as Microsoft Access, Microsoft Excel, and Microsoft Project) contain Visual Basic for Applications and expose their functionality in an object model. These applications are both OLE Automation controllers and OLE Automation servers.
The following section provides a brief overview of some Visual Basic for Applications basics.
Microsoft Visual Basic for Applications is a language engine that includes an advanced editing environment, debugging tools, and uses the Visual Basic programming language. Microsoft has built Visual Basic for Applications into several of its desktop application products (Microsoft Access, Microsoft Excel, and Microsoft Project). Visual Basic for Applications is also the language engine for Microsoft Visual Basic version 4.0.
Visual Basic for Applications makes it easy to work with application object models because it provides several helpful programming constructs and capabilities, including:
The following sections contain descriptions of each of these.
You can query the setting of a specific property as easily as you can set it, simply by assigning the value of a property to a variable. For example, to set a value for the Boat.Color property, you could write a line of code that looks something like this:
Boat.Color = "blue"
If you want to find out the current value of the Boat.Color property, you could assign a variable, such as color_of_boat, to be the value of the property, as shown in this example:
color_of_boat = Boat.Color
The With statement allows you to set multiple properties or methods on the same object in one construct. For example, suppose you want to refer to many properties or methods for one object. You can use the With statement to set multiple properties by typing the object name once:
With Boat
'8.2 and 10 are lengths in meters
.Color = "blue"
.Length = 8.2
.MastHeight = 10
End With
The For Each statement is convenient when you don't know how many objects are in a collection or you don't know their unique identifiers, and you want to take the same action or set the same property for all of the objects. The For Each statement enables you to cycle through the objects in a collection without specifically listing the objects. For example, suppose you want to make all the boats in the Fleet collection blue. One way to do this is by writing a portion of code that looks like this:
For Each Boat in Fleet
Boat.color = "blue"
Next
The lines of code between the "For Each Boat in Fleet" and "Next" lines will be executed on each object in the Fleet collection.
With named arguments, you may change only the arguments that need to be changed, regardless of declared order. Use the := symbol (a colon followed by an equal sign) as the named argument operator. For example, suppose the Boat function has the following properties: Color, Length, and MastHeight:
Boat (color, Length, MastHeight)
The traditional statement would be something like:
Boat ("blue", 8.2, 10)
Or to change just the MastHeight property (with placeholder commas):
Boat (,,10)
Using named arguments, the same statement reads:
Boat MastHeight:=10
Another benefit of named arguments is code maintenance—the above statement is much clearer as to which argument is being set.
The interface you use when you are programming inside an application is identical to the interface you use when you program from outside the application. For example, if you write code in Microsoft Excel to manipulate a worksheet, you can use the same code in Microsoft Access to manipulate a worksheet in Microsoft Excel. Just copy or cut and paste the code into another application—add the appropriate CreateObject syntax to point the code to the target application, and the code is ready to be run.
Now that you've got the basic terms and concepts of object models, OLE Automation, and Visual Basic for Applications under your belt, try your hand at coding with objects. In this section you'll find code samples that can be run in Visual Basic or Visual Basic for Applications. You should be familiar with the language and how the code editor works, including code comments and the line continuation character (an underscore preceded by a space, that continues one line of code onto the next). In addition:
If you are planning to distribute Visual Basic for Applications code to users, you should know that each application that uses Visual Basic for Applications manages references "by project." This means that whether you compile Visual Basic for Applications code by running it or by using the "Compile All Modules" command, information on the applications that are referenced at the time of compilation is saved in the application file (the .XLS, .XLA, .MDB, .MDA, or .MPP file, or Visual Basic .VBJ file).
Managing references by project and storing this information in the application file means that when the file is run on a different computer, the appropriate applications on that computer are referenced automatically. In other words, you (or the user) needn't worry about which applications are or are not referenced in order for the Visual Basic for Applications code to execute properly.
The one thing required for proper code execution is that the type libraries for all referenced applications must be on the user's computer. In most cases this is not an issue because the type library file is installed with the application. However, if you get a type library from another location (Microsoft Word, for example does not ship with a type library), you must distribute the type library with your files and install it in either \Windows\System or in the same directory as the application's .EXE file.
'Requires a reference to the Microsoft DAO 3.0 Object Library.
Option Explicit
Sub Update_Database()
Dim oDAO as DBEngine
Dim dbsNwind As DAO.Database, rstEmployees As Recordset, _
iSalesReps As Integer
Set oDAO = CreateObject("DAO.DBEngine")
Set dbsNwind = oDAO.Workspaces(0). _
'Points to location of Northwind sample in OfficePro95 & Access95
OpenDatabase("C:\MSoffice\access\samples\Northwind.mdb")
Set rstEmployees = dbsNwind.OpenRecordset("Employees")
With rstEmployees
Do Until .EOF
If ![Title] = "Sales Representative" Then
iSalesReps = iSalesReps + 1
.Edit
![Title] = "Account Executive"
.Update
End If
.MoveNext
Loop
End With
'Another (faster) way to do this is with a SQL statement.
'To run this code, comment out the code between "Set rstEmployees..."
'and "End With" and uncomment the three lines below
'dbsNwind. Execute "UPDATE Employees SET Title = 'Account Executive'" & _
'" WHERE Title='Sales Representative'", dbFailOnError
'iSalesReps = dbsNwind.RecordsAffected
MsgBox "There were" & Str$(iSalesReps) & " Sales Representatives"
dbsNwind.Close
End Sub
'Requires a reference to Microsoft Access for Windows 95.
Option Explicit
Sub Show_AccessReport()
Dim objAccess As Access.[_Application]
Set objAccess = CreateObject("Access.Application")
With objAccess
'Points to location of Northwind sample in OfficePro95 & Access95.
.OpenCurrentDatabase "C:\MSoffice\access\samples\Northwind.mdb"
.Visible = True
.DoCmd.OpenReport "Products by Category", acPreview
'acPreview is an Access-defined constant of value = 2.
End With
End Sub
'Requires a reference to the Microsoft Excel Object Library.
Option Explicit
Sub Create_Chart()
Dim objXL As Excel.Application, objXLchart As Excel.Chart, _
iRotate As Integer
Dim MyArray As Variant
MyArray = Array(1, 2, 3)
Set objXL = CreateObject("Excel.Application")
With objXL
.Workbooks.Add
.Range("A1:C1").Value = MyArray
.Range("A1:C1").Select
Set objXLchart = .Charts.Add()
.Visible = True
End With
objXLchart.Type = xl3DColumn
'xl3DColumn is an Excel-defined constant of value = -4100.
For iRotate = 30 To 180 Step 10
objXLchart.Rotation = iRotate
Next
End Sub
'Requires a reference to the OLE Messaging Object Library.
Option Explicit
Sub Create_Message()
Dim objSession As Object, objMessage As Object, objRecip As Object
Set objSession = CreateObject("MAPI.SESSION")
objSession.Logon
Set objMessage = objSession.Outbox.Messages.Add
objMessage.Subject = "Here's the information"
Set objRecip = objMessage.Recipients.Add
objRecip.Name = "Mike Loforte"
objRecip.Type = mapiTo
'mapiTo is an OLE Messaging-defined constant of value = 1.
objMessage.Update
objMessage.Send showDialog:=True
objSession.Logoff
End Sub
'Requires a reference to the Office Binder 1.0 Type Library
' and the Microsoft Word 7.0 Object Library.
Option Explicit
Sub Create_Binder()
Dim objBinder As OfficeBinder.Binder, objWord As WordBasic
Set objBinder = CreateObject("Office.Binder")
objBinder.Visible = True
Set objWord = CreateObject("Word.Basic")
With objWord
.FileNewDefault
.Formatfont Points:=22, Bold:=True, Italic:=True
.Insert "Russell says Binders are cool!"
End With
objWord.InsertPara
objWord.FileSaveAs "c:\Binder Summary.DOC"
Set objWord = Nothing
objBinder.Sections.Add Filename:="c:\Binder Summary.doc"
objBinder.Sections(1).Name = "Binder Summary"
objBinder.SaveAs Filename:="Mybinder.obd", saveOption:=bindDisplayDialog
'bindDisplayDialog is a Binder-defined constant of value = 3.
End Sub
'Requires a reference to the PowerPoint 7.0 Object Library.
Option Explicit
Sub Create_Presentation()
Dim objPPT As PowerPoint.Application, _
objPRES As Presentation, objSLIDE As Slide
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.AppWindow.Visible = True
Set objPRES = objPPT.Presentations.Add
Set objSLIDE = objPRES.SLIDES.Add(1, ppLayOutText)
'ppLayOutText is a PPT-defined constant of value = 2.
With objSLIDE
.objects(1).Text = "PowerPoint Programmability"
.objects(2).Text = "The FivePointStar AutoShape"
'"4800, ...." are measurement units in TWIPS (like Visual Basic)
.objects.AddShape ppShapeFivePointStar, 4800, 4300, 5000, 5000
'ppShapeFivePointStar is a PPT-defined constant of value = 12
.objects(3).GraphicFormat.Fill.ForeColor.RGB = RGB(255, 255, 0)
End With
End Sub
'Requires a reference to the Microsoft Project 4.1 Object Library.
Option Explicit
Sub Create_Project()
Dim oProjApp As MSProject.Application, oProjDoc As Project, i As Integer
Set oProjApp = CreateObject("MSProject.Application")
oProjApp.Visible = True
oProjApp.FileNew SummaryInfo:=False
Set oProjDoc = oProjApp.ActiveProject
For i = 1 To 10
oProjDoc.tasks.Add Name:="Task" & i
Next i
oProjApp.FileSave
oProjApp.Quit
End Sub
'Requires a reference to Microsoft Schedule Plus 7.0 Object Library.
Option Explicit
Sub Create_Appointment()
Dim objSchdPlus As Object, objAppt As Object
Set objSchdPlus = CreateObject("SchedulePlus.Application")
objSchdPlus.Logon
objSchdPlus.ScheduleSelected.Activate
Set objAppt = objSchdPlus.ScheduleSelected.SingleAppointments.New
objAppt.SetProperties Text:="Lunch", _
Start:=Now(), End:=Now() + Format("01:00")
objSchdPlus.Logoff
End Sub
'Requires a reference to the Microsoft Word 7.0 Object Library.
Option Explicit
'Dimensioning the Word object outside of the Sub creates a module-level
'variable that persists after the Sub is done executing.
Public objWord As WordBasic
Sub Create_Document()
Set objWord = CreateObject("Word.Basic")
With objWord
.AppMaximize
.FileNewDefault
.Formatfont Points:=22, Bold:=True, Italic:=True
.Insert "Brett Says 'Hello World'"
.InsertPara
.Formatfont Points:=10, Bold:=False, Italic:=False
.Insert "Brett Says 'Hello World'"
End With
End Sub
'Requires a reference to the Microsoft SQLOLE Object Library.
Option Explicit
Sub Show_SQLStatus()
Dim objSQL As Object, objSQLdb As Object, sMessage As String
Set objSQL = CreateObject("Sqlole.SQLServer")
'Where Corp_Srv is a SQL Server 6.0 Server with valid user name and password
objSQL.Connect "Corp_Srv", "username", "password"
For Each objSQLdb In objSQL.Databases
If objSQLdb.Status <> SQLOLEDBStat_Inaccessible Then
'SQLOLEDBSTAT_Inaccessible is a SQLOLE-defined constant of value 992
sMessage = sMessage & objSQLdb.Name & "=" _
& Format$(objSQLdb.Size) & " Mbytes" & Chr$(10)
End If
Next
objSQL.DisConnect
MsgBox sMessage
End Sub
When you view an application's object model in the Object Browser, you are actually viewing that application's type library. For example, when you see the Microsoft Excel object model in the Object Browser, you are really looking at XL5EN32.TLB. (The "EN" indicates that this is the English-language version type library.) Type library names and locations are provided for each application.
The Microsoft Access object model is provided by MSACCESS.TLB, which is included when you install Microsoft Office Professional or Microsoft Access. By default, MSACCESS.TLB is installed in the \MSOFFICE\ACCESS subdirectory. Online Help for this object model is available in ACCESS.HLP.
The Microsoft DAO object model is provided by DAO3032.DLL or DAO2516.DLL (for DAO version 3.0 or 2.5, respectively), which is included when you install Microsoft Visual C++ version 4.0, Microsoft Excel for Windows 95, Microsoft Visual Basic version 4.0 (Professional and Enterprise editions), or Microsoft Access for Windows 95. By default, DAO3032.DLL or DAO2516.DLL is installed in the . .\DAO subdirectory or \WINDOWS\SYSTEM subdirectory.
The Microsoft Excel object model is provided by XL5EN32.OLB, which is included when you install Microsoft Office or Microsoft Excel. By default, XL5EN32.OLB is installed in the \MSOFFICE\EXCEL subdirectory. Online Help for this object model is available in VBA_XL.HLP.
The OLE Messaging object model is provided by MDISP.TLB or MDISP32.TLB, which are included when you install the client portion of Microsoft Exchange Server. It will also be shipped in future versions of Windows and Windows NT. By default, MDISP.TLB and MDISP32.TLB are installed in the \WINDOWS\SYSTEM subdirectory.
Online Help for this object model is available in OLEMSG.HLP, which is included in the Microsoft Exchange Forms Designer and the Microsoft Solutions Development Kit, version 2.0.
The Microsoft Office Binder object model is provided by BINDER.TLB, which is included when you install Microsoft Office. By default, BINDER.TLB is installed in the \MSOFFICE\OFFICE subdirectory. Online Help for the binder object model is available in VBA_BIN.HLP.
There is a Microsoft Office Object Model (MSO5ENU.DLL) in the References dialog box. Although you do not directly call this object, it provides functionality for the Microsoft Excel, Microsoft Project, and the Microsoft Office Binder document property collection used by the BuiltInDocumentProperties and CustomDocumentProperties collections. You should maintain a reference to this object. For more information about a consistent way to read and write document properties, see the Microsoft Solutions Development Kit, version 2.0.
Microsoft Graph, which ships with Microsoft Word, Microsoft Access, and Microsoft PowerPoint, has a similar object model to the chart object model in Microsoft Excel, except it does not have a Drawing or PageSetup object. It also does not have a way to progammatically add or change the data set. The Graph object model is provided by GREN50.OLB. By default, GREN50.OLB is installed in the ...\MSGRAPH5 subdirectory.
The Microsoft PowerPoint object model is provided by POWERPNT.TLB, which is included when you install Microsoft Office or Microsoft PowerPoint. By default, POWERPNT.TLB is installed in the \MSOFFICE\POWERPNT subdirectory. Online Help for this object model is available in VBA_PP.HLP, which is included in the Microsoft Solutions Development Kit, version 2.0.
The Microsoft Project object model is provided by PJ4EN32.OLB, which is included when you install Microsoft Project. By default, PJ4EN32.OLB is installed in the \MSOFFICE\WINPROJ subdirectory. Online Help for this object model is available in VBA_PJ.HLP.
The Microsoft Schedule+ object model is provided by SP7EN32.OLB, which is included when you install Microsoft Office or Microsoft Schedule+, and is included with the Microsoft Exchange client software when Schedule+ is installed from the Microsoft Exchange server. By default, SP7EN32.OLB is installed in the \MSOFFICE\SCHEDULE subdirectory. Help for this object model is available in the Microsoft Exchange Forms Designer and Microsoft Solutions Development Kit, version 2.0.
The Microsoft Word object model consists of the Wordbasic object. All of the Microsoft Word Basic macro commands are accessible through this object. To find out more about using Word Basic commands, see the online Word Basic Help file, WRDBASIC.HLP, which you can choose to install when you set up Microsoft Word.
The Microsoft Word type library, WB70EN.TLB, ships with the Microsoft Solutions Development Kit, version 2.0, and the Microsoft Access Developer Sampler CD. You should copy this to your system \Windows\System Directory and add it to the reference dialog using the "Browse" command button. Remember to distribute it to users of your solutions.
The Microsoft SQL Server object model is provided by SQLOLE32.TLB, which is included when you install the Microsoft SQL Server Enterprise Manager client or server utilities. By default, SQLOLE32.TLB is installed in the \SQL60\DLL subdirectory. Online Help for this object model is available in SQLOLE.HLP in the Microsoft Solutions Development Kit, version 2.0.