Make Visual Basic Talk to Word

Send data to Word and print reports while tests keep running.

Dan DeStefano, Gnostech, Warminster, PA -- Test & Measurement World, 3/1/1997

Many programming languages and data-acquisition packages let you automate measurements, but they don't always let you produce custom reports. By using Microsoft Visual Basic (VB) 4.0 and Office 95 or Office 97, though, you can transfer data from a test and measurement application to a word processor or a spreadsheet and produce custom reports. Even better, the Office application can run in the background while an automated test is running, and it can quickly receive updated test results. All of this is possible because of Object Linking and Embedding (OLE)--a feature that lets you port data to Microsoft Office applications.

With a few lines of code, you can insert data into tables or into decimal-aligned columns. And you can control fonts, add a logo, or place a graphic image in your report. These simple touches go a long way toward improving how your customers (and your boss) view your work. And, you can archive your data (a must for ISO 9000), password protect your files, and retrieve test data without having to run the test program.

To illustrate how you can use VB and Office to generate reports, I'll explain how to create a report in Microsoft Word. But this isn't a primer about VB programming; I'm assuming that you already know how to program in VB and that you've had some experience using Word. (For more information, the April 1997 issue of Test & Measurement World contained an article in which Dan DeStefano described the process for transferring data into Microsoft's Excel spreadsheet.)

Make a Template
The first step in linking a Word document to Visual Basic code involves setting up a report template in Word. The template lets you see how your final report will look. When creating a template, you must decide what information belongs in the report and decide how to present that information. Figure 1 shows a typical test stand report. The top of the report includes the company name, the report header, and the UUT information. The bottom three sections show test parameters and calculated data in table formats. The table cells in the Word template present data as text.

Before you write any VB code, you must prepare the template to communicate with VB. Word's bookmarks act as named place holders in your template so you can add information later. Bookmarks are usually invisible, but you'll want to make them visible while developing your template. To do this, go to Word's Tools menu and select Options. The View tab will show several check boxes. Click on Bookmarks.

Back in your template, you'll add a bookmark that will insert the test name in your report. First decide where you want the test name to go, and then place the cursor there. Select the Edit menu and click on Bookmark. A dialog box will prompt you for a bookmark name. For this example, use bkTestName, and click the Add button. (I use the bk... nomenclature to easily refer to Word bookmarks in my VB code.) Word will place an "I-beam" marker where you left the cursor (Fig. 2). Your I-beam holds the place for the test name.

Place additional unique bookmarks in the report template where you want specific data to go. Be sure to note the bookmark names you've chosen for specific locations. Finally, save the document as SimpleReport with Bookmarks.doc (a Windows 95 long file name) and exit Word.

After creating the template, you're ready to write VB code that will send data from your VB data-acquisition or test program to the document. Use descriptive names such as TestName$ and UUTSerial$ for your string variables. Store set point data, analog input data, and calculation results in arrays of double-precision numbers using names such as SetpointValue#(i%), AnalogValue#(i%), and Calculation#(i%). Descriptive names make the code easier to read and maintain. For simplicity, assume that all the test information resides in global variables.

Once you've written a VB program to acquire the data and perform the calculations, you can write code that transfers data to your report. Listing 1 shows the first steps. The first statement declares MyReport as an object variable, so MyReport becomes the "handle" to all the OLE Word functions and methods. Next, the CreateObject command invisibly opens Word and exposes Word's macro command language, Word Basic, to VB. Finally, the code uses Word's .FileOpen function to open your SimpleReport with Bookmarks.doc template.

Listing 1.
Dim MyReport as Object
Set MyReport = CreateObject("Word.Basic")
MyReport.FileOpen ("c: SimpleReport with Bookmarks.doc")

Move the Data
The next step is to write code that will transfer the test information from VB to Word. Here's where the bookmarks come in handy. The VB code in Listing 2 will go to the two bookmarks and insert text strings at those locations. The text strings are examples of variables you can create in your VB test program.

Listing 2.
With MyReport
.EditGoto "bkTestName"
.Insert TestName$
.EditGoto "bkUUTSerial"
.Insert UUTSerial$
End With

You can repeat the .EditGoto and .Insert pattern within the With...End With structure to insert other text data into the report. Many of the commands emulate Word's menu commands. For example, .FileOpen and .EditGoto represent clicking on the File and Open menus and the Edit and Go To menus that you see in Word. You can follow a similar pattern to put the test results and calculations into the report, but first you must convert the data from numbers into text strings that represent the numbers.

You could use a separate .EditGoto command for each bookmark, but that would bloat the code and become tedious for large reports. Fortunately, you have an alternative. You can store the set points and their numeric data in arrays and insert the values into a series of consecutively numbered bookmarks.

Assume there are four set points in this test. You can label the bookmarks on the report that correspond to the set points as bkSetValue1 and bkSetValue2. The code in Listing 3 uses a For...Next loop to cycle through four consecutive bookmarks and insert a corresponding array value, SetpointValue# (i%). This loop and array structure minimizes the amount of code.

Listing 3.
With MyReport
For i% = 1 To 4
.EditGoto "bkSetValue" & Trim$(i%)
.Insert Format(Str(SetpointValue#(i%)), "0.00")
Next i%
End With

The code in Listing 3 is nearly identical with the code in Listing 2, but it has some added formatting. The Trim$ command will take the bkSetValue string and concatenate the appropriate number based on the loop index. The Str function returns a string representation of a number--in this case, the indexed value in the SetpointValue# array. The Format command formats the string according to the described pattern--all the set point values have two decimal places so the numbers will align in columns.

You can follow the same pattern for the analog input values and calculation values. As long as you label the bookmarks consecutively, you can use a For...Next loop structure to place data into your Word document.

Once you've made the basic report, you need only add code that prints it, saves it, and closes Word. Closing Word frees memory resources. The code in Listing 4 can perform these tasks.

Listing 4.
DocName$ = "c:" & TestName$ & " " & Format(Date, "mmm dd yyyy")
& " - " & Format(Time, "hh mm AMPM")
With MyReport
.FileSaveAs (DocName$)
.FilePrint
.FileClose 2
End With
Set MyReport = Nothing

DocName$ contains some formatting commands that add the time and date to the report's file name. The example in Listing 4 will produce a string looking like c:Sample Test Nov 20 1996 - 1 27 PM. You must format the file name to remove any slashes and colons, which are unacceptable in Windows 95 file names.

The .FileSaveAs and .FilePrint commands are self-explanatory, and .FileClose 2 closes Word. Adding the "2'' causes Word to bypass prompting the user to save the file. The final line of code frees the memory associated with having Word open.

All the Word operations run invisibly to the end user. If you want to see what's happening, you can open Word before running the code, then you'll see it step through the operations. You can easily bundle the code in listings 1-4 into a single VB function call that automatically generates reports for each test.

Make Reports Look Good
Graphics add a nice touch to any report, and adding graphics to a document is easy. Get an image file of your company logo, go to the Insert menu, and click on Picture. You can then add the graphic image to your Word document.

Here's another way you can enhance your reports. Suppose you want to be able to see--at a glance--whether any measurements were out of tolerance during a test. By designing your report to use a different font or text color for out-of-tolerance measurements, you'll be able to spot those measurements immediately.

The code shown in Listing 5 calls InTolerance, a function you can create that checks if a measured value, AnalogValue# (i%), is within tolerance. If the measurement is within tolerance, Word displays it in a bold font. Otherwise, Word displays the value in italics. You could also change the color so out-of-tolerance measurements appear in red while good measurements appear in black (Fig. 3).

Listing 5.
With MyReport
For i% = 1 To 20
.EditGoto "bkAnalogInput" & Trim$(i%)
If InTolerance (AnalogValue#(i%)) Then
.Bold
Else
.Italic
End If
.Insert Format(Str(AnalogValue#(i%)), "0.00")
Next i%
End With

So far, I've explained how to send data to preformatted, fixed-length tables. But what about tables whose length may vary? Assume that you've generated calculation data for four calculations and stored the results in a two-dimensional array called CalcSummaries#(i%, j%) where i% represents the test run number and j% represents the calculation number. You can best show this information in a table. Assume that you've stored the number of test runs in a sequence (20) and the number of calculations (4) in variables named NumOfTests# and NumOfCalcs#, respectively. The code in Listing 6 will generate 20 sets of test results, each with four calculations per row.

Listing 6.
With MyReport
.EditGoto "bkSummary"
For i% = 0 to NumOfTests# - 1
If i% <> 0 Then .NextCell
.Insert "Run #" & Str(i% - 1)
For j% = 0 to NumOfCalcs# -1
.NextCell
.Insert Str(CalcSummaries#(i%, j%))
Next j%
Next I%
End With

You must set up your Word table with two rows. The top row will contain headers, and the second row will hold the calculations from the first test. Set up the rows with five columns (1 plus the number of calculations to display). The leftmost column (column 1) will display the test run number, and the remaining columns will display the four calculations in that run.

Enter your calculation headers in the top row, leaving the cell in column 1 blank. Place a bookmark in the leftmost cell of the second row, and call it bkSummary. Save the document.

The .EditGoto line in Listing 6 places the cursor at the start of the table's second row. The two For...Next loops will then loop four times and enter all the calculation values into the row. After the second row is filled, i% is no longer 0, and the .NextCell function adds another row to the table. That process continues until the loop enters data from all of the test runs into the table.

Protect the Data
Having completed your test and generated reports, you want to make sure that others can read the file but can't tamper with the data. Word's built-in protection features can require a password to edit a document, and you can send a password to Word from VB.

When you protect the Word file with a password, other people can read and print the test report without corrupting the data. When VB creates the data file, it sends Word a password that lets a user edit the file. Without the password, anyone who opens the file in Word won't be able to edit the file. Only when your VB program opens Word will Word receive the correct password. And, because the test operators don't have the VB source code (they have an executable file), they can't get the password.

Assume that you've set up Word for password protection and VB has prompted the user for a password. Also assume that the password is stored in the variable Password$. By changing one line of code, you can prevent anyone from mistakenly corrupting your data. Just change Listing 4 from .FileSaveAs (DocName$) to .FileSaveAs Name := DocName$, WritePassword := Password$.

Now that you've seen how to move data into Word, print reports, and protect data, you may want to learn how to further control Word from VB. A good source of information is Word's help file, which you'll find under Word Basic Reference. T&MW

Dan DeStefano is a systems engineer at Gnostech, Warminster, PA, where he develops software for data-acquisition and control systems. He has a B.A. in chemistry from LaSalle University. His e-mail address is dand@gnostech.com.

Get Visual Basic Source Code for Free
The Visual Basic source code used in this article is available from T&MW by clicking here. Or, mail a blank, formatted PC-compatible 3.5-in. disk and your mailing address to Test & Measurement World, 275 Washington St., Newton, MA 02158, and we'll put the code on the disk for you.--Eds.