Chapter 28

Integration with Multiple Office Applications

Eventually you will need to build an application that uses more than one Microsoft Office application. This project will probably involve a large programming team and will require weeks (or even months) to complete.

In this chapter, you learn how to do the following:

• Use a generic database class that simplifies how to use data access objects (DAO)
• Use a rectangle class that eliminates the need for three-dimensional (3-D) controls and improves performance
• Create an application-independent way of returning to your main form from another application
• Perform OLE Automation on an embedded chart so that it reflects the changes in a database
• Play .WAV files from a resource file
• Draw pictures from a resource by using PaintPicture
• Create your own 3-D buttons using graphics methods and a picture box
• Create shareable and reusable objects

Creating a Large-Scale Integrated Application

This chapter's example, UseAll, is a integrated application that encompasses the most common obstacles to developing large-scale programs. This relatively small application doesn't do anything particularly useful except demonstrate some powerful techniques. Also, by using application-independent objects, the application demonstrates the importance of reusability.

Before viewing this application's code, this section presents the UseAll application's user interface. Figures 28.1 through 28.8 display the elements of this application's user interface. The figure captions describe the purpose of each element. This interface is designed to help a program manager accomplish the following:

• Plan the amount of time and resources that it will take to complete the project
• Analyze bugs that have been reported on the company's products
• Provide a consistent method for entering these bug reports into a bug database
• Edit and view a PowerPoint presentation that communicates your project's progress to upper management

Fig. 28.1

The interface's main window provides access to all the application's features.

Fig. 28.2

The first page of the Bug Report Wizard contains contact information about the person who submitted the bug report.

Fig. 28.3

The second page of the Bug Report Wizard contains system information about the person who submitted the bug report.

Fig. 28.4

The final page of the Bug Report Wizard is the actual bug reporting form used by all the applications that this company distributes.

Fig. 28.5

UseAll sends a bug summary to Excel for further analysis.

Fig. 28.6

Instead of viewing code as text, UseAll formats the text in Word for easy viewing.

Fig. 28.7

When a user clicks the embedded chart, UseAll automatically updates the chart by using OLE Automation and in-place activation.

Fig. 28.8

The user can edit Project and PowerPoint objects by right-clicking on the object and then choosing a verb.

Putting It All Together

This small application encompasses the most common obstacles of such projects. Although it doesn't do anything very useful, the application demonstrates how you can accomplish the following integration tasks:

• Using OLE Automation with Word for Windows 6.0
• Using OLE Automation to analyze data from an Excel database
• Updating an embedded object with external data
• Using embedded PowerPoint and Project objects
• Creating a wizard for entering database data

The sample application in this chapter, UseAll, contains virtually no error handling, in the interest of keeping the example simple. However, you should never release an application without extensive error handling. Chapter 29, "Distributing Integrated Applications," demonstrates proper error-handling techniques.

Don't panic when you see code listing for this example. Although the listing is rather long, most of the code is relatively simple. Also, previous chapters have already discussed about 80 percent of the techniques that this example demonstrates. If you just take the application one file at a time, you shouldn't have any trouble understanding the material that the code presents.

SHARED.BAS�A Common Module

SHARED.BAS (listing 28.1) contains code that two or more files in this project use. The code provides a way for this project's files to set a window's AlwaysOnTop status and enables the application to show or hide a splash form.

The SetWindowPos API call used by the AlwaysOnTop routine in SHARED.BAS does not work while you are running this sample within the Visual Basic 4.0 IDE. However, when you compile your application and run it outside of the IDE, it will work as expected.

Listing 28.1 SHARED.BAS: Common Modules Are Useful for Application-Independence and Code Sharing

QUADRANT.BAS�Creating and Manipulating a Form's Region

QUADRANT.BAS manipulates one of four regions on a form. The module starts with GetQuadrants, which divides the form into four equal regions, starting from the upper-left corner and incrementing the regions from left to right and top to bottom. The drawing functions that follow simply enable you to perform drawing methods (some of which are predefined) on any of a form's four regions. Table 28.1 describes the remaining helper functions and their purpose. Listing 28.2 is the code for QUADRANT.BAS.

Table 28.1 QUADRANT.BAS Helper Functions

Listing 28.2 QUADRANT.BAS Is Helpful for the Cosmetics of USEALL.VBP

BASWAVE.BAS�Playing .WAV Files

BASWAVE.BAS (listing 28.3) plays sound files from a resource file. The magic of this module lies in the API call to sndPlaySound in the multimedia library (MMSYSTEM.DLL) that comes with Windows 3.1.

Listing 28.3 BASWAVE.BAS Plays Wave Files from a File or Resource

GENERIC.CLS�a Simple Database Class

GENERIC.CLS (listing 28.4) is a useful database class that you can use in any project. Its purpose is to simplify the data-access features of Visual Basic 4.0 by making them easier to use. The class begins by declaring some necessary variables and initialization and continues by performing some extensive data-access functions.

GENERIC.CLS requires your project to have a reference to the Microsoft DAO 2.5/3.0 Compatibility Object Library. If you attempt to run this example without this reference, you get an error.

Listing 28.4 GENERIC.CLS: A Reusable Generic Database Class

GENERIC.CLS provides six read-only properties�GetWorkspace, GetDatbase, Data, NewTable, NewField, and Filename�that enable calling functions to manipulate the database directly in ways that the class itself does not provide. Listing 28.5 shows the code for these properties.

Listing 28.5 Helpful Properties Provided by the GenericDB Class

The most common data-access method is OpenDatabase, which makes data access easier and more reliable. The method first ensures that the file exists; if it does, OpenDatabase tries to open the file. If the method cannot open the database, it prompts the user to repair the possibly corrupt database. Listing 28.6 shows the a custom OpenDatabase method called OpenDB.

Listing 28.6 The OpenDB Method

CreateRecordset (listing 28.7) creates a recordset for use with the methods in this class. If you don't specify a recordset type, the procedure assumes a dynaset.

Listing 28.7 The CreateRecordset Method

The methods in listing 28.8�Create, MakeTable, AddTable, MakeField, AddField, and MakeIndex�simplify the process of creating a database and appending fields and indexes to a database.

Listing 28.8 Database and Table Creation Methods

The next three methods�GetData, GetArrayData, and GetControlData�provide an easy way to return all the records in a field either as a string or in an array or control. The key to the success of these functions is to start at the first record in the table and iterate through to the last. Listing 28.9 shows these three methods.

Listing 28.9 Methods That Return a Field's Records

AddOrEditRecord adds a new record or edits an existing one. Its first argument determines whether to add or edit the data that the FieldPipeValue parameter array stores. The second parameter, FieldPipeValue, is a parameter array that contains values delimited by the pipe character (|), in the form Field Name|Value.

This function begins by ensuring that the parameter array isn't empty, and then sets the current recordset's .Add or .Edit property based on the action specified the AddRec parameter. Next, AddOrEditRecord iterates through the parameter array, determines the data type, and updates the recordset. Listing 28.10 shows the AddOrEditRecord method.

Listing 28.10 The AddOrEditRecord Method Manipulates Data at the Record Level

The next four methods�MFirst, MLast, MNext, and MPrev�navigate through the recordset and return the current record. The last two methods�FindRecord and GetRecord�provide methods for searching for a specific record and retrieving the current record. Listing 28.11 contains all six of these methods.

Listing 28.11 GENERIC.CLS Recordset Navigation Methods

Databases sometimes become damaged, so the Repair method (listing 28.12) provides a cleaner way to repair and compact a corrupted database.

Listing 28.12 The Repair Method

PRETTY.CLS�Creating Word-Formatted Copies

PRETTYPR.CLS (listing 28.13) is a class that shows off the power of OLE Automation with Microsoft Word, by performing several methods to create a formatted copy of a given Visual Basic code file. The class begins by declaring some useful constants and properties.

Listing 28.13 The PRETTYPR.CLS Class Formats VB Code in Word

The application calls the Initialize event (shown in listing 28.14) for this class when you first access any member or method in the clsPrettyPrint class. This event displays a splash screen while attempting to create a Word object. In addition, the procedure initializes class variables with default values. When the class is destroyed, the Terminate event sets the Word object equal to Nothing to free up the memory that the Word object is using. Listing 28.14 shows both of these events.

Listing 28.14 The Initialize and Terminate Events

The PrettyPrint (listing 28.15) is the key method in the clsPrettyPrint class. It provides an interface for the calling module to format the document and display the results. The method begins by displaying a splash screen while opening a new document based on the PRETTYPR.DOT template. PrettyPrint then prints a header and reads the code from the file. The method reads each line and inserts and formats it into Word. On reaching the end of the file, PrettyPrint displays the results for the user.

Listing 28.15 The PrettyPrint Method

The next two procedures are responsible for formatting the document. PrintHeader inserts a formatted header line at the top of the first page. The procedure prints this line to describe which file the document contains. ExamineLine determines how to format the line and sends the line to the appropriate procedure for processing. Listing 28.16 shows both of these procedures.

Listing 28.16 The PrintHeader and ExamineLine Procedures

The next four procedures�FormatProcedure, FormatDefault, FormatComment, and FormatDeclaration�actually format and insert the line into Word. Each of these functions looks for a specific keyword (such as Sub, Function, or neither) and formats the line appropriately. If any of the lines contain comment characters, the function handles them appropriately as well. Listing 28.17 shows the code for these four functions. This code isn't actually as complicated as it looks. The key is to take each line and formatting option separately.

Listing 28.17 PRETTYPR.CLS' Functions for Formatting Lines in Word through OLE Automation

The next three procedures�DisplayResults, Save, and PrintResults�display, save, or print the results of the formatting. The last procedure, ChangeFonts, is a helper procedure that the three previous procedures use to change the formatting of the current font in Word. Listing 28.18 shows all four of these functions.

Listing 28.18 Displaying, Printing, and Saving Formatting Results

The OLEConnect function establishes an OLE Automation connection with Word, whether it is running or not. You write this function to be generic so that you can easily insert the same code into your own project without making any modifications.

REPRT2XL.CLS�Creating Bug Reports from a Database

REPRT2XL.CLS (listing 28.19) is a class that shows off the power of OLE Automation and data access with Microsoft Excel by performing several methods to create a report from a database query. The class begins by declaring some variables and exposing a read-only pointer to the object that this class uses. When initialized, the class displays a splash screen while trying to create an Excel object. When destroyed, the class sets the Excel object equal to nothing to close Excel and free up the memory that the object is using.

Listing 28.19 REPRT2XL.CLS Sends Report Data to Excel

The ReportToExcel method (listing 28.20) retrieves data from BUGS.MDB and inserts it into USEALL.XLS. After inserting the data, the procedure formats it to make it easier to read and removes the splash screen.

Listing 28.20 ReportToExcel Demonstrates OLE Automation with Excel

The key to the ReportToExcel method is the LoadColumn procedure (listing 28.21). LoadColumn requires that the caller provide a table name, a field name, and the column in which to insert the data from the field. The procedure queries the database and prints the results in Excel.

Listing 28.21 The LoadColumn Procedure

FRMSPL.CLS�Displaying a Splash Screen

Any large project such as UseAll inevitably performs certain tasks that take several minutes to complete. Because long intervals with no apparent activity can scare users into prematurely rebooting their computer, a splash screen provides a reassuring visual effect. For this project, FRMSPL.FRM (listing 28.22) provides the code for presenting the splash screen.

Listing 28.22 FRMSPL.FRM Presents a Splash Form

FRMRET.FRM�Enabling the User to Return to UseAll

The clsPrettyPrint and clsReportToXL classes leave the user in different applications when they finish processing. FRMRET.FRM (listing 28.23) is a form that looks like a button in the screen's upper-right corner. The form provides the user an easy (and safe) way to return to your application. FRMRET.FRM also closes the current program (either Excel or Word) by setting its objects equal to nothing.

Listing 28.23 FRMRET.FRM Presents a Button That Enables Users to Return to Your Application

FRMBUGS.FRM�Writing Data to an Access Database

FRMBUGS.FRM (listing 28.24) is a bug-reporting wizard that you use to store information into an Access database. The form begins by declaring some form-level constants. Next, the SetupForm procedure initializes the form by positioning all the controls, loading the combo boxes, and playing an opening tune. Listing 28.24 also includes the LoadCombos procedure, which uses the GetControlData function from the GenericDB class to load all the combo boxes on the form.

Listing 28.24 FRMBUGS.FRM Shows How to Create a "Bugbase" Wizard

The NavigatePages procedure (listing 28.25) handles the navigation between different pages in the wizard by hiding and displaying the appropriate frames.

Listing 28.25 The NavigatePages Procedure

As UseAll changes each page, it calls UpdateData (listing 28.26) to write the data to the database. This function calls either the VerifyRequiredField function, to verify that required fields are filled, or the AddOrEditRecord function, to write data to the database.

Listing 28.26 The UpdateData Function

As the cboContact drop-down combo box loses its focus, the cboContact procedure (listing 28.27) checks whether the combo box is blank. If it is, the procedure displays a message and returns the focus to the combo box. If not, the user information (if any) that is already in the database fills the page.

Listing 28.27 The cboContact Procedure

The cmd_Click procedure handles click events from the command buttons on the wizard. The only control with any significant code is the Finish button. This button writes the bug report to the database and then asks the user if he or she wants to enter another bug. If not, the procedure dismisses the form. Otherwise, the procedure clears the page and returns the focus to the first drop-down combo box. The txtMultiBugs_Change event handles the enabled status of the Finish button to prevent users from finishing prematurely. Listing 28.28 shows these event-handling routines.

Listing 28.28 FRMBUGS Event-Handling Routines

The LoadPage procedure loads a page with data from the database. The next procedure, ClearAll, clears all the form's controls. Finally, the VerifyRequiredField procedure verifies that the field isn't null. If it is, the procedure displays an error message and adds a space to the field to prevent multiple errors. Listing 28.29 shows all three of these procedures.

Listing 28.29 The LoadPage, ClearAll, and VerifyRequiredField Procedures

FRMMAIN.FRM�Combining Modules to Create an Interface

FRMMAIN.FRM (listing 28.30) ties all the previous modules together in one central user interface. The beginning of this form contains some necessary declarations, followed by the form initialization tasks. The Form_Load procedure draws the 3-D effect of the form and its four graphical buttons. In addition, the procedure positions the controls on the form to fit each individual quadrant. These tasks can be rather time consuming, so FRMMAIN.FRM displays a splash screen during startup processing.

Listing 28.30 FRMMAIN.FRM Is USEALL.VBP's Central User Interface

The VerifyControlPositions routine (listing 28.31) verifies that the controls are placed properly. If the screen resolution is anything other than 640 by 480, the routine must resize the controls. Every time that you size an OLE container control, you must open the OLE server object and redraw the control's picture. Because this process is time consuming, the routine resizes the controls only if necessary.

Listing 28.31 The VerifyControlPositions Routine

When unloading the form, the Form_Unload procedure ensures that the application doesn't continue displaying the frmReturn form. The next procedure, Form_MouseMove, ensures that the lblStatus label is no longer visible. The lblStatus label displays when the user moves the mouse over the oleExcel control to display a status message. The message informs users that they update the control by clicking on it. Listing 28.32 shows both of these procedures.

Listing 28.32 The Form_Unload and MouseMove Procedures

All the following procedures in listing 28.33 paint the graphical buttons that appear on the form. The buttons are actually picture boxes with a 3-D border and a caption and graphic painted on them. The resource file stores the pictures, and PaintPicture paints them on the control.

Listing 28.33 The 3-D Graphical Buttons That Use Picture Boxes

Finally, the UpdateChart procedure (listing 28.34) gets bug data from the database and uses OLE Automation methods on the object inside the OLE container control to update the Excel chart.

Listing 28.34 The UpdateChart Procedure

To help you understand the UseAll application components better, the companion CD includes six small projects that use each major component separately. If you have difficulties understanding any of the information presented in this chapter, try opening one of these smaller projects. You might understand the information better if you focus on a smaller code sample.

From Here...

Although this chapter presents many solutions to integrated application problems, it omits thousands of other solutions. You might find the solutions to some of your integration problems in the following sources:

• Microsoft Windows SDK. All the documentation of the Software Development Kit (SDK) contains useful information for manipulating stubborn Windows applications. Although this documentation is sometimes difficult to understand, you might be surprised at the information that it provides.
• Visual Basic Programmer's Guide to the Windows API (Ziff-Davis, 1994), by Daniel Appleman. This book will be your bible if you decide to explore extensive API programming with Visual Basic. If you program with the API, make sure that you have this book handy.