Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

PB, OLE and Word

OLE automation is a technology by which you can share information and manipulate services

Oh, no, that dreaded error calling external function is back! Ever get this message while working with OLE (object linking and embedding)? Well, I have, while trying to integrate PB, OLE, and Word 2000.

PowerBuilder is the main tool our organization uses to write our "front office" application, and all our marketing material is printed using mail merge with OLE and Microsoft Word 7. We haven't had any problems so far. However, we're in the process of moving to Microsoft Office 2000. One of my tasks was to test if the mail merge worked with Word 2000. To infinity and beyond it didn't work! I kept getting the dreaded message. After many unsuccessful attempts, I searched the Sybase solutions Web site (www.sybase.com/solutions/) for PB, OLE articles. To my surprise, I learned that I wasn't the only developer tearing my hair out - there were others out there in cyberspace.

Success strikes! At last I got PB6.5, OLE, and Word 2000 to talk. Many of the Word 2000 object function calls have changed. The intent of this article is to guide you, step-by-step, through the approach I took to get the mail merge working.

Understanding OLE
OLE automation is a technology by which you can share information and manipulate services (functions, methods, properties) of other applications (e.g., Microsoft Word, Excel, Outlook). While communicating with another application it's essential to know how to access its services and, of course, the correct syntax. Word 2000 uses Visual Basic for Applications (VBA) syntax. Now where can you find the correct VBA functions syntax and, more important, how do you convert it into the correct PB syntax? The answer: using the Word macro editor and the Visual Basic Object Browser.

Using the Word Macro Editor
Open Word, click Tools on the toolbar, scroll to Macro, then Record New Macro... (see Figure 1).

Enter the new macro name and click OK. Perform the tasks you wish (e.g., execute a mail merge) and when completed, turn off the recording. To obtain the VBA function syntax, click Tools on the toolbar, scroll to Macro again, and select Macros.... Select your new macro and click Edit. This will open the Word macro editor (see Figure 2), and you can view the VBA syntax (see Figure 3). To get more information on the syntax in your new Sub () function, click View on the toolbar of the macro editor, then Object Browser.

Obtaining Information in the Object Browser
The main sections to focus on in the Object Browser are "Search/Results", "Classes", "Members of", and "Syntax" (see Figure 4). Type in the name of the function you'd like more information about (e.g., Open). Scroll through the list of results and select the Class and Member you're interested in. The Syntax section of the Browser shows the precise syntax of the function. Arguments in square brackets ([]) are optional, and the commas indicate where the function expects arguments. Constants and their equivalents can be found by scrolling through the "Classes" and "Members of" sections.

Converting VBA Syntax to PB Syntax
This is where the information in the Object Browser becomes invaluable. Search for the relevant VBA function you need and convert it into a PB OLE function call using the dot notation. Here's a sample of VBA syntax:

 

Documents.Open FileName:="Document.txt",
ConfirmConversions:= False, _
ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", _
Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

and the converted PB syntax:

 

Ole_object.Documents.Open ("Document.txt",
False, False, False, "", "", False, "", "",0)

Optionally, you can pass only the document name to the Documents.Open function, as all other arguments are optional. Again, to find the constant's equivalent, look in the "Classes" section for wdOpenFormat. Click on wdOpenFormatAuto in the "Members of" section, and the value 0 is displayed in the "Syntax" section.

OLE, Word 2000, and Mail Merge
Before I jump into the code, let me set the scene and explain in some detail how we print our marketing material using mail merge. Users can select two types of letters from a range of letters to mail to our customers:

  1. Standard letter: A DataWindow is displayed, with the customer's current information (e.g., title, first name, last name, address) for the user to verify. If the information isn't up to date, data can be modified prior to ordering the letter.
  2. Data aware letter: In addition to the standard letter information, the user enters other information (e.g., sum insured, contents value, premium amount) that's required to be mail merged with the letter.

Each letter is set up in Word 2000 as a main document - a form letter with a unique header and data source attached to it. The header source is set up as a tab-delimited document and contains mostly static information. However, the data source document information changes frequently and is refreshed with data entered by the user in the DataWindow. Listing 1 has the code I use to refresh the data document. Each data item in the DataWindow is surrounded by quotes and separated by a tab delimiter.

Once the data document is refreshed, it's easy sailing from here. Listing 2 shows the code I use to perform the mail merge and print two copies of the letter. Although I've included comments to identify what each function call is performing, I'll explain a couple of areas I had hassles with:

  • ConnectToNewObject (OLE Class): While connecting to the Word application, I was putting the version number within the quotes (i.e., "Word.Application.9"), which didn't work. After a few variations of the code, it worked without the dot version number.
  • Application.PrintOut ([Background], [Append], [Range], [OutputFileName], [From], [To], [Item], [Copies].....): Although all the arguments in the PrintOut Function call are optional, I needed to print two copies of the mail merge letter. After many attempts at various argument type (e.g., Boolean, String, Number) combinations sequence, I determined the correct argument types leading to the copies argument (False,False,0,"","","",0,2).

Final Thoughts
There are many techniques to performing a mail merge using OLE and Word (e.g., drive the mail merge using a macro and invoke the macro via OLE). Whichever way you employ it, this approach can be used to divulge the syntax of any Word VBA function call.

Happy OLEing!

 

More Stories By Andrew Mendes

Andrew Mendes is the manager of support systems at Australian Unity, Melbourne. He has 6 years of client/server programming experience, mostly using PowerBuilder and Microsoft SQL Server.

Comments (1) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
KEWAL 01/04/08 03:27:02 AM EST

Hi, I guess tghe links in the page has been disabled. Can you enable them.