Ask the MultiValued Visual Basic Expert - #3

(as published in Spectrum magazine Mar/Apr 1997)

code updated Aug. 21, 1998

To email your questions to "Ask the MultiValued VB Expert", click here.
Copyright 1996-98 Caduceus Consulting. All rights reserved.

Printing a Word document with data from Visual Basic

Our manufacturing company requires the ability to read data from a Pick-type host, edit it in Visual Basic, and then print it from MSWord. I am told that this is possible, but the VB manuals are not much help. The WinLink product from Via Systems will get the data into VB but then I’m stuck. Can you assist us? - R. Sullivan, Lavelle Industries

I am not surprised that you had trouble with this one. The best bridge between Visual Basic and MS Word is OLE Automation. A solution using ODBC can go directly from your database into Microsoft Word, but would not easily give you the edit ability using VB.

The problem with trying to use OLE Automation is that, by its very nature, it goes between two different applications, but the documentation that you really need is usually found in neither of the corresponding manuals! I have found the same difficulty applies when you call Microsoft VB support.

The good news is that once the coding issue is solved, you actually have a very powerful tool. [A sophisticated version of this solution code is now downloadable from this site!] For the purposes of this web page, I will assume that you already have the ability to bring the data from your MultiValue host. (The WinLink product mentioned above is just one way of doing this.) The example that I am going to use will take data from Visual Basic and merge it into a Microsoft template that any Word user can layout. You can embed logos, set multiple fonts, and do other formatting that would be very tricky if you were just printing straight from Visual Basic.

The trick is to embed strings called ‘tokens’ in the Word template document. The Visual Basic program will access the document as an OLE object, search for the tokens, and replace them with the actual data (limited to 255 characters!). It can then save or print the document. The font settings used for each token will be what are used for the data that replaces it.

Here is my sample Word template document (as only the Web's HTML can show it), saved as c:\winword\quote.dot:

Caduceus Consulting Quote . . . Date: ~{TODAY}

Prepared for ~{CONTACTNAME} of ~{COMPANYNAME}

Dear ~{CONTACTNAME}:

I estimate the cost of this service to be: $~{DOLLARS}
and that the work can be completed by: ~{COMPLETION}

I can then use the following Visual Basic code to invoke MS Word, read the template document, replace the tokens with my data, print the document, and then close Word:

Sub PrintBtn_Click()
Dim MSWordDoc As Object
Set MSWordDoc = CreateObject("Word.Basic")
MSWordDoc.EditReplace Wrap:=1
' check whole doc
MultiRepl% = False
OldStr$ = "~{TODAY}" : NewStr$ = Date
GoSub DoReplace
OldStr$ = "~{COMPANYNAME}" : NewStr$ = Text2.Text
GoSub DoReplace
OldStr$ = "~{DOLLARS}" : NewStr$ = Text3.Text
GoSub DoReplace
OldStr$ = "~{COMPLETION}" : NewStr$ = Text4.Text
GoSub DoReplace
MultiRepl% = True
OldStr$ = "~{CONTACTNAME}" : NewStr$ = Text1.Text
GoSub DoReplace
With MSWordDoc
. . .ToolsOptionsPrint Background:=False
. . .FilePrint
. . .FileClose 2 ' avoid prompt for saving doc
. . .ToolsOptionsPrint Background:=True
End With
Set MSWordDoc = Nothing
Exit Sub

DoReplace:
. . MSWordDoc.EditReplace Find:=OldStr$, Replace:=NewStr$, _
. . . . . . ReplaceAll:=MultiRepl%, ReplaceOne:=Not(MultiRepl%)
. . Return
End Sub

Note that there is nothing special about the “~{...}” characters - I simply chose them to designate my tokens since they were unlikely to be mistaken for real text. For more information on things that you can do to the MS Word document object, see the “wrdbasic.hlp” file that is usually supplied with Microsoft Word, or pick up a copy of the book “Word Developer’s Kit” (Microsoft Press). Also note that I believe this code will only work in Visual Basic version 4.0 (sorry VB3 users!).

[A sophisticated version of this solution code is now downloadable from this site!]
To email your questions to "Ask the MultiValued VB Expert", click here.
Copyright 1996-98 Caduceus Consulting. All rights reserved.
Revised: August 21, 1998.

Return to Caduceus Consulting Home Page

Copyright 2006 intellact
Last modified: Thursday May 25, 2006