Using Word under dBASE
by Gary White, dBVIPS

 
   

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.

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.