Using Word under dBASE
by Gary White, dBVIPS

| WordBasic vs VisualBasic OleAutoclient() Word Versions Inspecting the Word Object Getting Started Creating a New Document Adding Some Text to a Document Opening a Password-Protected Document Navigating in a Word Document Formatting Word Text Replacing Bookmarked Text Seeking and Replacing Text Formating Bookmarked Text Indenting Paragraphs Avoiding Paragraph Breaks Inserting a Page Break Performing Loop Operations Based on Line Number Setting Margins Word Document Table Creating a Table in a Word Document Merging cells Creating a Form in Word Naming or Renaming a Document Copying a Word Document into a Memo Field Performing a Mail-Merge Conclusion

WordBasic vs Visual Basic

Word 7.0 (Word95) used the WordBasic object. This was an entirely different object model. WordBasic language consists of a flat list of approximately 900 commands. Later versions of Word use Visual Basic for Applications. Visual Basic consists of a hierarchy of objects, each of which exposes a specific set of methods and properties (similar to statements and functions in WordBasic). While most WordBasic commands can be run at any time, Visual Basic only exposes the methods and properties of the available objects at a given time. None of the code using the `word.application` will work properly in the older version.

OleAutoclient()

The OleAutoclient class creates an OLE2 controller that attaches to an OLE2 server. This simply means that it starts an instance of the appropriate application that acts as an OLE server and sets up the lines of communication. The OLE2 server is the host application, in this case Microsoft Word. The OLE2 controller is the object reference you create in your dBASE application. For example, the following statement creates an instance of Microsoft Word:

| `oWord = new oleAutoclient("word.application")`
| ``

Word Versions

In some instances, the installation for MS Word does not create a “version independent” registry key. In those cases, you will need to specify the version of Word as `word.application.8` for Word97, as `word.application.9` for Word2000, or whatever version of Word you are using. You can find the correct terminology by starting RegEdit and searching for “word.application” and seeing the way it is listed in the registry.

The following code is a slightly modified version of a method I use. It first attempts to get the correct version from:

| `HKEY_CLASSES_ROOT\Word.Application\CurVer`
| ``

If you want your code to work on computers that might have different versions of Word installed, you could use your code inside a `try/catch`:

| `local oWord, bSuccess, n, c` `try` `oWord = new oleautoclient('Word.application')` `bSuccess = true` `catch(exception e)` `for n = 9 to 7 step - 1` `bSuccess = false` `c = 'word.application.' + n` `try` `oWord = new oleautoclient(c)` `bSuccess := true` `catch(exception e)` `endtry` `if bSuccess` `exit` `endif` `next` `endtry` `if bSuccess` `// go ahead with your code` `else` `msgbox('Failed to start Word')` `endif`
| ``

Inspecting the Word Object

Once created thusly, you can type `inspect(oWord)` in the Command Window and, after a rather lengthy wait (there are a lot of things to load), view its properties, events and methods. With a lot of trial and error, you could probably use just that information to use the created object. You could see the properties, some of which may even have a drop-down list of choices. You can inspect other objects to which this one was related.

However, a better approach is to use the documentation supplied with the application with which you want to connect. In the case of MS Word, the Word Visual Basic help file should get you started. Note that the default installation of Word does not include this file. You need to select Custom Installation during the Word installation and specifically select the VB help files for those applications you want.

Another invaluable source of information for MS Office applications is the Visual Basic Object Browser. In this example, if you start MS Word, then click on the Tools menu, then click Macro, and then Visual Basic Editor, you will see the VB editor. Once there, click View and then Object Browser. The Object Browser will show you all the defined constants, the properties, the events and the methods along with appropriate parameters.

With the aid of the Inspector in dBASE, the Word Visual Basic documentation, and the Object Browser, you should be able to get a pretty good idea of what you can and cannot do with MS Word. The below diagram, from the Word Visual Basic help file, depicts the Word97 object hierarchy.

About now, you may be saying how you don’t know Visual Basic and you aren’t really interested in learning it. Well, fear not. You’ll not need to know much. You can often let MS Word do much of the work for you. And just how do you perform this magic? By simply recording a macro in Word while you do what you want done. Then you just look at the generated source code for the macro and translate that into something dBASE can pass to Word.

Getting Started

Let’s begin with something very simple. Let’s assume you want to print a Word document. Begin by starting MS Word. Click Tools | Macro | Record New Macro. Give the macro a name, or accept the default (`Macro1`) and click OK. Next, open the document you want to print, and then click the Print button on the Word toolbar. Finish by clicking Tools | Macro | Stop Recording. Now, let’s examine the generated code. Click Tools | Macro | Macros. Select the macro you just recorded and click the Edit button. This will open the Visual Basic Editor with the generated code. It will look something like this:

| `Sub Macro1()` `'` `' Macro1 Macro` `' Macro recorded 04/26/99 by Gary White` `'` `Documents.Open FileName:="Doc1.doc", ConfirmConversions:=False, ReadOnly:= _` `False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:= _` `"", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _` `Format:=wdOpenFormatAuto` `ActiveDocument.PrintOut` `End Sub`
| ``

As you can well imagine, dBASE would choke if it tried to execute that code. So how do you get this to work in dBASE? A couple of general guidelines:

1. You cannot use named parameters from within dBASE. This means that you must supply all parameters up to and including the last one you want to send.
2. Any Word method, when called from dBASE, must include parenthesis even if there are no parameters.

So let’s see what this looks like in dBASE:

| dBL Code | Comments
| oWord = new ; oleAutoclient("word.application") | First, create the instance of Word
| oWord.documents.open( ; "C:\My Documents\Doc1.doc", ; false, true ) | Next, open the document. While the recorded macro includes a whole bunch of parameters, if the document is a Word document and it is not password protected, you can safely ignore everything but the file name. The file name you use should include the full path to the document. Since the file name is the first param, that's all you really need. However, the possibility exists that another user could have this document open, in which case, a nasty dialog box would be displayed asking what to do next. In our case, all we want to do is print the document, so we can safely open the document as "Read-Only" to avoid that. Neither do we want to worry about dialogs asking about converting a document that may be from a prior version of Word. So, in this case, we'll supply a couple more params. The first one, following the file name is a `false` to indicate no prompt for conversion and the second will be a `true` to indicate opening "Read-Only". Note that you MUST use parenthesis to enclose the param.
| oWord.activeDocument.printOut() | Next, print the document. Once again, note the parenthesis, even though there are no parameters passed
| oWord.quit( 0 ) | Now, we close Word. It may take Word two or three seconds to shut down. If you're doing this in a loop, or other fast acting code, it may not release the file quick enough before the next attempt. (you can find information about the quit method in the Word VB help file)
| release object oWord | Dispose of our oleAutoclient object
| oWord = null | Finally, stub out the reference to make sure it's released

With just 5 lines of code, you have created a Word instance, loaded and printed a document, closed Word and cleaned up after yourself! In fairness, a real application should include some minimal error checking to allow it to fail gracefully and abort if necessary. Included in error trapping should be cases where the Word application info is not found in the registry, the file is not found, or the printer is not connected. A series of nested `try/catch` structures should handle that with a minimum of effort.

The exciting part is that you’ve not even scratched the surface of the potential uses for this technology. If you’ve tried the above code, you will have noticed that you never saw an instance of Word. It was all handled transparently. If you wanted to open a document and allow the user to edit it, all you need to do is set your reference to the OleAutoclient’s `visible` property to `true` (and, of course, don’t open it read-only):

| `oWord.visible = true`
| ``

Note that, by default, the macro you recorded will be saved in your “Normal” Word template. It would be a good idea to delete any macro you do not want permanently available in all your Word documents. You do this in Word by clicking the Tools menu, then Macro, then Macros. Highlight the name of the macro you want to delete and click the Delete button.

Creating a New Document

The `add()` method of the `documents` object is used to create a new Word document. It can accept up to two parameters. The first is the name of the template to be used for the new document. If this argument is omitted, the Normal template is used. The second is a boolean which, if `true`, specifies that the new document is a template. In the following example, we will create two new documents at the same time:

| `local oWord, oDocument1, oDocument2` `oWord = new oleAutoclient('Word.application')` `oDocument1 = oWord.documents.add()` `oDocument2 = oWord.documents.add()` `oDocument1.content.text := 'This is the text of the first letter.'` `oDocument2.content.text := 'This second letter is very short.'` `oWord.visible := true`

Adding Some Text to a Document

Some text (or the content of a field or of a variable) can be added to a Word document:

| `#define wdGoToLine 3` `#define wdGoToAbsolute 1` `local oWord` `oWord = new oleautoclient("Word.application")` `oWord.visible = true` `oWord.documents.open("docname.doc", true, false, true)` `// go at the beginning of line 7` `oWord.selection.goto(wdGoToLine, wdGoToAbsolute, 7)` `// inset a tabulation and some text` `oWord.selection.typetext( chr(9) + "Text to add" )` `// go at the beginning of line 10` `oWord.selection.goto(wdGoToLine, wdGoToAbsolute, 10)` `oWord.selection.typetext( form.rowset.fields["Memo_Field"].value ;` `+ variable_1 + form.editor1.value)`

Opening a Password-Protected Document

To supply the password and open the document from within dBASE, all you need to do is:

| `local oDocument, cPassword, ConfirmConversions, ReadOnly, AddToRecentFiles, oWord` `oDocument = <path\name of file>` `cPassword = "My_Password"` `ConfirmConversions = false` `ReadOnly = false` `AddToRecentFiles = false` `oWord = new oleautoclient("Word.application")` `oWord.visible = true` `// if Word is not maximized by default` `oWord.windowstate := 0` `oWord.documents.open(oDocument, ConfirmConversions, ReadOnly, AddToRecentFiles, cPassword)`

Navigating in a Word Document

In the following code, line 10 places the cursor at the left of the first character on page `pn`. At line 12, the cursor jumps to the left of the first character on line `ln`. That line number is not calculated from page `pn`, but rather from the start of the document. Finally, lines 14 and 16 will place the cursor respectively before the first character of the document and after the end of that document.

If you take out all the numbers at the left of the following code, put everything in a program and run it, the code will be executed so fast that nothing between lines 9 and 15 will seem to have been executed: unless your document is very big or your computer very slow, the first thing you’ll know is that the cursor is at the end of the document.

| `1 #define oUnit 6` `2 #define wdGoToLine 3` `3 #define wdGoToPage 1` `4 #define wdGoToAbsolute 1` `5 local oWord` `6 oWord = new oleautoclient("Word.application")` `7 oWord.visible = true` `8 oWord.documents.open("docname.doc", true, false, true)` `9 // replace 'pn' with the page number` `10 oWord.selection.goto(wdGoToPage, wdGoToAbsolute, pn)` `11 // replace 'ln' with the line number` `12 oWord.selection.goto(wdGoToLine, wdGoToAbsolute, ln)` `13 // go to beginning` `14 oWord.selection.homekey(oUnit)` `15 // go to end` `16 oWord.selection.endkey(oUnit)`
| ``

Formatting the Word Text
Replacing Bookmarked Text

Bookmarks can be displayed in a Word document: select the menu item Tools | Options… and under the View tab, check the Bookmarks checkbox. If you assigned a bookmark to a location, the bookmark appears as an I-beam. If you assigned a bookmark to some text, that text appears in brackets ([…]) on the screen. The following code will insert the new text at the right of the I-beam when the bookmark was assigned to a location. However, that code will replace the bookmarked text and delete the bookmark itself.

| `local oWord, r` `oWord = new oleautoclient("Word.application")` `oWord.visible = true` `oWord.documents.open("docname.doc", true, false, true)` `if oWord.activedocument.bookmarks.exists("bookmark1") == true` `r = oWord.activedocument.bookmarks("bookmark1").range` `r.text = "new text is here"` `endif`
| ``

Seeking and Replacing Text

Similarly, you might have to seek and replace all occurances of some text before performing mail-merge. That’s easy enough with Word:

| `#define oUnit 6` `local oWord, oFind` `oWord = new oleautoclient('Word.application')` `oWord.visible := true` `oWord.documents.open(<full path and file name of the file to open>)` `oFind = oWord.selection.find` `// To ensure that unwanted formats aren't included as criteria in the` `// find or replace operation, the following method is used before` `// carrying out the operation.` `oFind.clearFormatting()` `with(oFind)` `Text := 'Text to be replaced'` `Forward := true` `MatchCase := false` `MatchWholeWord := false` `MatchWildcards := false` `MatchSoundsLike := false` `MatchAllWOrdForms := false` `endwith` `do while oFind.execute() // do while text found` `oWord.selection.text = "Replacement Text"` `oWord.selection.homekey(oUnit)` `enddo`
| ``

Note that, other than the `Text` property, the rest of the `oFind` object properties shown above are the default values and need not be specifically set unless you want them different than what is shown.

Formatting Bookmarked Text

Assume you’ve created a bookmark named `BoldRedSpot` where you want the bolding to take place and the text has to be in red:

| `#define wdRed 6` `local oWord` `oWord = new oleautoclient("Word.application")` `oWord.visible = true` `oWord.documents.open("docname.doc", true, false, true)` `oWord.selection.goto(-1,null,null,'BoldRedSpot')` `oWord.selection.Font.bold := true` `oWord.selection.Font.ColorIndex = wdRed`

Indenting Paragraphs

Besides bookmarked text, formatting Word documents can be done programmatically. The following example sets a first-line indent of 0.2 inches for each paragraph and changes the whole document into a double-spaced document:

| `#define oUnit 6` `local oWord, n` `oWord = new oleautoclient("Word.application")` `oWord.visible = true` `oWord.documents.open("docname.doc", true, false, true)` `for n = 1 to oWord.activeDocument.paragraphs.count` `oWord.activeDocument.paragraphs(n).range` `oWord.activeDocument.paragraphs(n).range.select()` `oWord.selection.paragraphFormat.firstlineindent := ;` `oWord.inchestopoints(0.2)` `oWord.selection.paragraphFormat.space2() // space15() for 1.5-line spacing` `next` `oWord.selection.homekey(oUnit)`

Avoiding Paragraph Breaks

Typographers call “widows” a single short line at the top of the page or column which is the end of a sentence or a paragraph. “Orphans” are sometimes erroneously called widows: they are the lines which lead into a larger block of type, but which have been left by themselves at the end of a page or column (in other words, an orphan is the first line of a paragraph, left alone at the end of a page). Widows and orphans are considered undesirable. To control both, the `WidowControl` property has to be set to `true`.

| `local oWord` `oWord = new oleautoclient("Word.application")` `oWord.visible = true` `oWord.documents.open("docname.doc", true, false, true)` `oword.activeDocument.paragraphs.widowControl := true`

Inserting a Page Break

To insert a page break after the 46th line of the document:

| `#define wdGoToAbsolute 1` `#define wdGoToLine 3` `#define wdPageBreak 7` `local oWord` `oWord = new oleautoclient("Word.application")` `oWord.visible = true` `oWord.documents.open("docname.doc", true, false, true)` `// go to line number 47` `oword.selection.goto(wdGoToLine, wdGoToAbsolute, 47)` `// inseting the page break` `oword.selection.InsertBreak(wdPageBreak)`

Performing Loop Operations Based on Line Number

To insert a page break after each block of 40 lines of text, we could use a loop based on the line number:

| `#define wdGoToAbsolute 1` `#define wdGoToLine 3` `#define wdPageBreak 7` `#define wdStatisticLines 1` `local oWord, nTotalLines, nLineNo` `oWord = new oleautoclient('word.application')` `oWord.visible = true` `oWord.documents.open("docname.doc",true,false,true)` `nTotalLines = oWord.ActiveDocument.ComputeStatistics(wdStatisticLines)` `nLineNo = 41` `if nLineNo > nTotalLines` `msgBox("No page break were inserted because the document " + chr(13) + chr(10) + ;` `"is less than one page long", " Error...", 48)` `else` `do` `oword.selection.goto(wdGoToLine, wdGoToAbsolute, nLineNo)` `oword.selection.InsertBreak(wdPageBreak)` `nLineNo += 41` `until nLineNo > nTotalLines` `endif`

Setting Margins

The following will set the top margins to 1.5 inches and the bottom margins to 2 inches. It will not add to the actual margins but rather change them to the new settings:

| `#define InchToPoints 72` `local oWord` `oWord = new oleautoclient("Word.application")` `oWord.visible = true` `oWord.documents.open("docname.doc", true, false, true)` `oword.activedocument.pagesetup.TopMargin := InchToPoint * 1.5` `oword.activedocument.pagesetup.BottomMargin := InchToPoint * 2`

Word Document Table

Creating a Table in a Word Document

To fill a table in a document, the `add()` method of the `tables` object can be used, as in the following example. Here, after line 19 (the old line 20 will be pushed down), a new table will be added:

| `#define wdGoToLine 3` `#define wdGoToAbsolute 1` `local q, oWord, oTable, n, oCells` `set database to` `if not file('tbldemo.dbf')` `create table tbldemo(;` `field1 char(15),;` `field2 char(15),;` `field3 char(15))` `use tbldemo` `generate 10` `use` `endif` `q = new query('select * from tbldemo')` `if q.rowset.first()` `try` `oWord = new oleautoclient('word.application')` `oWord.visible := true` `oWord.documents.open("docname.doc",true,false,true)` `oWord.selection.goto(wdGoToLine,wdGoToAbsolute,20)` `oWord.activeDocument.tables.add(oWord.selection.range, 1, 3)` `// if there is already another table in the document before` `// line 20, in the following line replace 1 with 2` `oTable = oWord.activeDocument.tables(1)` `for n = 1 to q.rowset.fields.size` `oCells = oTable.rows(1).cells(n)` `oCells.range.bold := true` `oCells.range.text := q.rowset.fields[n].fieldName` `next` `do` `oTable.rows.add()` `for n = 1 to q.rowset.fields.size` `oCells = oTable.rows(oTable.rows.count()).cells(n)` `oCells.range.bold := false` `oCells.range.text := q.rowset.fields[n].value` `next` `until not q.rowset.next()` `catch(exception e)` `msgbox( e.message + ' / Line ' + e.lineNo, 'Error', 16)` `finally` `try` `release object oCells` `catch(exception e)` `endtry` `try` `release object oTable` `catch(exception e)` `endtry` `try` `release object oWord` `catch(exception e)` `endtry` `endtry` `endif`

Merging cells

To merge cells in a Word table, the `merge()` method of the `cells` object will be used. Here we will merge all cells between the one at the first column of the 5th row, to the one in the third column of the 8th row:

| `local oWord, oTable, oCells` `oWord = new oleautoclient('word.application')` `oWord.visible := true` `oWord.documents.open("docname.doc",true,false,true)` `oWord.activeDocument.tables.add(oWord.selection.range, 1, 3)` `// if we want to merge cells in the 1st table in the document` `oTable = oWord.activeDocument.tables(1)` `// from:` `oCells = oTable.rows(5).cells(1)` `// to:` `oCells.Merge(oTable.rows(8).cells(3))`
| ``

Creating a Form in Word

Word can be used to create forms or surveys. Creating the form can be done programmatically:

| `local oWord, oDocument, oTable, n, oBorders, oCells` `oWord = new oleautoclient('Word.application')` `// create a new document` `oDocument = oWord.documents.add()` `// place a heading at the top in 18 pt bold type` `with(oWord.selection)` `paragraphformat.alignment := 1` `font.bold := true` `font.size := 18` `text := 'Questionnaire' + chr(13) + chr(13)` `endwith` `// add a table` `r = oDocument.paragraphs(3).range` `oTable = oDocument.tables.add(r, 3, 2)` `// format the table to 10 point plain text` `oTable.select()` `with(oWord.selection)` `paragraphformat.alignment := 0` `font.bold := false` `font.size := 10` `endwith` `// hide the table borders` `for n = 1 to 6` `oBorders = oTable.borders(n)` `oBorders.visible := false` `next` `// insert questions and form fields for answers` `for n = 1 to 3` `oCells = oTable.cell(n,1)` `oCells.range.text := n + '. Question number ' + n + '?'` `oCells = oTable.cell(n,2)` `f = oCells.range.formfields.add(oCells.range, 70)` `next` `// protect the form so all the user can` `// type in answers to the questions` `oDocument.protect(2)` `// show Word` `oWord.visible := true` `oDocument.saveas(set('directory') + '\demo.doc')`

Reading the answers from a Word survey can also be done programmatically. If you’ve created the questionnaire with the code above, insert your answers, save the document, then try the following code which will list your answers in the Command Window:

| `local oWord, oDocument, n` `oWord = new oleautoclient('word.application')` `// open the document document` `oDocument = oWord.documents.open(set('directory') + '\demo.doc')` `// get the results from the form fields` `for n = 1 to oDocument.formfields.count` `// insert your code. For example:` `? oDocument.formfields(n).result` `next` `oWord.quit()`

Naming or Renaming a Document

The name property of a Word document object is read-only. The name is not created until the document is saved. To name a document, your only option is to save the document immediately after `add()`ing it:

| `local cOldName, cNewName, oWord` `cOldName = 'c:\eval.doc'` `cNewName = 'c:\eval_new.doc'` `oWord = new oleautoclient('Word.application')` `oWord.documents.open(cOldName)` `// make some changes if needed` `oWord.activeDocument.SaveAs(cNewName)` `oWord.activeDocument.close()` `delete file (cOldName)`

Copying a Word Document into a Memo Field

Assuming you have the appropriate record displayed in a form and the memo field is datalinked to `Form.editor1`:

| `local oWord` `oWord = new oleAutoclient('Word.application')` `oWord.documents.open('My_document.doc')` `oWord.selection.wholestory()` `oWord.selection.copy()` `Form.editor1.value = ''` `Form.editor1.paste()` `oWord.quit(false)`

Performing a Mail-Merge

Performing a mail-merge can be a very beneficial part of an application. We’ve all seen those organizations that store massive amounts of data. They generate blizzards of paper printouts of every conceivable piece of information. They, in fact, generate so much paper that it becomes useless. They bury themselves in data to the point that it becomes meaningless and can be put to no good use. A mail-merge is one way of using this stored data that will provide a tangible, beneficial result. We’ll use the dBASE OleAutoclient class to accomplish this task.

We will now examine three methods of creating the mail-merge document. Each has its advantages and disadvantages. You’ll have to decide which is best for use in your particular application. Note that the following pages are intended to be read in order. Each builds on the information in preceding pages and avoids excessive repetition by leaving some things out.

- Creating the data source in MS Word
- Creating the data source in MS Excel
- Creating the data source in a dBASE table

Conclusion

This document has only scratched the surface. As you continue to use and learn about the OleAutoclient class and MS Word, you’ll see that there is little to stand in the way of doing nearly anything you want, right inside your dBASE program. This interoperability can add immeasurable value to your dBASE program.

Acknowledgement: Thanks to Ken Mayer and Geoff Wass for their help with this document. Thanks also to Steve Hawkins and Barbara Coultry, my proofreaders, for the improvements they brought to my text.