Querying & Reporting: Generate Reports with Word

The process is easier than you think

Organizations often create applications that serve both internal and external customers. For example, an organization might need to regularly generate customized Word documents for both users and customers. Fortunately, you can use COM objects with Microsoft Office applications. Office lets you drive most of its features from a programming language such as Visual Basic (VB). This compatibility lets you automate almost any task. In this article, I show you how to use VB and Word to create custom reports from a SQL Server database.

Getting Started

Suppose you need to create a custom Word document that contains a list of products that match a particular category. The document must also show the category name and the name of the person requesting the report. The completed document needs to resemble the one in Screen 1.

You can use VB and Word to create an application that lets users create this Products by Category report. Creating this application is easy. Word has several features that come in handy. First, you can create templates for commonly used documents. Screen 2 shows the template, ProductTemplate2.dot, that I developed for this article. To create a new template, create a document, modify it, then use File, Save As and select the document type Word template (*.dot) to save it. Also, you can use File, New and select the Template radio button. Make sure you save it in your document templates directory, which is C:Program Files Microsoft OfficeTemplates by default. You can change the Template directory by selecting Tools, Options, File Locations.

The template for this project contains three bookmarks that the application uses, as Screen 2 shows. Use these bookmarks to properly place the text in the document. To insert bookmarks into a document, place the cursor where you want to insert the bookmark. Then select the Bookmark command on the Insert menu. The bookmarks for this example are Customer, Category, and Products. The template also contains the formatting necessary for the final document, such as headers and custom styles.

Now, let's look at the VB application. Screen 3, page 58, shows the user interface. The user first selects a customer, then enters a filename for the document. Next, the user selects the product category to extract from the database, which prompts Word to create the document. The user can create multiple documents by changing the filename, then selecting a new category.

How the Application Works

The application declares several variables and constants, including: oWordApp as Word.Application, oCustomerDoc as Word.Document, mCustomer as String, mCategory as String, and Const wdLine-Break = 6.

The oWordApp and oCustomerDoc variables represent the Word objects you are using. The module-level mCustomer and mCategory variables store the customer and category name, respectively. Both variables place information into the Word document. The wdLineBreak constant inserts a line break into the Word document.

When the application starts, it triggers the Form_Load event, which Listing 1 shows, so that the user sees the form in Screen 3. The event code first uses the New keyword to create a reference to the Word Application object (oWordApp).

When the user selects a customer, the code calls the LoadCustomerList procedure to fill the cboCustomer combo box. This procedure calls the LoadCustomer function, which retrieves the customer from SQL Server's Northwind database. When the user selects a category, the code calls the LoadCategory-List procedure to fill the cboProductCategories list in a similar fashion.

When the user selects a category, the cboProductCategories _Click event also fires. This event creates the document. Listing 2 shows how this event works.

As you see in Listing 2, the event code uses the InStr function to find the left parenthesis first, then extracts the CategoryID from cboProductCategories drop-down box. If the InStr function finds the left parenthesis in the string, the IF statement executes and extracts the CategoryID. Next, the event code extracts the category name from the left portion of the text, and then the code executes the GetProductsByCategory function. This function extracts all the products that match sCategoryID from the database. If the GetProductsByCategory function returns any data, the next IF statement executes and calls the CreateDocument procedure to create the Word document.

The GetProductsByCategory function takes the CategoryID as a parameter and retrieves all the products that match. The function executes the data command, cmdProductByCategory, to perform the search:

envNorthwind.cmdProductByCategory CategoryID

This data command is based on the SQL statement:

SELECT ProductID, ProductName, UnitPrice, CategoryID FROM Products WHERE (CategoryID = ?) ORDER BY ProductName DESC

Notice how the data command sorts the recordset in descending (reverse) order. The Word code inserts the products into the Products by Category report in reverse order.

The GetProductsByCategory function's DO WHILE loop processes the products in the recordset. This loop places the ProductName, ProductID, and UnitPrice in the sProductList array. As Listing 3 shows, the loop inserts the Tab characters (chr(9)) into the string to align the fields with the tabs in the Word document. The ProductInfo style in the template has tab stops that align the elements of the string correctly under the appropriate headers.

Notice the use of the Format function in the DO WHILE loop. The Format function returns the UnitPrice as a nicely formatted custom currency field. When this text drops into Word, Word applies its formatting to correctly display the text. If you display the value from an sProductList() entry in the Immediate window in VB (without the special formatting), the value appears as:

Raclette Courdavault59  $55.00

The Form_Terminate event, shown in Listing 4, contains the code to destroy the objects you create in the application. This event also contains code to exit Word. If you do not exit Word in your application code, Word continues to execute after the application terminates. The Form_Terminate event executes when the form is terminated at the end of the application.

Coding the Word Document

The CreateDocument procedure in Listing 5 contains the code that drives Word. Because you did most of the work in the Word template, you don't have much to do in the application code to drive Word. At this point, your document template has become an object that works with your application. If you need to change the look of the document, you can change most of it in Word without changing the application code.

The IF statement at the beginning of the CreateDocument procedure ensures that the user has entered a valid filename. If the filename is blank, the ELSE clause executes and the subroutine exits without performing any action. After the user enters the filename, the application creates the document. The SET statement creates a document and adds it to the collection of Word documents by executing the Add method of the Documents collection:

SET oCustomerDoc = oWordApp.Documents.Add("ProductTemplate2.dot")

The procedure passes the name of the template (Product-Template2.dot) to the Add method. After the Add method executes, oCustomerDoc references the new document.

Next, the procedure saves the document by calling the SaveAs method:

oCustomerDoc.SaveAs "c:temp & txtFileName

In this application, the directory path is hard coded and the filename is pulled from the txtFileName textbox. If you have the Prompt for Document Properties check box selected on the Saved tab of the Tools, Options dialog box, this dialog box displays when the SaveAs method first saves the document. In this application, this feature does not present a problem because a user is directly executing the application. If you are using these features in a COM object, make sure this setting is not turned on. You can programmatically control this feature through the Save-PropertiesPrompt property (a member of the Word libraries Options class).

Next, the procedure adds the entries to the document. As callout A in Listing 5 shows, the first two entries are the customer's name and the selected category. The bookmark names (Customer and Category) must exist in the Word document or an error will occur. (Place error-handling routines in any procedures in your code that use COM objects. These error handlers must check for errors that are returned from the COM object.) Placing and finding elements in a document becomes easy. You simply reference the bookmark name as if it were a position on a form, and then work with it. I used the Text property of the Range object to change the text at the bookmark. I was interested only in placing one text string at a time, so using the Range object made sense.

The FOR loop inserts the product information from the array. The calling procedure passes this information into the CreateDocument procedure as the vCategory parameter. The first statement in the FOR loop inserts a line of text into the Word document, and the next line inserts a line break after the line of text.

Always load the array with the products in descending (reverse) order. You load products in reverse order because of the way Word inserts products into the document. If the products were in ascending order, you would need to process the array from back to front or put in more code to manipulate Word to continually find the end of the last line inserted. Loading the documents in descending order and then processing the array from front to back is much easier.

After the loop terminates, the procedure saves the document and moves the current position to the end of the range, in case the user terminates the application and leaves Word running. This safeguard makes it harder for users to accidentally destroy the text.

Further References

Keep Office's ease of use in mind when you are working on tough application development projects. For more information about automating Office tasks, look at the Help files for each Office product. If you did not install the Developer Help for a particular application, you can use Add/Remove Programs in Control Panel to add those files. More information is available in the Office 97 Visual Basic Programmer's Guide from Microsoft Press.