http://www.microsoft.com/TechNet/desk/office/vba/technote/vba.htm (PC Press Internet CD, 03/1996)
Go To TechNet Home Page
This session is designed to introduce developers to integrating Microsoftr Office applications using Visual Basic, Visual Basic for applications, and OLE Automation. Topics covered in this session include:
For the code examples used in this session see the files OLEAUTO.FRM and OLEAUTO.MAK in the Visual Basic 3.0 sample files and VA301.XLS in the Microsoft Excel 5.0 VBA samples on the TechNet CD for TechEd '94. Additionally, the VBACONST.BAS and XLCONST.BAS files which contain the VBA and Microsoft Excelr constants for Visual Basic respectively have been included. Finally, the OLE 2.0 Object Viewer (OLE2VIEW.EXE) has been included to help implementors of OLE 2.0-enabled applications better understand what is happening in their systems Sample files are available in the Conferences Section of the TechNet CD with the TechEd '94 files.
Why Office Development?
Era of multiple application integration As personal computers are used to solve more and more complex business problems, pulling together the specialized features of multiple applications often results in a better business solution than any single application could provide.
Standards in place for solution development Any third-party product that supports OLE 2.0 and OLE Automation can be integrated seamlessly into an Office-based solution. As more and more products support OLE 2.0, solution builders can create more specific solutions.
Rapid development of better solutions By pulling together the features of existing applications, solution developers can save development and testing time as well as provide their users with the functionality that they are already used to.
New generation of solutions on the desktop MIS/solution builder may create very specific applications for a particular organization (for example, a stock analysis tool for large bank). With support for OLE 2.0, these objects can be integrated in Office solutions.
Microsoft Office Development Platform Using Microsoft Office as a development platform, you can pull together objects from any Microsoft Office application as well as objects from third-party applications and in-house applications to build a complete solution.
Microsoft's Architecture
OLE 2.0
OLE 2.0 is the general term for an inter-application communication protocol. It supports many new features that make it easier for users to seamlessly create compound documents using multiple applications. These include:
OLE Automation is the industry-standard way to automate tasks across applications on the Windows and Macintoshr operating systems.
Visual Basic, Applications Edition (VBA)
VBA is a portable, hostable replacement for Embedded Basic, that extends the Visual Basic programming style to access application-supplied objects in addition to forms.
Major OLE 2.0 Interfaces
As you can see from the illustration below an application can support OLE 2.0 in a variety of ways. Also note that there are different types of support for OLE Automation. We will look at these different types of support when looking at the individual Office applications.
OLE 2.0 -Visual Editing
Support for visual editing of an object is the most common type of OLE 2.0 support seen in applications. While not always directly related to development, embedding of OLE information can be a very useful tool. For example, an Office solution might simply consist of a Microsoft Word for WindowsT document that contains an embedded Microsoft Excel worksheet of PivotTable information.
OLE Automation
OLE Automation is the 'glue' that provides access to all objects. It is an open OLE 2.0-provided service that is fundamental to VBA. OLE Automation allows a user to transparently access objects in VBA host applications and other applications, in the same way that they access forms and controls in VB today. OLE Automation is used by VBA on Windows 3.x, Windows NT and Macintosh platforms.
Putting It Together
An Office solution might incorporate objects from many applications as well as one or more controller applications.
Office Support of OLE 2.0 Automation
Microsoft Excel 5.0 Microsoft Excel 5.0 supports all of the Visual Editing and Automation interfaces of OLE 2.0 including exposing an object library for other applications to control Microsoft Excel. Microsoft Excel 5.0 is also the first application to implement Visual Basic for applications and through VBA can control other OLE Automation applications.
Microsoft Word for Windows 6.0 Microsoft Word for Windows 6.0 supports an OLE Automation interface which allows it to be controlled via OLE Automation. However, Word for Windows 6.0 doesn't implement VBA and can't control other applications by using OLE Automation.
Microsoft Project 4.0 Same as Microsoft Excel 5.0.
Microsoft Access 2.0 Microsoft Access 2.0 can act as a OLE Automation Controller via Access Basic. While Microsoft Access 2.0 does not support an OLE Automation interface to be controlled by other applications, most applications will get to the Microsoft Access data via ODBC.
Microsoft PowerPoint 4.0 Microsoft PowerPoint 4.0 will provide support for visual editing but not for OLE Automation.
Visual Basic 3.0 Support of OLE Automation Visual Basic 3.0 can act as an OLE 2.0 container application for embedded OLE objects (via the OLE 2.0 control) as well as an OLE 2.0 Automation Controller application. However, Visual Basic 3.0 does not support type libraries exposed by other applications (such as Microsoft Excel) because Visual Basic 3.0 was released before this portion of the OLE 2.0 specification was complete. This means that Visual Basic can control OLE Automation applications but can not use any specific object, property, or method information exposed by an OLE Automation application. For example, the Visual Basic compiler would not be able to tell if a Microsoft Excel property used in code was valid or not.
OLE Automation Functions
Set When dealing with two applications that have and understand type libraries you simply use the object in the other application as you would objects in the host application. In the example below the Set statement is used to assign an object variable from Microsoft Project.
set MyProj = MSProject.ActiveProject
CreateObject
Use this function to create an OLE Automation object and assign the object to an object variable. To do this, use the Set statement to assign the object returned by CreateObject to the object variable. For example:
Set WordBasicObject = CreateObject("Word.Basic")
When this code is executed, the application creating the object is started, if it is not already running (Microsoft Word in this example), and an object of the specified type is created. Once an object is created, you reference it in code using the object variable you defined. In the above example, you access properties and methods of the new object using the object variable, WordBasicObject. For example:
WordBasicObject.Insert "Hello, world."
WordBasicObject.FilePrint
The syntax for CreateObject is as follows:
CreateObject(class)
class syntax: "appname.objecttype"
GetObject
Syntax:
GetObject([pathname][,class])
Use the GetObject function to access an OLE Automation object from a file and assign the object to an object variable. GetObject can also be used with some applications to get the running instance of the application instead of starting a new instance with CreateObject. To get an object that refers to a file, use the Set statement to assign the object returned by GetObject to the object variable. For example:
Set CADObject = GetObject("C:\CAD\SCHEMA.CAD")
Object Browser
You can use the Object Browser (currently available in Microsoft Excel and Project) to view available procedures, objects, methods, and properties exposed by any application's object library.
Microsoft Excel Controlling Word for Windows
When controlling Word for Windows from Microsoft Excel it's important to note that Word for Windows only has a single object, the 'Basic' object. All Word for Windows functions are implemented as methods of the 'Basic' object.
When using CreateObject to create an object variable that refers to Word for Windows it uses the running instance of Word for Windows or starts a new instance if Word for Windows is not running. Also, if a new instance of Word for Windows is started, that instance will be closed when the object variable that refers to that instance goes out of scope.
Example of controlling Word for Windows from Microsoft Excel:
Sub CreateWordObject()
Static wd As Object
'Create Word object and assign to object variable
Set wd = CreateObject("Word.Basic")
'Use WordBasic functions as methods of the object
With wd
.FileNewDefault
.Insert "Hello, world!"
End With
End Sub
Microsoft Excel Controlling Embedded Word for Windows Object
When controlling an embedded Word for Windows document object on a Microsoft Excel worksheet, you must first activate the embedded object. Then to get an OLE Automation object to control, use the Object property of the of the OLE object on the worksheet.
The following example first creates an embedded object then controls that object via OLE Automation:
Sub InsertWordObject()
Dim WordObject As Object
Dim WordBasicObject As Object
Worksheets("Word Object").Activate
' add the embedded object
Set WordObject = ActiveSheet.OLEObjects.Add("Word.Document.6")
Set WordBasicObject = WordObject.Object.Application.WordBasic
WordObject.Activate 'Activate the object
WordBasicObject.FontSize 18
WordBasicObject.Insert "Hello, world" 'Insert info into the object
End Sub
Microsoft Excel Controlling Project
Using Microsoft Excel to control objects in Project is nearly identical to controlling native Microsoft Excel objects since both applications provide an object library. In order to use objects and their properties and methods in Project you must first make 'Reference' to Project's object library.
After you have made reference you control Project's objects just as you would control any Microsoft Excel object. For example.
ex. set x = MSProject.ActiveProject
Visual Basic Controlling Microsoft Excel
When using Visual Basic to control Microsoft Excel you must use the CreateObject or GetObject functions to create an object variable that refers to Microsoft Excel. This is necessary because Visual Basic does not understand Microsoft Excel's type library.
When you create a new instance of Microsoft Excel note that Microsoft Excel's Visible Property is False by default. If you want the user to see the instance of Microsoft Excel you must set its visible property to true. Also note that any Microsoft Excel or VBA constants that you wish to use in Visual Basic must be defined. This is easily done with the VBACONST.BAS and XLCONST.BAS files included with the TechEd files.
The following example creates a new workbook in Microsoft Excel and enters some number on a worksheet which are then charted. The chart is then rotated in a for loop.
Sub cmdControlMicrosoft Excel_Click ()
'Code Sample for Tech-Ed 1994
'Written by Craig Lokken, Microsoft Corporation
Dim xl As Object
Dim xlchart As Object
Dim xl3DColumn As Integer
xl3DColumn = -4100 'xl constant for 3D Column chart
'Create Microsoft Excel object
Set xl = CreateObject("Microsoft Excel.Application")
xl.visible = True
'add an Microsoft Excel workbook
xl.workbooks.add
'put values into cells
xl.range("a1").value = 3
xl.range("a2").value = 2
xl.range("a3").value = 1
xl.range("A1:A3").Select
'add a new chart and capture the chart object
Set xlchart = xl.charts.add()
'make the chart 3D Column
xlchart.Type = xl3DColumn
'rotate the chart
For i = 30 To 180 Step 10
xlchart.rotation = i
Next
End Sub
Visual Basic Controlling Embedded Microsoft Excel Object
When using Visual Basic to control an embedded Microsoft Excel object you use Object property of the OLE control to get an OLE automation object. You can then send commands to that embedded control in the same manner that you send command to Microsoft Excel.
Note that you must activate the object in the OLE control before sending OLE automation commands to it. For example:
Sub cmdControlMicrosoft ExcelObject_Click ()
Dim xlchart As Object
Dim i As Integer
OLE1.Action = "[unarchived-media]" 'Activate object
'Get the OLE Automation object from the OLE control
Set xlchart = OLE1.Object
'Rotate the chart object
For i = 0 To 360 Step 10
xlchart.rotation = i
OLE1.Refresh
Next
For i = -60 To 60 Step 10
xlchart.elevation = i
OLE1.Refresh
Next
xlchart.rotation = 60
xlchart.elevation = 30
OLE1.Action = "[unarchived-media]" ' Update Contents, so changes are saved
OLE1.Action = "[unarchived-media]" ' Close Object and return to former appearance
Set xlchart = Nothing
End Sub
Visual Basic Controlling Word for Windows
When using Visual Basic to control Word for Windows note that CreateObject always uses the running instance of Word for Windows if one is available. Also note that Word for Windows is closed (if it was started by CreateObject) if the object variable that refers to Word for Windows goes out of scope.
Example
Sub cmdControlWord for Windows_Click ()
Static wd As Object
'Create Word object and assign to object variable
Set wd = CreateObject("Word.Basic")
'Use WordBasic functions as methods of the object
wd.FileNewDefault
wd.FontSize 24
wd.Insert "Hello, world!"
End Sub
Visual Basic Controlling Embedded Word for Windows Object
When using Visual Basic to control an embedded Word for Windows object you use the Object property of the OLE control to get an OLE automation object. You can then send commands to that embedded control in the same manner that you send commands to Word for Windows.
Note that you must activate the object in the OLE control before sending OLE automation commands to it. For example:
Sub cmdControlWord for WindowsObject_Click ()
Dim wdControl As Object
OLE2.Action = "[unarchived-media]" 'Activate object
'get WordBasic object from OLE control
Set wdControl = OLE2.Object.Application.WordBasic
'send OLE Automation commands to OLE control
wdControl.EditSelectAll
wdControl.Underline
OLE2.Action = "[unarchived-media]" ' Update Contents, so changes are saved
OLE2.Action = "[unarchived-media]" ' Close Object and return to former appearance
Set wdControl = Nothing
End Sub.
Resources
Below are some additional resources that may be useful in doing development with the Microsoft Office.
Go To TechNet Home Page
Go To Microsoft Home Page