Microsoft Office Automation with Visual FoxPro
Communicating events with VFPCOM
Throughout this book, we ve concentrated on telling the servers what to do. Communication to the server takes the form of calling methods and setting properties; communication from the server takes the form of VFP initiating a query of a property or getting a return value from method calls. Communication seems a bit lopsided, requiring VFP to start all the conversations. This seems strange when contrasted to COM (ActiveX) controls, which can communicate back to VFP through event code.
While FoxPro can see and act on events raised by ActiveX controls, it cannot see any events raised by objects created with the CreateObject() function. This can lead to some awkward kludges, like having VFP sit in a loop waiting for the status of a server to change, or invoking a server and leaving the VFP screen with a message displaying, "Press this button when you are done." In contrast, Visual Basic has a WITH EVENTS construct that allows events in the servers VB invokes to fire code within VB. Shortly after VFP 6.0 shipped, the wizards at Microsoft came up with a COM control called VFPCOM that solves this problem. VFPCOM binds the server s events to an event handler object you create, which has methods with the same names as the events of the server object. When Word raises its DocumentBeforeClose event, VFPCOM looks for a DocumentBeforeClose method in the event handler object you ve crafted.
Obtaining VFPCOM.DLL
You ll need to go get VFPCOM from Microsoft s web site, at msdn.microsoft.com/vfoxpro. Follow the links to the Product Updates page (as of this writing, it s under Samples and Downloads on the main page). Then select the VFPCOM utility to download. It s approximately 156KB. Save the VFPCOM.EXE file to disk. While you re at the web site, be sure to save or print the ReadMe file, which is not included in the EXE.
When you run VFPCOM.EXE to install it, it places the DLL and its sample files in a \VFPCOM directory underneath VFP s main directory (unless you specify otherwise). It also registers the DLL.
Using VFPCOM.DLL
The first step in using VFPCOM is to create an object reference. Use the class name in the CreateObject() function, like this:
oVFPCOM = CreateObject("vfpcom.comutil")
To use VFPCOM, you also need a reference to an Office server. For the examples, we ll use Word (if you re trying this interactively, do yourself a favor and make it visible so you don t orphan an invisible instance of the server):
oWord = CreateObject("Word.Application")
oWord.Visible = .T.
The VFPCOM server has five methods, but only two are important to us: ExportEvents and BindEvents. The ExportEvents method provides the answer to the question, "But how do I find all the names of the events to write that event handler object?" ExportEvents not only extracts the names of the events, it creates a program file that holds the entire class definition! WOW! The method takes two parameters: the first is the object reference whose events you want to export, and the second is the filename to write all this to. Assuming you ve created oVFPCOM and oWord (as shown previously), create the file WordEvents.PRG like this:
oVFPCOM.ExportEvents(oWord, "WordEvents.PRG")
And then, like magic, the file WordEvents.PRG contains this:
DEFINE CLASS ApplicationEvents2 AS custom
PROCEDURE DocumentBeforeClose(Doc,Cancel)
* Add user code here
ENDPROC
PROCEDURE DocumentBeforePrint(Doc,Cancel)
* Add user code here
ENDPROC
PROCEDURE DocumentBeforeSave(Doc,SaveAsUI,Cancel)
* Add user code here
ENDPROC
PROCEDURE DocumentChange
* Add user code here
ENDPROC
PROCEDURE DocumentOpen(Doc)
* Add user code here
ENDPROC
PROCEDURE NewDocument(Doc)
* Add user code here
ENDPROC
PROCEDURE Quit
* Add user code here
ENDPROC
PROCEDURE WindowActivate(Doc,Wn)
* Add user code here
ENDPROC
PROCEDURE WindowBeforeDoubleClick(Sel,Cancel)
* Add user code here
ENDPROC
PROCEDURE WindowBeforeRightClick(Sel,Cancel)
* Add user code here
ENDPROC
PROCEDURE WindowDeactivate(Doc,Wn)
* Add user code here
ENDPROC
PROCEDURE WindowSelectionChange(Sel)
* Add user code here
ENDPROC
ENDDEFINE
Cool, no? Just change the name of the class from ApplicationEvents2 to WordEvents, change the parameter variable names to your own naming convention, then add your code, and you ve got your class definition.
The next step is to use the BindEvents method to tell VFPCOM which object is the server and which object contains the methods that are mapped to the events. The first parameter is the object reference to the Office application; the second is the object reference to the object (the skeleton of which could be the output of ExportEvents).
oWordEvents = CreateObject("WordEvents")
oVFPCOM.BindEvents(oWord, oWordEvents)
Be sure you ve scoped the variables for the VFPCOM utility and the event handler objects (oVFPCOM and oWordEvents) so they are available while the Office application reference is available.
Let s examine a small example that shows how VFPCOM works. Listing 4 shows a very pared-down version of what s needed to handle Word s events. Listing 4 is available as VFPCOMandWord.PRG in the Developer Download files available at www.hentzenwerke.com.
Listing 4. A simple example of how VFPCOM handles Word s events. This interactive example illustrates how VFPCOM handles Word s BeforeClose event.
* Clean out any existing references to servers.
* This prevents memory loss to leftover instances.
RELEASE ALL LIKE o*
* Make the following variables public for demonstration
* purposes, so they are available after this PRG has run
* to demonstrate the VFPCOM functionality.
PUBLIC oWord, oWordEvents, oVFPCom
* Create the server application
oWord = CreateObject("Word.Application")
* Make it visible
oWord.Visible = .T.
oWord.Documents.Add()
oWord.Documents[1].Range.Text = "This is a test."
* Create the VFPCOM object
oVFPCom = CreateObject("vfpcom.comutil")
* Create the event handler
oWordEvents = CreateObject("WordEvents")
* Bind the server to the event handler
oVFPCom.BindEvents(oWord, oWordEvents)
* Bring VFP to the top and tell the user what to do next
_SCREEN.AlwaysOnTop = .T.
_SCREEN.AlwaysOnTop = .F.
MessageBox("When you close this box, you'll be put in Word." + CHR(13) + ;
"Close down Word to see how VFPCOM handles it." + CHR(13) + ;
"VFP should give 'BeforeClose' MessageBox, " + CHR(13) + ;
"then Word should give its Save dialog." )
oWord.Activate() && Activate Word
* The definition for the pared-down event handler
DEFINE CLASS WordEvents AS Custom
PROCEDURE DocumentBeforeClose(lCancel, oDoc)
* Cheap way to bring VFP to the front.
_SCREEN.AlwaysOnTop = .T.
_SCREEN.AlwaysOnTop = .F.
MessageBox("BeforeClose")
* Go back to Word to see what it presents (prevent Alt-Tab)
oWord.Activate()
ENDPROC
ENDDEFINE
As you can see, the event handler has been trimmed to handle only one event; that s simply to save space in the book run the ExportEvents method to get the full code. The code that is run is a simple MessageBox, to show you where your code would run. It also handles moving the proper windows on top, so you don t have to Alt-Tab between applications.
After this code runs, the PUBLIC variables are still in scope, so the event handler still works. When you close the message box, VFP activates the Word window. Close either the Document or the Application (both fire the DocumentBeforeClose event). When the DocumentBeforeClose event fires in the WordEvents object, VFP comes forward, gives a simple "BeforeClose" message box, then puts you back in Word to see that Word continues with its Save dialog.
Imagine the possibilities there are many events to trap, and with total access to the document, you have lots of possibilities. Imagine letting the user edit the Word document, then passing the contents of the document back to VFP to put in a text control when they save the document. You can trap for a right-click, and run code based on where the user right-clicked in the document. You can trap for quitting Word, and remind the user that they can t close Word while the Automation code is running. Cool. Way cool.
VFPCOM with Excel
VFPCOM works well with Excel, with one minor difference: there are different events for the Application object, the Workbook object, and the Worksheet object. Run the ExportEvents with oExcel, oBook, and oSheet and compare the results. Make an event handler for each kind of object, then use BindEvents to bind each kind of object to its event handler.
VFPCOM with PowerPoint
VFPCOM works quite a bit differently with PowerPoint. An inconsistency in VFPCOM and/or in PowerPoint s object model means that VFPCOM doesn t work as advertised. One of the first indicators is that VFPCOM s ExportEvents results in an empty file; none of the events appear as if they re exposed. Also, if you try to BindEvents, you ll receive an error: "OLE Error code 0x80070002: The system cannot find the file specified."
Fortunately, there is a workaround. Our thanks to John V. Petersen, who has written a DLL in Visual Basic, which takes care of the problem. The DLL, as well as its source, is available as a series of vbPowerPoint files (DLL, VBP, LIB, and EXP) in the Developer Download files available at www.hentzenwerke.com. The DLL uses VB to create a class, which is an instance of PowerPoint. This VB instance exposes the event interface. You need to register the DLL before you use it. Run the following command from the Start button s Run menu option:
REGSVR32 C:\YourDriveAndPathHere\vbPowerpoint.dll
Be sure that the path is appropriate for your machine. To uninstall this DLL, issue the same command, adding " /u" to the end.
Listing 5, stored as VFPCOMandPPT.PRG in the Developer Download files available at www.hentzenwerke.com, shows how to use the DLL to get VFP to work. The main difference is that oVBPowerPoint is created from the VB class and contains the PowerPoint application in the oPowerPoint property.
Listing 5. Using VFPCOM with PowerPoint. A workaround is required here involving vbPowerPoint.DLL.
* Clean out any existing references to servers.
* This prevents memory loss to leftover instances.
RELEASE ALL LIKE o*
* Make the following variables public for demonstration
* purposes, so they are available after this PRG has run
* to demonstrate the VFPCOM functionality.
PUBLIC oVFPCom, oVBPowerPoint, oVFPPowerPoint
#DEFINE ppLayoutTitle 1
* Create an instance of VFPCOM
oVFPCom = CreateObject("vfpcom.comutil")
* Create the VB class, which, in turn, creates PowerPoint
oVBPowerPoint = CreateObject("vbPowerPoint.Class1")
* Add a small presentation
oVBPowerPoint.oPowerPoint.Visible = .T.
oPres = oVBPowerPoint.oPowerPoint.Presentations.Add()
oSlide = oPres.Slides.Add(1, ppLayoutTitle)
oSlide.Shapes[1].TextFrame.TextRange.Text = "This is a Test"
oSlide.Shapes[2].TextFrame.TextRange.Text = "It is ONLY a Test."
* Create an instance of the event handler object
oVFPPowerPoint = CreateObject("vfpPowerPoint")
* Bind PowerPoint to the error handler object
oVFPCom.BindEvents(oVBPowerPoint, oVFPPowerPoint)
* Let the user in on what to do
_SCREEN.AlwaysOnTop = .T.
_SCREEN.AlwaysOnTop = .F.
MessageBox("When you close this box, you'll be put in PowerPoint." + CHR(13) +;
"Close down PowerPoint to see how VFPCOM handles it." + CHR(13) + ;
"VFP should give 'BeforeClose' MessageBox, " + CHR(13) + ;
"then PowerPoint should give its Save dialog." )
oVBPowerPoint.oPowerPoint.Activate() && Activate PowerPoint
DEFINE CLASS vfpPowerPoint AS custom
PROCEDURE PresentationClose(Pres)
* Cheap way to bring VFP to the front.
_SCREEN.AlwaysOnTop = .T.
_SCREEN.AlwaysOnTop = .F.
MessageBox("PresentationClose")
* Activate PowerPoint
oVBPowerPoint.oPowerPoint.Activate()
ENDPROC
ENDDEFINE
Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved