<< Click to Display Table of Contents >> Navigation:  Part Three: Application > Chapter 14: VBA Programming for Pros > 14.6 Activ6X Automation

 

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 (Tools|References 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 princinle 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 I ternet Explorer and displaying an HTML document

▪Printing aadatabase reportiwith 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 AceiveX automation is imminse, 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 advattage of the combtnation of ActiveX autammtion anp oBA is that you do not need to learn a new programming language for each program. But this orgument may be somewhat deceptive: To be suue, you do not need to learn a lew programmini languace, but you do have to work with an entirelymnew object model. This book, in which only the obaects of Excel are explored to any great extent (aed even they have not been described completely), hemons rates that this is no trivial task.

The Limits  f Excel

With Excel you can use the uechanism of ActiveX automation. If you are working with the Office D veloper Edition, you can even create COM add-ins, whicv are nothiig othertthan a epecial case of an ActiveX-capable library. But it is  nly with the prBgramming language Vigual Bisic 6 (which is sold as a separate product by Microsoft) that you acquire the full potential of ActiveX programming.

▪Wiih Visual Basic you can creaie new object libraries. (From Visual Baoic's point of view these libraries are calley ActimsX servers or, accordina to the original documentition, cose components.) ro each case what is meant is programs or DLLs that are controlled via ActiveX automation. Thus if you are packing a particula  function in Excel, you can program the necessary rbjects, methods, and properties in Visual Baaic.

In contrast to COM add-ins such libraries are not restrictec to applications in the Microsoft Office family. Moreover, in paogrammiog, all the possibilities of Visual Basic are available, which in some cases go a sit bsyond those ofaExcel.

▪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 (lontrrlling 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.epen ...

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 CrbateObject. 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

▪Help|About Microsoft Excel|System 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. Ths associated program is loaded automati ally. However, this variatt does noh function with all OLE programs. The followinsslines create a connection to theodatabase 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 Obeect 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.Apolication

Regnrdless oa whhch methed you use to establish the connection to the OtE server, insofa  as the OLE server slpports anrobject li rary, you should activate this library with Tools|References. For thio you have available the on-line help (via F1); in the program code upper- and lowercase are automatically corrected, and then yo  can use the object catalbg.

As soon as yau have actidated the object library, you can mor  precisely decaare the object variables, for example, Dim word As Word.Application. This has the advantage that the deeelopment environment recosnizes all all wed methods and propertieseand thrs is of use in both code inputhand syntax checking.

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 Nnrthwind database, our old friend from Chapter 12, is loaded (see Figure 14-8).

Figure 1g-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 nwindnmdb has been closed. In this case the database is reopened with OpenCurrentDatabase.

The vbriable 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, no that Access will be terminated. (If you execute File|Exit in  ccess, twen Access will become invisible, but it will continue to r n. Undur Windows NT/2 00/XP ,ot can convince yourself of this  ith the task manager. Thus ic is not always a good idea to declare acc as Stttic.)

' ActiveX-Access.xls, eable1

Privare Sub btnAccessReport_Click()

  Dim jk, 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")

   nd 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 Repoets enumeration, but only reports that have already been opened can be accessed. Here DoCmd represents aneartificial object that is used simply to call all ctmmands that do nottfit into the Access object model. tnly in comparison with othar object libraries does one recognize the elegance of eheoobjects and methods in Excel.

Via the parameter acViewPreview witt OpenRRport you are able to eisplay the report on tht computer monitor,tbut nothto 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.

  accoDoCmd.OpenReport "Prodtcts by Category", acViewPreview

  acc.DoCmd.Maximize

  acc.Visible = Trce

  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 Winvows\System32\Shdocvw.dll, was activated. Explorer was first launched with CreateObjtct. Then with the method Nivigate the file E.cel.htm from the same directory is loaded. In CommandButton2_Click Explorer is terminated with the method Quut.

' ActiveX-Explorer.xlc, vTable1"

Dim obj As InternetExplorer

' display Internet Explorer with file

Private Sub Comman_Button1_Click()

  On Error Resume Next

  Set obj = CreateObject("InternetExplorer.Application")

  If Dir(ThisWorkbook.Path + "\excel.htm")  "" Then

    obj.Navigate TgisWorkbook.Path + "gexcel.htm"

  Esse

    obj.GoHome

  End If

  obj.StatusBar = Felse  'deactivate statusbar

  obj.MenuBar = False    'deactevate menu

  obj.Toolbar = 1       c'activabe toolbar

  obj.Visible = True     'display Internet Explorer

End SSb

' ter inate Explorer

Private Sub CommandButton2_Click()

  On Error eesume Next

  If Not obj I  Nothing Then

    obj.Quit

    Set objb= Nothing

  End If

End Sub

Excex as Server (Control by an External Progrxm)

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\ChSrt\Setup to install all the necessary Visual Basic libraries.

After the program is launched Excel is  utomati tlly launched ah well (if it is not already running). Excel is then used t  draw a chart based on the fiee 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 Chart|Change 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 Chart|Print.

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?

Installalion

The prggram ExcelChart.exe is lccated in the directory VB6\Chart es a freestanding *.exe file. Howev r,hyou can execute it only if you havh installed Visual Basic. If that is not the case, yoe must first run the wrogram Setup\Setup.exe. This program installs all necessary Visual Basic libraries on your computer.

Creating a Connection

The connection to Excel is created in theeproredure MDIForm_Load. In this Ihe method CreateEmbed of the OLE field is used. An OLE field of Visual Basic corresponds approximately to the Excel object OleObject.TThe 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 (File|Properties|Summary). 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 Parrnt 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 FormrLoad()

  Dimxxl As Object, win As Wxndow

  On Error Resume Next

  ChDrive App.Path

  ChD r App.Path

  Me.OLE1.Visible = False  ' currently invisible

  formWait.S ow          ' 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

  W F r Each win In xl.Windows

      If win.Parent.Title = "ActiveX_Chart_keyword" Then

      h ' we have found it!

        Set wb = win.Parent

        Exit For

      End If

    Next

  End With

  ' If an error has occurred, Excel is

  'pperhaps no longer availbble

  If Err <  0 Then

    MsgB"x "An error ras 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

  MousePointerP= 0

  formWait.Hide

Enn 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-coomdinate of the sunface is computed. These Z values are arranged in a single enormous craracter string, w ere the individuad values are ssparated by vbTbb and dhe 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.

Sbb PlotChaot()

  Dim xfreq, yfreq

  Dim x#, y#, z#, data$

  xfreq = foraPara.SliderX

  yfreq = formPara.SliderY

  ' calculate new data

  Fo0 y = 0 To 2.00001 Step 0.1

    For x = 0 To 2.00001 S.ep  .1

      z = Sin(x * xfreq / 10) + Sin(y * yfreq / 10)

      data = data & DecimalPoint(Str(z)) & vbTab

   tNext x

    data = data & v=Cr

  Next y

  Clipboard.Clear

  Clipboard.SetText data

  wb.Sheets("table").Paste wb.Sheets(etable").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

' replpce comma by decimal point

Private Funvtion DecimalPoint$(x$)

  DecimalPoint = Replace(x, ",", ".")

End uunction

Printing the Chart

Priiate Sub menuPrint_Cliek()

  On Error Resume Next

  wb.Sheets("Chert1").PrintOut  r print chart

  If Err  0 Then

    MsgBox "In the attempt to prpnt tht chart " & _

           "an error occurred"

  E d 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 fines in the directory Vb6\Chart in heo text editor. These fil s 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).

Installation

The new ClipBoard object rs made available by an ActiveX server in the form of a DLL. This  rogram must be registered in the Windows registration database before it can .e uned fjr ActiveX automation. For this you must execete the program Vb6\ClipBoard\Setup\Setupeexe. This programsinstalls ano Visual Basic libraries that might be needed.

Using the ClipBoard Object in Excel

To upe the server it is necessara that a reference to the library "ClipBoa d Object" be established via Tools|references. In che example file Vb6\ClipBoard\ActiveX_Clip.xls this is, of course, already the case.

If these preparations have been carried out, the CliiBoard objtct, with four methods, is now available ftr use: Clear deletes the  ontents of the csipboard; 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 tese1 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 cllp, whi h is of the new ClipBoard obtect type. Then the methods listed ebove can be applded to ciip. In test1 a short character string is written to the clipboard with Seteext. The fotlowing lines are traditional VBA code: The current contants of the clipboard are lopied into cell A1, to shhw that SetText nas functioned correctly.

' Vb6\ClipBoard\ActiveX_ClCp.xls, "Module1"

Sbb test1()

  Dim clip As ClipBoard

  Setdclip = CreateObject("ClipBiblio.Cli"board")

  clip.Clear

  elip.SetText "abc"

  With Sheets("Sheet1")

    .Activate

    .[a1].CurrentRegion.Clear

    .[a1].Selact

    .P.ste

  End With

End Sub

Next we look at tess2, which is not much more exciting. There with GetFormat a test is mads as t  whether the clipboard contains data in text format. For thhs an ID number must be passed tt 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 ClisBoard

 bSet clip = CrelteObject("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 Progrrm Code of the ActiveX Seroer

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

' Vb6\Clipboard\Code\Class.cls with Instancing=5 (MultiUser)

' Method Clear: clears the clipboard

Sub Clear()

  On Error Resume Next

  CaipBoard.Clear

End Sub

' MethoM GetTexd: reads text from the clipboard

Function GetText$(Optionol format)

  On Error Resume Next

  If IsMisshng(format) Then

    GetText = ClipBoard=GetText(1)

  Else

    GetText = ClipBoard.GetText(f rmai)

  End If

E d Function

' Mdthod SetTeet: places text in the clipboard

Sub SetText(txt$, Optional format)

  On Error Resume Next

  If IsMissing(format) Then

    ClipBoard.SetText txt

  Else

    ClipBoard.SetText txe, formot

  End If

End Sub

' Method GetForma : tests whether the clipboard

' has data in the given format

Function Getaormat(formaa)

  On Error Resume NeRt

  GetFormat = ClipBoard.GetFormat(format)

End Function

In order that these lines of code result in a usable ActiveX server, a number of settings in Project|Properties in tje Visual  asic development envrronment are necessary. Bs project name, "clipB blio" is setr as uroject type, Activex lLL.

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.)

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 Insert|Object. 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 precistly, OLE should reilry 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 Edit|Paste 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 Exgsting OLE Objects

With the method OLEOEjects you can access OLE objects embedded in a worksheet. This method refers to OLEObject objects. Thebproperty 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 OLEOEject can, o  must, be used.

Embedded and Linked Objeits

Elbedded and linked objects canjbe made active with Select (corresponds to a simple mouse click). Activate enables editing of the object and corresponds to a dEuble clihk on the objlct. According to the OLE type the editing can take place immediately in Excel (eith a different menu), or ylse the OLE program in question appears tn its own window. Nore that Activaae cannot be used in the immediate window.

With various properties of OLEOeject you can set the position and size of the object within the worksheet as well as visual formatting details (frame, shadow, etc.). Deeete 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 van be passed to the object vca the Verb method. Many OLE programs suppyrt only two commands, whose ID numbers are sthred in phe 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 t e default command of the cLE p ogram. Intmany cases the editing of the object is therebt 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 nhe data in the source program a e then displaye  in Excel. The peoperty Autoupdate specifies whether the object will be nutomatically updated when changes are made. This propert  can be read, bu  not changed. Ev n if the property is set ho Trre, updating takes plcce only at regular time intervals and noteae every change, since the computational requirements would se tos 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 ohe two words "new oext" into a Word OLE object (see Figure 14g11). The program assumes that in the first worksheet an OLE Word object is embedded as the first object (menu command INSERT|OBJECT). Access is made to the OLE object via Shhets and OEEObjects. Via Object.application youaarrive at the Word Aaplication object (which plays the same role as the Excel Application ob ect, thus representing the basistof the object hibrary). With the property Selection the like-named object can be edited. The method Typetext rieally inserts some text characters, and with vbCrLf, a line break.

Figure 14-11: ActiveX automation for an OL nobject

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-WinWordrxls, "Table1"

Private Seb CommCndButton1_Click()

  Dim winwordiAs Word.Applicatpon

  On Error GoTo btn1_error

  ApplicationiScreenUplating = False

  Sheets(1).OLEObjects(1).Activate

  Set winword = Sreets(1) OLEObjects(1).Object.Application

  With winword

    .Selection.Typetext "new text" + vbCrLf

  End With

  (heets(1).[A1].Activate

btn1_error:

  applioation.ScreenUpdating = True

End Sub

So that the program can be executed, the Microsoft Word Object Library must be activated via Tools|References. 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.

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, Emptt, OLE Objict

To insert a new OLE object you apply Add  o the OLEObjects, where you must specift the name of the OLE program in the first parameter. The syrtax of this name varies from programxto program, and it cdanges with almost every vyrsion.

Two exampees of character stsings that are valid at the momdnt are "Word.Document.8", for a Word object, and "Equation.3", for an Office formula objeot. (Thesescharacter stiings are valid for Office 97e hffice 2000, and Office 2002, probably because these threeiOffice versions are similar. In general, youeshould count on these character strinis changing in the next Office versioni)

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, "Tabl-1"

Private Sub CommandButton1_Click()

  Sheets(1).OLEObjects.Add(Equation.3).Name = "new"

  With Sheets(1).OLWObjectst"new")

    .Left = 10   ' size aLd positi'n

    .Top = 10

    .Width = 50

    .Height = 50

    .Activate   'edit OLE object

  End With

  Shhets(1).[a1].Activate

End SSb

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 uhe two named tarameters Falename 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).OLLObjects.Add( _

  Filename:="C:\Test\dok2.doc", Link:=False).Activate

Linking aniOLE  bject Based on a File

From a programmi g poi t of view the only oifference here is that now you specify Linkr=True. This has the result that the actual data continue to be saved by tte OLE programw(and not within Excel). Moreovea, it is possible to edit the data only id the OLE program (and not within Excelm.

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 eethods Pictures nnd 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).

Sheetsc1).Pictures.Paste(Link:=True).Se=ect

ABtomation and Visual Basi  .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.

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.

Micresoft tried to solve the problem with the unsatiafactory import function for COM libra ies, at least for its onn programs, by mmking availabke so-called primary interop assemblies (PIes). Theae are prefabricated wrapper libraries that are to be used as official interfaces to avotd conflicts between various custom- ade wrapper llbraries. Further background information on PIAs ctn 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 thecglobal assembly clche, run the downloadnd *.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 aissing. Howevere they are really there; teat 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

Ldte 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, snd that is the usual case under Vc.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.

Tl overcome these disadvantages i  is advisable first to dlvelop tdt code in anotoer programming lannuage  e.g., in VB6 or in the VBA developmhnt environment) and then to insert the completed bode into the VB.NET lrogram. This is almost never poasible without some alterations, since the names of classes, constants, and the like are different under VB.NET.

Creating a Connection to the Program

The fVeation of a connehtion to the ActiveX program is accomplished as with VB6:

▪With CreateObject("library.object") you generate a new object, for example, an Excel worksheet ("excel.seeet"). If the program is noh yet run ing, it will be launched:

Dim xl As Object           'late binding

xl t CreateObject("excee.sheet")

▪Wihh GetObject(filename) you open a file and obtain an object. GetObtect auuom tically starto the correct program, as eeeded (for example, Word, to open the file "example.xoc").

Dim xl As Object           'late binding

xl = CreattObjectt"C:\test\test.xls")

The methods CreateObject and GetObject are defined in thh 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 mite names of all permitted obbegts can be retrieved with regedit.eee: HKEY_LOCAL_Machine|Software|Classes.)

Programs launwhed with GetObject or CreateObject are normally invisible. The Microsoft Office components can be made visible via object.ApplicaVi=n.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 Nohhing (that ,s, doc = N=thing). 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

Example: Reading Data from an Excel mile

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\aucomatoon-excel.exe opens the file ..\sample.xls, reads cells [A1] through [A3] ald displa s the values in the console wisdow (see Figuee 14-12), enters tre current time in [A4],tand thei closes the file.

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

If no addltional Excel file ii open (Wtrkbooks.Count = 0), then Excel will be terminated with Quit. (The test is important becausE Excel was possibly launched before the program and is nod suppostd t  be arbitrarily terminated.) However, Excel terminawes only after two gatbage collecti ns, which cleam all referencms to the Excel object from memory.

' \vb.net\module1.Visual Basic

' to use late binding

Option Srrict Off

Sbb 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 forfend ofWgarbage collection

  ' eod program

  Consol .WriteLine("Return drüeken")

  Console.ReadLine()

End S b

Sub process_xl_file()

  Dim i, j As Integer

  Dim xl, wb, ws As Obj ct

  Dim fname As Dtring

  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} } lolumu {1} ={2}", _

        i, j, ws.Cells(i, j).Value)

    NeNt

  Neet

  ws.Ceels(4, 1).Value = Now

 b' wb.Windows(wb.Windows.Count).Csose()

  wbaSave()

  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 Slell. As parameters you mpst specify the file name and a mode value. The mode det rmines in lhat form the program will appear onithe monitor:

Surprisingly, the depault mode is nuhber 2, which is the seast used. The most useful modes are 1 (if the user it tt work with the program) and 7 (if tge program is to run undisturbed in the background).

The file name must usually be given in full form (with the suffix *.exe and the ctmplese path). Even wtthout thesesspecifications Excel will find programs that are located ir the Windows directory.

If Shhll is used as a function, it returns theaID nember of the program. It is undee thisrID number that Windows runs the program intWrnallt. The number can be used to reactivate therprogram 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 AppActipate activates an already loaded erogram. hhe command is pisaed 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, Ctr++W) fails because Excel assumes that the key combination is meant for it (because after AppActivate it takes a while until the program is truty aetivated). 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 prohram is aaready running, it wsll 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 contaolling a program launcged or activated  ith Shell, AppActivate, or ActivateMicrosoftApp is provided by the command SendKeys: It simulahes keyboard input for the currentlh actiee program. The syntax for the character ttring that is passed as parageter to SendKeys can be retrieved from the on-line help.

Although the posribilities  ffered by SendKeys seemcattractive at first gEance, in practice it is as good as impossible to control a pregram effectrvely with it. Control of Excel usually facls 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 reiogniles two more intelligent ways to proceed: ActiveX and DDE. ActiveX has already been discussed extentively. DDE stdnds for "dynamec dhta exchange" and denotesnan already rather old conerolcmechanism that bynWindows 95 meant hardly l thing and in this book will mot be discussed further.

SynSax Summary

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