Sample: OFramer.exe Automates Excel and Word Inside a VB Form

ID: Q221195

The information in this article applies to:

SUMMARY

OFramer.exe (Office Framer) is a sample that demonstrates an alternative approach to automating Microsoft Excel and Microsoft Word from Visual Basic 6.0. Rather than use the OLE control on a form, Office Framer displays the actual automation server inside a frame control on the form. This is primarily accomplished by using the SetParent API function to change the parent of the automation server to the frame control.

This approach offers the following advantages:


MORE INFORMATION

The following file is available for download from the Microsoft Software Library:

OFramer.exe
Release Date: Mar-23-1999

For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:
Q119591 How to Obtain Microsoft Support Files from Online Services
The following files are provided with OFramer.exe. All files should be extracted to a folder on your machine. However, Book1.xls, Book2.xls, Doc1.doc and Doc2.doc are not required for Office Framer. These files are simply blank documents included to illustrate the File list box feature of Office Framer:

FileName Size
OfficeFramer.exe 52KB
OfficeFramer.vbp 1KB
Main.frm 29KB
Options.frm 3KB
Book1.xls 14KB
Book2.xls 14KB
Doc1.doc 19KB
Doc2.doc 19KB


The main form for Office Framer (Main.frm) is composed primarily of two frame controls. The frame fraExcel is used to house Excel. The frame fraWord is used to house Word. The two frames are positioned on top of each other. Only one frame is made visible at any given time, based on the user's option to display Excel or Word through an option group on the form. The form also contains a File list box control that lists the Excel and Word files on the system. When a user double-clicks a file in the list box, that file is opened and displayed in the appropriate frame through automation. When the user double-clicks another file from the list, the previous file is closed before the next file is opened. Other features of Office Framer demonstrate how to manage the startup, display, and termination of the automation servers.

Additional Features

Office Framer shows how you can check the status of Excel and Word before sending commands to them through automation. The custom function GetServerStatus returns a value that indicates if the server is started, busy, or ready to automate. If GetServerStatus returns "busy" then you can display a message to the user rather than attempt to carry out the automation task. This allows you to avoid run-time errors or confusing "server is busy" messages when your code attempts to automate a busy server. To see an example in Office Framer:
  1. Click the "Microsoft Excel" option on the form so that Excel is displayed.


  2. Then, click the File Open menu in Excel to display the File Open dialog box.


  3. With the Open dialog box still displayed, attempt to open a file by double-clicking a file in the File list box on the form. Note that you see a custom message that tells you the server is busy.


This approach can be very useful when automating an "interactive" automation server because the user could be manipulating the same instance you are trying to automate. Be sure to study the filList_DblClick() procedure to see an example of using GetServerStatus.

When the user resizes the form, the two frames resize as well. By sizing the form, the user can control the size of the Excel and Word frames.

The Options menu on the main form allows you to determine whether to view the Caption or StatusBar of the automation server. The advantage of not displaying the Caption and StatusBar is that you have a little more room to display the server in the frame. Also, hiding the caption prevents users from clicking on the caption to change the window state or window position of the automation server.

Instead of using the SetFocus method, Office Framer shows use of the SetForegroundWindow API to set focus to the automation server.

The automation servers are private to Office Framer. For example, if the user double-clicks an .XLS or .DOC file in Windows Explorer, a new instance of Excel or Word is launched. If the user clicks the Start button on the Taskbar to launch Excel or Word, a new instance is launched. When using automation, having a private instance prevents users from inadvertently starting and quitting the instance you are automating.

When the user quits Office Framer, the user is asked to save any unsaved files that are opened. See the QueryUnload event for details. If the user is asked to save a file and clicks Cancel, the shutdown is aborted and Office Framer stays running.

Office Framer also demonstrates how to manipulate CommandBar objects by disabling the File New menu item in both Excel and Word. This change is temporary, only while Office Framer is running. The purpose of disabling the File New menu item is to avoid a refresh problem that can occur when the user drags the File New dialog box of the automation server. When the dialog is moved, Office Framer could "disappear" from the desktop. By disabling the File New menu item you avoid this potential problem. This problem occurs on Windows 95 and 98 but not Windows NT. This problem can be resolved by installing the Microsoft Plus Pack for your version of Windows. This enables the "Show window contents while dragging" feature, which appears to solve this problem. If you wish to enable the File New menu item, comment or remove the CommandBar code in the ShowExcel, QuitExcel, ShowWord, and QuitWord procedures.

When you view Excel and Word through Office Framer, the window size and position of Excel and Word are changed so that they display properly in the frames. When you exit Office Framer, the original window sizes of Excel and Word are restored prior to ending Office Framer.

Limitations

There are some limitations in using this approach versus using the OLE control on a form.

If you are using a database with an OLE field that contains Excel or Word documents, you cannot "bind" to that field in the database as you can when using the OLE control on a form. In this case, the OLE control would be necessary to view the documents in the database. To use Office Framer, you would have to extract the files from the OLE field and write them to disk before viewing them.

Menu Merging does not occur as when in-place activating an object in the OLE control. If you have a menu on the Visual Basic form, it is completely separate from the Excel and Word menus. The Excel and Word menus appear only inside the frames.

The user has full access to the Excel and Word products on the form. If you need to prevent users from accessing certain menus and toolbars, you have to write code to remove or disable them. You can easily manipulate the CommandBars object to manipulate the menus or toolbars. See an example of disabling the File|New menu item in the ShowExcel and ShowWord procedures.

Additional query words: 


Keywords          : kbfile kbole kbsample kbAutomation kbContainer kbExcel kbInplaceAct kbVBp kbVBp600 kbWebBrowser kbWord kbGrpDSO kbOffice2000 
Version           : :; WINDOWS:6.0,97
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: July 14, 1999