Example: generating form letters using OLE
This example takes names and addresses from a DataWindow object
and letter body from a MultiLineEdit and creates and prints letters in
Microsoft Word using VBA scripting.
To set up the form letter example:
-
Create a Word document called CONTACT.DOC with four
bookmarks and save the file in your PowerBuilder directory.These are the bookmarks:
-
name1 — for the name in the return address
-
name2 — for the name in the salutation
-
address1 — for the street, city, state, and zip in the
return address -
body — for the body of the letter
The letter should have the following content:
1234567891011121314Multimedia Promotions, Inc.1234 Technology DriveWestboro, MassachusettsJanuary 12, 2003[bookmark name1][bookmark address1]Dear [bookmark name2]:[bookmark body]Sincerely,Harry MogulPresidentYou could enhance the letter with a company and a signature
logo. The important items are the names and placement of the
bookmarks. -
-
In PowerBuilder, define a DataWindow object called
d_maillist that has the following columns:id
first_name
last_name
street
city
state
zip
You can turn on Prompt for Criteria in the DataWindow object
so the user can specify the customers who will receive the
letters. -
Define a window that includes a DataWindow control called
dw_mail, a MultiLineEdit called mle_body, and a CommandButton or
PictureButton:
-
Assign the DataWindow object d_maillist to the DataWindow
control dw_mail. -
Write a script for the window’s Open event that connects to
the database and retrieves data for the DataWindow object. The
following code connects to a SQL Anywhere database. (When the
window is part of a larger application, the connection is
typically done by the application Open script.)1234567891011121314151617181920212223/**************************************************Set up the Transaction object from the INI file**************************************************/SQLCA.DBMS=ProfileString("myapp.ini", &"Database", "DBMS", " ")SQLCA.DbParm=ProfileString("myapp.ini", &"Database", "DbParm", " ")/**************************************************Connect to the database and test whether theconnect succeeded**************************************************/CONNECT USING SQLCA;IF SQLCA.SQLCode <> 0 THENMessageBox("Connect Failed", "Cannot connect" &+ "to database. " + SQLCA.SQLErrText)RETURNEND IF/**************************************************Set the Transaction object for the DataWindow control and retrieve data**************************************************/dw_mail.SetTransObject(SQLCA)dw_mail.Retrieve() -
Write the script for the Generate Letters button (the script
is shown below).The script does all the work, performing the following
tasks:-
Creates the OLEObject variable
-
Connects to the server (word.application)
-
For each row in the DataWindow object, generates a
letterTo do so, it uses VBA statements to perform the tasks in
the following table.VBA statements
Task
open
Opens the document with the
bookmarksgoto and typetext
Extracts the name and address information
from a row in the DataWindow object and inserts it
into the appropriate places in the
lettergoto and typetext
Inserts the text the user types in
mle_body into the letterprintout
Prints the letter
close
Closes the letter document without saving
it -
Disconnects from the server
-
Destroys the OLEObject variable
-
-
Write a script for the Close button. All it needs is one
command:1Close(Parent)
Script for generating form
letters
The following script generates and prints the form
letters:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
OLEObject contact_ltr integer result, n string ls_name, ls_addr /*************************************************** Allocate memory for the OLEObject variable ***************************************************/ contact_ltr = CREATE oleObject /*************************************************** Connect to the server and check for errors ***************************************************/ result = & contact_ltr.ConnectToNewObject("word.application") IF result <> 0 THEN DESTROY contact_ltr MessageBox("OLE Error", & "Unable to connect to Microsoft Word. " & + "Code: " & + String(result)) RETURN END IF /*************************************************** For each row in the DataWindow, send customer data to Word and print a letter ***************************************************/ FOR n = 1 to dw_mail.RowCount() /************************************************ Open the document that has been prepared with bookmarks ************************************************/ contact_ltr.documents.open("c:pbdocscontact.doc") /************************************************ Build a string of the first and last name and insert it into Word at the name1 and name2 bookmarks ************************************************/ ls_name = dw_mail.GetItemString(n, "first_name")& + " " + dw_mail.GetItemString(n, "last_name") contact_ltr.Selection.goto("name1") contact_ltr.Selection.typetext(ls_name) contact_ltr.Selection.goto("name2") contact_ltr.Selection.typetext(ls_name) /************************************************ Build a string of the address and insert it into Word at the address1 bookmark ************************************************/ ls_addr = dw_mail.GetItemString(n, "street") & + "~r~n" & + dw_mail.GetItemString(n, "city") & + ", " & + dw_mail.GetItemString(n, "state") & + " " & + dw_mail.GetItemString(n, "zip") contact_ltr.Selection.goto("address1") contact_ltr.Selection.typetext(ls_addr) /************************************************ Insert the letter text at the body bookmark ***********************************************/ contact_ltr.Selection.goto("body") contact_ltr.Selection.typetext(mle_body.Text) /************************************************ Print the letter ************************************************/ contact_ltr.Application.printout() /************************************************ Close the document without saving ************************************************/ contact_ltr.Documents.close contact_ltr.quit() NEXT /*************************************************** Disconnect from the server and release the memory for the OLEObject variable ***************************************************/ contact_ltr.DisconnectObject() DESTROY contact_ltr |
Running the example
To run the example, write a script for the Application object
that opens the window or use the Run/Preview button on the
PowerBar.
When the application opens the window, the user can specify
retrieval criteria to select the customers who will receive letters.
After entering text in the MultiLineEdit for the letter body, the user
can click on the Generate Letters button to print letters for the
listed customers.