14.6 ActiveX Automation

ActiveX automation is a control mechanism by means of which one application (Excel, say) can control another application (Word, say). Usually, the controlling program is called the client, the controlled program the server.

VBA in combination with the object library of the server program serves as the control language. If this is to function smoothly, the object library of the server must be activated (ToolsReferences in the development environment). Then all objects, methods , and properties of the server can be used as if they were built-in keywords; that is, the object catalog, on-line help, and so on, are available for the controlling components .

In principle every library under Excel is controlled via ActiveX automation, thus also the VBA, Scripting, and ADO libraries, described extensively in this book. ActiveX automation is actually nothing new, but your daily bread and butter (except that you perhaps did not know until now that every day you are employing ActiveX automation as an internal control mechanism).

You may be asking at this point, Why devote here an entire section to ActiveX automation if everything has already been explained? In fact, there is no systematic reason, but rather a practical one: To this point all that has been written about the use of ActiveX libraries dealt with standard applications. In the foreground was the use of the libraries, not the technical background. In this section we shall consider some of the more exotic forms of ActiveX automation:

Starting Internet Explorer and displaying an HTML document
Printing a database report with Access
Programming a new object library with Visual Basic and using this library in Excel.
Controlling Excel with an external program

The Limits of ActiveX Automation

The potential of ActiveX automation is immense, but alas, there are problems:

The mechanisms for control of external programs are seldom as developed as the use of pure function libraries such as ADO. A desire to experiment is still a requirement for the creation of a functioning program. And if it finally runs with version n , it is completely uncertain whether it will continue to run with version n +1.
ActiveX automation presupposes that the external program in question is installed. This may be the case on your computer, but not necessarily on your client's computer.
A great advantage of the combination of ActiveX automation and VBA is that you do not need to learn a new programming language for each program. But this argument may be somewhat deceptive: To be sure, you do not need to learn a new programming language, but you do have to work with an entirely new object model. This book, in which only the objects of Excel are explored to any great extent (and even they have not been described completely), demonstrates that this is no trivial task.

The Limits of Excel

With Excel you can use the mechanism of ActiveX automation. If you are working with the Office Developer Edition, you can even create COM add-ins, which are nothing other than a special case of an ActiveX-capable library. But it is only with the programming language Visual Basic 6 (which is sold as a separate product by Microsoft) that you acquire the full potential of ActiveX programming.

With Visual Basic you can create new object libraries. (From Visual Basic's point of view these libraries are called ActiveX servers or, according to the original documentation, code components.) In each case what is meant is programs or DLLs that are controlled via ActiveX automation. Thus if you are lacking a particular function in Excel, you can program the necessary objects, methods, and properties in Visual Basic.

In contrast to COM add-ins such libraries are not restricted to applications in the Microsoft Office family. Moreover, in programming, all the possibilities of Visual Basic are available, which in some cases go a bit beyond those of Excel.
Visual Basic also makes possible the programming of new ActiveX controls, which can be used like the MS Forms controls in Excel dialogs and worksheets.

Excel as Client (Controlling External Programs)

Creating a Connection to the Partner Program

Before you can use the control mechanism of ActiveX automation you must create a connection to the OLE program. For this there are the following two possibilities:

You simply create a start object of the ActiveX program. However, this variant functions only if the object library supports this (which is usually the case only for libraries that are not designed as freestanding programs). An example is the ADO library, for which the following two lines suffice to create a connection:
```
  Dim conn As New Connection conn.Open ...  
```
This form of ActiveX automation is the simplest and most convenient variant. (The use of ADO objects is described extensively in Chapter 12.)
You create an object with CreateObject . As parameter you must specify the object class that identifies the program. The following lines create the connection to Word.
```
  Dim word As Object Set word = CreateObject("Word.Document.8")  
```
The character string " Word.Document.8 " identifies the object that you wish to create. Many application programs recognize a number of objects (in Word, for example, "Word.Document" or "Word.Picture.6"; a portion of the available character strings are displayed in the system information program
HelpAbout Microsoft ExcelSystem Info ). A very long list of all registered objects can be obtained with the registration editor (program Regedit.exe in the Windows directory) under the rubric HKEY_CLASSES_ROOT.
You generate an object with GetObject . As parameter you must specify the file name . The associated program is loaded automatically. However, this variant does not function with all OLE programs. The following lines create a connection to the database Access.
```
  Dim access As Object Set access = GetObject("filename.mdb")  
```
To execute these lines Access must be installed. The program will be launched automatically. Not all the objects of the Access library are available for programming.
You may fall back on the Object property of an OLE object in Excel. This method functions only when the program supports OLE.
```
  Dim oleobj as OLEObject, word As Object Set oleobj = Sheets(1).OLEObjects(1) Set word = oleobj.Object.Application  
```

Regardless of which method you use to establish the connection to the OLE server, insofar as the OLE server supports an object library, you should activate this library with ToolsReferences. For this you have available the on-line help (via F1); in the program code upper- and lowercase are automatically corrected, and then you can use the object catalog.

As soon as you have activated the object library, you can more precisely declare the object variables , for example, Dim word As Word.Application . This has the advantage that the development environment recognizes all allowed methods and properties and thus is of use in both code input and syntax checking.

Tip	Object libraries that have never been used will usually not appear in the References dialog. Click on Browse and search for the object library in question. The libraries of the Office components are located in OfficeDirectory/Office.

Remarks

It is absolutely necessary to activate the object library via Tools References only for the first four variants above. If the connection to the ActiveX server is accomplished via CreateObject or GetObject, then the program runs in principle even without a reference to the object library. Now the object variable must be declared with the generally applicable Dim x As Object.

For this reason, however, Excel can test only at run time whether the method or property that you have specified exists. Furthermore, the code that is generated in this case is less efficient than it would have been if an object library had been used (because the definition of the method cannot take place at compile time, but only at run time).

Example: Printing an Access Report

The following example assumes that you have access to Access. (The ADO library is not sufficient!) Although the code looks completely unspectacular, this short procedure actually accomplishes something: Access is launched, and the Northwind database, our old friend from Chapter 12, is loaded (see Figure 14-8).

Figure 14-8: The first page of the report printed by Access

Here some relatively complicated error protection is necessary: It could happen that Access will refuse to be launched (perhaps because it has not been installed). It could also happen that Access is running from the last time the procedure was called, but in the meantime the database file nwind.mdb has been closed. In this case the database is reopened with OpenCurrentDatabase .

The variable acc is defined as static. This has the advantage that Access does not have to be launched anew when the procedure is called. Beginning with the second time, Access appears almost without hesitation ( assuming that you have sufficient RAM). However, you must execute Set acc=Nothing explicitly, so that Access will be terminated . (If you execute FileExit in Access, then Access will become invisible, but it will continue to run. Under Windows NT/2000/XP you can convince yourself of this with the task manager. Thus it is not always a good idea to declare acc as Static .)

 ' ActiveX-Access.xls, Table1 Private Sub  btnAccessReport_Click  ()   Dim ok, fil$, prjName$   On Error GoTo report_error   Static acc As Access.Application report_anothertry:   fil$ = ThisWorkbook.Path + "\nwind.mdb" ' start Access, load database   If acc Is Nothing Then     Set acc = GetObject(fil, "access.project")   End If   On Error Resume Next  ' error if no CurrentProject   prjName = acc.CurrentProject.Name   On Error GoTo report_error   If LCase(prjName) <> "nwind.mdb" Then     acc.OpenCurrentDatabase fil, False   End If

With the Access property DoCmd you can now print out the report defined in Nwind.mdb . (The definition of the report has already taken place in Access.) OpenReport here is a method of the DoCmd object.

Altogether, this object construction works a bit weirdly: There indeed exists a Report object together with a Reports enumeration, but only reports that have already been opened can be accessed. Here DoCmd represents an artificial object that is used simply to call all commands that do not fit into the Access object model. Only in comparison with other object libraries does one recognize the elegance of the objects and methods in Excel.

Via the parameter acViewPreview with OpenReport you are able to display the report on the computer monitor, but not to print it. (The constant acViewPreview is defined in the Access object library.) ActivateMicrosoftApp makes Access the active window; otherwise , Access would be visible only as an icon in the task bar.

 acc.DoCmd.OpenReport "Products by Category", acViewPreview   acc.DoCmd.Maximize   acc.Visible = True   Application.ActivateMicrosoftApp xlMicrosoftAccess   Exit Sub report_error:   ok = MsgBox("An error has occurred: " & Error & vbCrLf & _     vbCrLf & "Another try?", vbYesNo)   Set acc = Nothing   If ok = vbYes Then     On Error GoTo report_error     GoTo report_anothertry   End If End Sub

Example: Displaying HTML Files (Internet Explorer)

In our second ActiveX example Internet Explorer will be launched. Within it an HTML file will be displayed (Figure 14-9). This way of proceeding can serve, for example, as an alternative to displaying one's own help text. (The advantage is that you save the tiresome task of working with the HTMLHelp Workshop.)

Figure 14-9: Display of an HTML file in Internet Explorer

For our example the library Microsoft Internet Controls, file Windows\System32\Shdocvw.dll , was activated. Explorer was first launched with CreateObject . Then with the method Navigate the file Excel.htm from the same directory is loaded. In CommandButton2_Click Explorer is terminated with the method Quit .

 ' ActiveX-Explorer.xls, "Table1" Dim obj As InternetExplorer ' display Internet Explorer with file Private Sub  CommandButton1_Click  ()   On Error Resume Next   Set obj = CreateObject("InternetExplorer.Application")   If Dir(ThisWorkbook.Path + "\excel.htm")  "" Then     obj.Navigate ThisWorkbook.Path + "\excel.htm"   Else     obj.GoHome   End If   obj.StatusBar = False  'deactivate statusbar   obj.MenuBar = False    'deactivate menu   obj.Toolbar = 1        'activate toolbar   obj.Visible = True     'display Internet Explorer End Sub ' terminate Explorer Private Sub  CommandButton2_Click  ()   On Error Resume Next   If Not obj Is Nothing Then     obj.Quit     Set obj = Nothing   End If End Sub

Excel as Server (Control by an External Program)

Up to now we have proceeded from the assumption that program development took place with VBA within Excel and that other programs were being controlled via ActiveX automation. As we mentioned in the introduction to this section, quite another scenario is possible: Program development can take place in the VBA dialect of another application program (Access, Project) or in the freestanding program Visual Basic. Excel is then controlled by an external program and acts as the ActiveX server.

Example: Displaying an Excel Chart in a Visual Basic Program

I have taken this example from my book on Visual Basic 6 (see the References). The example program is a freestanding program (that is, it is not an Excel file, but an independent *.exe file). If you do have not Visual Basic 6 installed on your computer, you must execute the setup program in the example directory VB6\Chart\Setup to install all the necessary Visual Basic libraries.

After the program is launched Excel is automatically launched as well (if it is not already running). Excel is then used to draw a chart based on the file ExcelChart.xls . This chart is displayed in the Visual Basic program in an OLE field (see Figure 14-10).

Figure 14-10: A Visual Basic program that relies on Excel's chart functions

With the menu command ChartChange Parameter two parameters of the graphic can be set. The Visual Basic program then recalculates the z -coordinates for all the points of the graphic and inserts these data via the clipboard into the Excel table. Then the chart is redrawn on the basis of these data. With two Chart commands the appearance of the chart can be changed. In program code the corresponding Excel dialogs are easily inserted. The chart can be printed with ChartPrint.

This Visual Basic program shows how ActiveX automation can be used to display and print a 3-D chart equipped with every refinement, without having to program all these functions anew. Why do otherwise, when Excel does it all so effortlessly?

Installation

The program ExcelChart.exe is located in the directory VB6\Chart as a freestanding *.exe file. However, you can execute it only if you have installed Visual Basic. If that is not the case, you must first run the program Setup\Setup.exe . This program installs all necessary Visual Basic libraries on your computer.

Tip	You can easily undo this installation:With a double click on the Add/Remove Programs icon in the control panel you find yourself in the Windows dialog for deinstallation of programs.

Creating a Connection

The connection to Excel is created in the procedure MDIForm_Load . In this the method CreateEmbed of the OLE field is used. An OLE field of Visual Basic corresponds approximately to the Excel object OleObject . The method CreateEmbed is comparable to CreateObject .

A loop follows in which all currently open windows in Excel are processed , until the currently loaded file is found. As an identifier the character string "ActiveX_Chart_keyword" is used, which was specified as the title in the file ExcelChart.xls (FilePropertiesSummary). The loop gets around a weak point of CreateEmbed : The method creates an object, but it does not return a reference to an object. If Excel is already running and several files are open, this process makes certain that the wrong workbook is not processed. As soon as the window has been found, the Workbook object of the workbook is returned via the Parent property and stored in the global variable wb .

 'VB6\Chart\formOLE.frm Dim wb As Workbook ' initialization: loads Excel file, reference to ' the file in which to save variables wb, ' insert data, display via OLE Field Private Sub  Form_Load  ()   Dim xl As Object, win As Window   On Error Resume Next   ChDrive App.Path   ChDir App.Path   Me.OLE1.Visible = False  ' currently invisible   formWait.Show          ' please wait ...   MousePointer = vbHourglass   With Me     .OLE1.CreateEmbed App.Path + "\ActiveX_Chart.xls"     Set xl = .OLE1.object.Application     ' process all Excel windows,     ' search for newly generated window     For Each win In xl.Windows       If win.Parent.Title = "ActiveX_Chart_keyword" Then         ' we have found it!         Set wb = win.Parent         Exit For       End If     Next   End With   ' If an error has occurred, Excel is   ' perhaps no longer available   If Err <> 0 Then     MsgBox "An error has occurred. " _       & "The program will be terminated. To" _       & "execute this example program Excel 2000 must be installed" _     Unload Me   End If   PlotChart   Me.OLE1.Visible = True   MousePointer = 0   formWait.Hide End Sub

Drawing a Chart

Drawing the chart takes place in a separate procedure. The idea is simple: In two loops for each point in a 21-by-21 element region the Z -coordinate of the surface is computed. These Z values are arranged in a single enormous character string, where the individual values are separated by vbTab and the rows by vbCr ( carriage return). This character string is then transferred to the clipboard.

With the Excel method Paste the data are taken from the clipboard and inserted into table 1 beginning with cell B2. The chart in ExcelChart.xls expects its data in the range A1:V22, where the first row and column are reserved for the axis labels and are already supplied with values. In principle, it would also be possible to write the data directly into the individual cells of the worksheet in a loop, but that would take much longer.

After the transfer of the data the table (and not the chart) is displayed in the OLE field. To overcome this shortcoming the chart sheet is activated and the worksheet made invisible. In principle, each of these measures should suffice individually, but experience has shown that only both commands accomplish the task. It is precisely these niggling details that make life with ActiveX automation so difficult and lead to a great loss of time in the debugging process.

 Sub  PlotChart  ()   Dim xfreq, yfreq   Dim x#, y#, z#, data$   xfreq = formPara.SliderX   yfreq = formPara.SliderY   ' calculate new data   For y = 0 To 2.00001 Step 0.1     For x = 0 To 2.00001 Step 0.1       z = Sin(x * xfreq / 10) + Sin(y * yfreq / 10)       data = data & DecimalPoint(Str(z)) & vbTab     Next x     data = data & vbCr   Next y   Clipboard.Clear   Clipboard.SetText data   wb.Sheets("table").Paste wb.Sheets("table").Cells(2, 2)   ' so that the chart and not the table is displayed   wb.Sheets("chart").Activate   ' Activate alone does not help, for whatever   wb.Sheets("table").Visible = False End Sub ' replace comma by decimal point Private Function  DecimalPoint$(x$)  DecimalPoint = Replace(x, ",", ".") End Function

Tip

In transferring data via the clipboard a period must be given as the decimal point, even if one is using an international version of Excel in which this function is taken over by the comma. Since the Visual Basic function Str provides a comma in some local settings, this is replaced by a period in DecimalPoint.

Printing the Chart

 Private Sub  menuPrint_Click  ()   On Error Resume Next   wb.Sheets("Chart1").PrintOut  ' print chart   If Err  0 Then     MsgBox "In the attempt to print the chart " & _            "an error occurred"   End If End Sub

The remaining code of the program has little to do with Excel and is therefore not of interest to us. If you have Visual Basic, you can look at the remaining procedures yourself. If you do not have access to Visual Basic, you can examine the four *.frm files in the directory Vb6\Chart in any text editor. These files contain the definition of the program's forms and the program code in ASCII format.

New Objects for Excel (ClipBoard Example)

In the two previous sections we have seen how to use already existing object libraries of another program, where Excel was treated once as client, and then as server. Visual Basic offers another, far-reaching, possibility: You can program a new ActiveX server with Visual Basic and then use it from within Excel. In this way you can define new objects, methods, and properties.

This section gives a simple example of this: In Excel there is no way to access properly the contents of the clipboard. You can copy a range of cells to the clipboard or copy the clipboard contents to another range. However, you cannot read or write a character string to or from the clipboard. For many applications this would be desirable. In particular, large data sets could be much more efficiently inserted into a range of cells via the clipboard than in the traditional way (that is, processing each cell individually).

Tip	Since this book is about Excel and not Visual Basic, we shall not go into the details of server programming. In this section we consider only in principle how this could be done and to demonstrate the application of a new OLE server under Excel.

Installation

The new ClipBoard object is made available by an ActiveX server in the form of a DLL. This program must be registered in the Windows registration database before it can be used for ActiveX automation. For this you must execute the program Vb6\ClipBoard\Setup\Setup.exe . This program installs any Visual Basic libraries that might be needed.

Using the ClipBoard Object in Excel

To use the server it is necessary that a reference to the library "ClipBoard Object" be established via ToolsReferences. In the example file Vb6\ClipBoard\ActiveX_Clip.xls this is, of course, already the case.

If these preparations have been carried out, the ClipBoard object, with four methods, is now available for use: Clear deletes the contents of the clipboard; GetFormat tests whether the clipboard contains data in a particular format (for example, text data); SetText inserts text into the clipboard; GetText reads the text contained in the clipboard.

The Excel Program Code

The procedure test1 demonstrates the use of the new object: With CreateObject the connection to the OLE server is set. The reference to the new object is stored in the object variable clip , which is of the new ClipBoard object type. Then the methods listed above can be applied to clip . In test1 a short character string is written to the clipboard with SetText . The following lines are traditional VBA code: The current contents of the clipboard are copied into cell A1, to show that SetText has functioned correctly.

 ' Vb6\ClipBoard\ActiveX_Clip.xls, "Module1" Sub  test1  ()   Dim clip As ClipBoard   Set clip = CreateObject("ClipBiblio.Clipboard")   clip.Clear   clip.SetText "abc"   With Sheets("Sheet1")     .Activate     .[a1].CurrentRegion.Clear     .[a1].Select     .Paste   End With End Sub

Next we look at test2 , which is not much more exciting. There with GetFormat a test is made as to whether the clipboard contains data in text format. For this an ID number must be passed to GetFormat . For VBA applications there are two ID numbers that are relevant: 1 for normal text and 2 for bitmaps. (Moreover, the clipboard can simultaneously contain data in several formats, say ASCII text and text with Word formatting codes. For this reason the format GetFormat must be tested and is not directly returned.) If the clipboard contains text, this is read with GetText and displayed via MsgBox . If a text is too long, MsgBox will truncate it.

 Sub  test2  ()   Dim clip As ClipBoard   Set clip = CreateObject("ClipBiblio.Clipboard")   If clip.GetFormat(1) Then     MsgBox "The clipboard contains data in text format: " & _       Chr(13) & Chr(13) & clip.GetText   End If End Sub

The Program Code of the ActiveX Server

The Visual Basic program ClipBoard.vbp consists of nothing more than the class module Class.cls . There the methods of the new class ClipBoard are defined. The code is very short, since under Visual Basic there is already a predefined ClipBoard object, whose methods have only to be applied.

 ' Vb6\Clipboard\Code\Class.cls with Instancing=5 (MultiUser) ' Method Clear: clears the clipboard Sub  Clear  ()   On Error Resume Next   ClipBoard.Clear End Sub ' Method GetText: reads text from the clipboard Function  GetText$  (Optional format)   On Error Resume Next   If IsMissing(format) Then     GetText = ClipBoard.GetText(1)   Else     GetText = ClipBoard.GetText(format)   End If End Function ' Method  SetText  : places text in the clipboard Sub SetText(txt$, Optional format)   On Error Resume Next   If IsMissing(format) Then     ClipBoard.SetText txt   Else     ClipBoard.SetText txt, format   End If End Sub ' Method GetFormat: tests whether the clipboard ' has data in the given format Function  GetFormat  (format)   On Error Resume Next   GetFormat = ClipBoard.GetFormat(format) End Function

In order that these lines of code result in a usable ActiveX server, a number of settings in ProjectProperties in the Visual Basic development environment are necessary. As project name, "clipBiblio" is set; as project type, Activex DLL.

Object Linking and Embedding (OLE)

OLE denotes the embedding of a document x in another document y and the mechanisms by which x can be processed without leaving the program for y . (More concretely: You embed a Corel Draw graphic in Excel and can edit the graphic without leaving Excel.)

Tip	The abbreviation OLE was used by Microsoft for a while also as a synonym for COM, that is, for Component Object Model (Microsoft's technology for communication between objects). Later, the notion OLE was replaced by ActiveX, so that OLE today (almost) always is used only in its original sense.

For programmers OLE is interesting insofar as an object embedded in an Excel worksheet can be addressed via the Excel object model. This works particularly well when the embedded object on its part can be processed via ActiveX automation, but that is not a necessary presupposition.

Fundamentals

If you wish to embed an object in Excel in interactive mode, you execute InsertObject. The object is now visible within Excel, although its source is another program. If you wish to process an OLE object, you activate it with a double click. Instead of the Excel menu bar the menu bar of the other program is shown.

More precisely, OLE should really stand for "object linking or embedding," since in question are two completely separate mechanisms. What we have just described was object embedding: A freestanding object was embedded in Excel. The data of this object are saved in the Excel file.

In contrast to this we have object linking: In this case a part of a file of the partner program is copied and inserted via the clipboard (in Excel EditPaste Special, option Paste Link) into a second program. If the data in the original program are changed, the data are updated in Excel as well. The data are, however, a part of the original program and are saved by it. A change in the data can be made only in the original program (and not within the object framework in Excel).

Editing Existing OLE Objects

With the method OLEObjects you can access OLE objects embedded in a worksheet. This method refers to OLEObject objects. The property OLEType of this object specifies whether the object is freestanding and embedded ( xlOLEEmbed ) or linked ( xlOLELink ). This distinction is important in programming, since according to the OLE type different properties and methods of OLEObject can, or must, be used.

Embedded and Linked Objects

Embedded and linked objects can be made active with Select (corresponds to a simple mouse click). Activate enables editing of the object and corresponds to a double click on the object. According to the OLE type the editing can take place immediately in Excel (with a different menu), or else the OLE program in question appears in its own window. Note that Activate cannot be used in the immediate window.

With various properties of OLEObject you can set the position and size of the object within the worksheet as well as visual formatting details (frame, shadow, etc.). Delete removes the object (where in the case of an embedded object all the data are lost, while with a linked object it is only the reference to the data in the original program).

A command can be passed to the object via the Verb method. Many OLE programs support only two commands, whose ID numbers are stored in the constants xlOpen and xlPrimary . Verb xlOpen results in the OLE object being able to be edited in a separate window (even if this OLE program would support direct editing within Excel). Verb xlPrimary executes the default command of the OLE program. In many cases the editing of the object is thereby begun within Excel (such as with the method Activate ). However, depending on the OLE program the default command can have a different effect. Additionally, many OLE programs support further commands. The ID numbers of these commands must be taken from the documentation of these programs.

For Linked Objects Only

Linked objects can be updated with the method Update . Any changes in the data in the source program are then displayed in Excel. The property AutoUpdate specifies whether the object will be automatically updated when changes are made. This property can be read, but not changed. Even if the property is set to True , updating takes place only at regular time intervals and not at every change, since the computational requirements would be too great.

Editing OLE Objects with ActiveX Automation

If the OLE program also supports ActiveX automation (which is the case, for example, with all Office components), then with the Object property of the OLEObject object you reach the interface for ActiveX automation. Presumably, you have become dizzy by now with all these objects. Therefore, here is a brief explanation of what is meant by each object: OLEObject is a normal VBA object like Range or Font . Object is a property of OLEObject and refers to a new object that represents the starting point for ActiveX automation.

The following example inserts the two words "new text" into a Word OLE object (see Figure 14-11). The program assumes that in the first worksheet an OLE Word object is embedded as the first object (menu command INSERTOBJECT). Access is made to the OLE object via Sheets and OLEObjects . Via Object.Application you arrive at the Word Application object (which plays the same role as the Excel Application object, thus representing the basis of the object library). With the property Selection the like-named object can be edited. The method Typetext finally inserts some text characters , and with vbCrLf , a line break.

Figure 14-11: ActiveX automation for an OLE-object

Note that the OLE object must be activated with Activate before execution of the automation command. A command for deactivation is lacking, and thus finally a cell of the Excel worksheet is activated, which results in the deactivation of Word.

 ' OLE-WinWord.xls, "Table1" Private Sub  CommandButton1_Click  ()   Dim winword As Word.Application   On Error GoTo btn1_error   Application.ScreenUpdating = False   Sheets(1).OLEObjects(1).Activate   Set winword = Sheets(1).OLEObjects(1).Object.Application   With winword     .Selection.Typetext "new text" + vbCrLf   End With   Sheets(1).[A1].Activate btn1_error:   Application.ScreenUpdating = True End Sub

So that the program can be executed, the Microsoft Word Object Library must be activated via ToolsReferences. Now the expansion of properties and methods of the Word library functions as with the Excel library. The Word object library is available in the object browser; on-line help can be summoned with F1; and so on.

Tip	If the Microsoft Word Object Library is not displayed in the References dialog, click the button Browse and select the file `OfficeDirectory/Office/MSO9.olb` (Office 2002) or `OfficeDirectory/Office10/MSWord.olb` (Office 2000).

Inserting New OLE Objects

If you do not wish to edit existing objects ”as described in the previous pages ” but would like to insert new objects into tables, charts , or dialogs, then you have a number of options: Embedding a new, empty, OLE object; embedding a file; linking a file; and linking an object from the clipboard.

Embedding a New, Empty, OLE Object

To insert a new OLE object you apply Add to the OLEObjects , where you must specify the name of the OLE program in the first parameter. The syntax of this name varies from program to program, and it changes with almost every version.

Two examples of character strings that are valid at the moment are "Word.Document.8" , for a Word object, and "Equation.3" , for an Office formula object. (These character strings are valid for Office 97, Office 2000, and Office 2002, probably because these three Office versions are similar. In general, you should count on these character strings changing in the next Office version.)

The following lines insert an empty object for a mathematical formula into the first worksheet of your workbook. Editing the formula must be done manually. The formula editor cannot be controlled by ActiveX automation. The procedure works only if the formula editor is installed.

 ' OLE-Equation.xls, "Table1" Private Sub  CommandButton1_Click  ()   Sheets(1).OLEObjects.Add(Equation.3).Name = "new"   With Sheets(1).OLEObjects("new")     .Left = 10   ' size and position     .Top = 10     .Width = 50     .Height = 50     .Activate   'edit OLE object   End With   Sheets(1).[a1].Activate End Sub

Embedding an OLE Object Based on a File

To embed a new object whose contents are prescribed by a file of the OLE program, you again use Add . However, now you specify the two named parameters Filename and Link . In the example instruction below the new object is activated at once. You could also proceed as in the example above and first change the position of the object.

 Sheets(1).OLEObjects.Add(_   Filename:="C:\Test\dok2.doc", Link:=False).Activate

Linking an OLE Object Based on a File

From a programming point of view the only difference here is that now you specify Link:=True . This has the result that the actual data continue to be saved by the OLE program (and not within Excel). Moreover, it is possible to edit the data only in the OLE program (and not within Excel).

Inserting and Linking an OLE Object from the Clipboard

The insertion of OLE data from the clipboard proceeds, interestingly, not via OLEObjects and Add , but via the methods Pictures and Paste . The object so inserted counts as a "normal" OLE object, which can be edited after insertion via OLEObjects . A necessity for the correct execution of the following lines is that you first select data in a suitable OLE program and copy it to the clipboard (for example, a paragraph from a Word document).

 Sheets(1).Pictures.Paste(Link:=True).Select

Automation and Visual Basic .NET

With the new .NET framework and its programming languages Visual Basic .NET and C#, communication between programs and components is no longer based on OLE/COM/ActiveX, but on new mechanisms. For reasons of compatibility, ActiveX continues to be supported, though only to a limited extent. This section describes how to run Excel via VB.NET.

Pointer

In the VB.NET documentation the subject of automation is largely ignored. On the other hand, there are some good articles on this issue in the knowledge base. (Additional articles can be found by searching the knowledge base for "automate.NET" or "automation .NET"):

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q301982
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q302814

If you are working with C# instead of with VB.NET, the following article might serve as your starting point:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q302084&

The OLE Field

There is no longer an OLE field in VB.NET (nor a relevant replacement). Thus programs like the chart viewer presented in the earlier section on Excel as server are no longer possible.

Problems with ActiveX Libraries

As with Visual Basic 6 and VBA, in VB.NET programs it is possible to create a reference to the ActiveX libraries. For this, however, the libraries must be equipped with a .NET wrapper. This happens automatically once a reference to the library has been made. Unfortunately, the library adaptation does not yet function satisfactorily. For example, after the importation of the Excel library, most of the objects are missing.

Microsoft tried to solve the problem with the unsatisfactory import function for COM libraries, at least for its own programs, by making available so-called primary interop assemblies (PIAs). These are prefabricated wrapper libraries that are to be used as official interfaces to avoid conflicts between various custom-made wrapper libraries. Further background information on PIAs can be found at http://msdn.microsoft.com/library/en-us/dndotnet/html/whypriinterop.asp

At the time this book went to press, PIAs for OfficeXP (but none for Office 2000) were available at the following site: http://msdn.microsoft.com/downloads/list/office.asp.

To install the libraries into the global assembly cache, run the downloaded *.exe file to extract the files into a directory and then execute the batch file register.bat . When using the libraries, you will notice that most classes still seem to be missing. However, they are really there; that is, you can use the Microsoft.Office.Interop.Excel.Range class even though this class is invisible in the object browser.

Further information on how to use the libraries and on many known issues with the OfficeXP PIAs can be found at

http://msdn.microsoft.com/library/en-us/dnoxpta/html/odc_oxppias.asp
http://msdn.microsoft.com/library/en-us/dnoxpta/html/odc_piaissues.asp

Late Binding

Since using the ActiveX libraries of Excel with or without PIAs can be troublesome , a reasonable alternative is to work without any such library and instead rely on late binding. This means that one uses a variable of type Object in running an ActiveX program with appropriate methods and properties. The compiler cannot, however, check whether these methods or properties even exist. Thus this existence remains an open question. The binding between object and method or property is created only when the program is executed, whence the name late binding . (The opposite of late binding is called early binding , and that is the usual case under VB.NET.)

There are two significant disadvantages to late binding: First, the code development is difficult, since the IntelliSense function does not work, and you must constantly look in the Help resource to see which properties or methods are recognized by a particular class, which parameters and return values are allowed, etc. Second, the compiler cannot check the correctness of the code. Typographical errors such as an incorrect method name are not revealed until the program is executed.

To overcome these disadvantages it is advisable first to develop the code in another programming language (e.g., in VB6 or in the VBA development environment) and then to insert the completed code into the VB.NET program. This is almost never possible without some alterations, since the names of classes, constants, and the like are different under VB.NET.

Tip	VB.NET supports late binding only when Option Strict Off holds! With Option Strict On , only early binding can be used.

Creating a Connection to the Program

The creation of a connection to the ActiveX program is accomplished as with VB6:

With CreateObject("library.object") you generate a new object, for example, an Excel worksheet ( "excel.sheet" ). If the program is not yet running, it will be launched:
```
 Dim xl As Object           'late binding xl = CreateObject("excel.sheet") 
```
With GetObject(filename) you open a file and obtain an object. GetObject automatically starts the correct program, as needed (for example, Word, to open the file "example.doc" ).
```
 Dim xl As Object           'late binding xl = CreateObject("C:\test\test.xls") 
```

The methods CreateObject and GetObject are defined in the Interaction class of Microsoft.VisualBasic . The class to which the returned objects belong depends on the program's class library. Which character strings are permitted to be passed to CreateObject depends on how the programs are registered in the registration database. (Tables with names of all permitted objects can be retrieved with regedit.exe : HKEY_LOCAL_MachineSoftwareClasses .)

Programs launched with GetObject or CreateObject are normally invisible. The Microsoft Office components can be made visible via object.Application.Visible=True .

Closing the Connection

Sometimes, it is more complicated to disconnect from the program to be run (to disconnect from the program that had been automated) when it is no longer needed than to establish the connection. If you don't pay attention, the program will continue to run (perhaps invisibly ).

In order to terminate the program explicitly, you would usually execute object.Application.Quit() . This does not actually terminate the program. To the contrary, it runs (at least) as long as there remain references to any of the program's objects in your VB.NET program. How long there are such references depends on when these are removed from memory by garbage collection.

If you wish for the external program to be terminated as quickly as possible, then you should adopt the following technique: First, see to it that all variables that refer to the program's objects either are no longer valid (since they are declared in a procedure that is no longer running) or are explicitly set to Nothing (that is, doc = Nothing ). Then trigger garbage collection twice explicitly. (Don't ask me why twice. This idea came from a news group contribution and has proven itself in practice.)

 doc.Application.Quit()         'request program to terminate doc = Nothing                  'delete object variables GC.Collect()                   'trigger garbage collection GC.WaitForPendingFinalizers()  'wait for end of garbage collection GC.Collect()                   'execute garbage collection again GC.WaitForPendingFinalizers()  'wait for end of garbage collection

Note

It can happen that Excel is still running when you excute GetObject or CreateObject . Then the new object is generated in the already running instance. In this case you should not terminate the program with Quit . The problem is that it is not easy to determine in VB.NET whether the program is still running. Therefore, you should check before executing Quit whether other documents are still open in the program. (In Excel you can do this with Application.Workbooks.Count .)

Example: Reading Data from an Excel File

Because of the problems that have been described in attempts to import the Excel class library, the example program uses late binding. The program 14\vb.net\bin\automation-excel.exe opens the file ..\sample.xls , reads cells [A1] through [A3] and displays the values in the console window (see Figure 14-12), enters the current time in [A4], and then closes the file.

Figure 14-12: ActiveX Automation for a VB.NET program

If no additional Excel file is open ( Workbooks.Count = 0 ), then Excel will be terminated with Quit . (The test is important because Excel was possibly launched before the program and is not supposed to be arbitrarily terminated.) However, Excel terminates only after two garbage collections, which clear all references to the Excel object from memory.

 ' \vb.net\module1.Visual Basic ' to use late binding Option Strict Off Sub  Main  ()   ' process Excel file   process_xl_file()   ' close Excel   GC.Collect()                   'trigger garbage collection   GC.WaitForPendingFinalizers()  'wait for end of garbage collection   GC.Collect()                   'again trigger garbage collection   GC.WaitForPendingFinalizers()   'wait for end of garbage collection   ' end program   Console.WriteLine("Return drcken")   Console.ReadLine() End Sub Sub process_  xl_file  ()   Dim i, j As Integer   Dim xl, wb, ws As Object   Dim fname As String   fname = IO.Path.Combine(Environment.CurrentDirectory, _     "..\sample.xls")   wb = GetObject(fname)   xl = wb.Application   ' xl.Visible = True   'if you want to see Excel   ' wb.NewWindow()   ws = wb.Sheets(1)   For i = 1 To 3     For j = 1 To 3       Console.WriteLine("Cell in line {0} / column {1} ={2}", _         i, j, ws.Cells(i, j).Value)     Next   Next   ws.Cells(4, 1).Value = Now   ' wb.Windows(wb.Windows.Count).Close()   wb.Save()   wb.Close()   If xl.Workbooks.Count = 0 Then xl.Quit() End Sub

Launching and Controlling Programs Without ActiveX

Launching a Program

If you wish from within a VBA program to launch another Windows or DOS program that cannot be controlled via ActiveX automation, then you must make use of the command Shell . As parameters you must specify the file name and a mode value. The mode determines in what form the program will appear on the monitor:

1	as a normal window with focus
2	as an Icon with focus (the program appears in the task bar, but its window is not opened)
3	as full-screen window with focus
4	as a normal window without focus
7	as an icon without focus

Surprisingly, the default mode is number 2, which is the least used. The most useful modes are 1 (if the user is to work with the program) and 7 (if the program is to run undisturbed in the background).

The file name must usually be given in full form (with the suffix *.exe and the complete path). Even without these specifications Excel will find programs that are located in the Windows directory.

If Shell is used as a function, it returns the ID number of the program. It is under this ID number that Windows runs the program internally. The number can be used to reactivate the program at a later time with AppActivate . The following instruction starts the Notepad program.

 ID = Shell("notepad", 2)

Note, please, that VBA continues its work on the procedure after a short hesitation. Excel and the newly started program now run quasi-simultaneously and independently of one another. There is no way to determine within VBA whether a program is running yet or continues to run. (You would have to employ various DLL system functions that determine information about all running processes. This requires some rather difficult programming that would go outside the subject matter of this book. Details can be found in the KB article Q129796 in the MSDN library.)

Activating a Running Program

The command AppActivate activates an already loaded program. The command is passed as parameter the ID number of an earlier Shell command or the name of the window of the program to be activated. VBA is relatively forgiving in the way it interprets these parameters: Case plays no role. The program is activated as soon as it can be uniquely identified.

AppActivate activates the program, but does not change its state. If the program is currently showing itself as an icon, then it remains an icon. Attempts to shift the program into another state with SendKeys (say, with Alt+spacebar, Ctrl+W ) fails because Excel assumes that the key combination is meant for it (because after AppActivate it takes a while until the program is truly activated). If AppActivate cannot find the specified program, then error 5 occurs (invalid procedure call).

Launching and Activating Microsoft Application Programs

If your goal is not to launch an arbitrary program, but one of the Microsoft application programs (in particular, Word, Access, and so on), you can use the method ActivateMicrosoftApp . This method has the advantage over Shell that you do not need to know the precise file name of the program.

ActivateMicrosoftApp is passed a constant that identifies the program (see the on-line help). Sadly, in the list of programs that can be activated you will not find the most elementary Windows programs (such as Explorer).

If the program is already running, it will be activated as with AppActivate . This method returns True if the program launch or activation was successful, and otherwise, False .

 Application.ActivateMicrosoftApp xlMicrosoftWord

Controlling the Active Program

The simplest way of controlling a program launched or activated with Shell , AppActivate , or ActivateMicrosoftApp is provided by the command SendKeys : It simulates keyboard input for the currently active program. The syntax for the character string that is passed as parameter to SendKeys can be retrieved from the on-line help.

Although the possibilities offered by SendKeys seem attractive at first glance, in practice it is as good as impossible to control a program effectively with it. Control of Excel usually fails because the keys simulated by SendKeys can be processed only when no VBA macro is running. The control of external programs fails because you cannot always foresee how the program will behave. Moreover, you must take care not to send the keys too quickly; otherwise, keys will be "swallowed" or incorrectly interpreted. The speed of the program to be controlled depends, in turn , on the speed of the computer. In short, SendKeys is suitable for a few cute gags, but not for controlling a program.

In addition to this truly inflexible control option VBA recognizes two more intelligent ways to proceed: ActiveX and DDE. ActiveX has already been discussed extensively. DDE stands for "dynamic data exchange" and denotes an already rather old control mechanism that by Windows 95 meant hardly a thing and in this book will not be discussed further.

Syntax Summary

sh stands for a worksheet or chart sheet, oleob for an OLE object.

PROGRAM LAUNCH AND CONTROL
id= Shell("datname")	launch external program
AppActivate "windowtitle"	activate running program
AppActivate id Application.ActivateMicrosoftApp xlXxx	start or activate MS program
SendKeys ".."	simulates keyboard input

OLE, ACTIVEX AUTOMATION
sh.OLEObjects(..)	access OLE objects
sh.OLEObjects.Add	create new OLE object
sh.Pictures.Paste link:=True	insert OLE object from the clipboard
oleob.Select	select object (normal mouse click)
oleob.Activate	activate object (double click)
oleob.Verb xlOpen/xlPrimary	execute OLE command
oleob.Update	update linked OLE object
oleob.Delete	delete OLE object
oleob.Object	reference for ActiveX automation
obj = GetObject("," "ole-name")	reference for ActiveX automation