This macro shows how to take the data from the current record in a Database and generate a letter in Write. The macro is broken into manageable junks in the description below. To see the macro in full, with tips on how to use it with your own data, see Create letter from database - code listing.
1. Initialize the macro
Sub MakeLetterFromDB
Dim myflds(9), lfld, s, tb, appWrite, mydoc, i
lastfld = 8
myflds(0) = "CustTitle"
myflds(1) = "Firstname"
myflds(2) = "Lastname"
myflds(3) = "Company"
myflds(4) = "Add1"
myflds(5) = "Add2"
myflds(6) = "Add3"
myflds(7) = "Add4"
myflds(8) = "Pcode"
The first line declares in advance all the variables that are going to used (good practice). Note the declaration of an array myflds with 9 elements, that holds the names of the fields to be used for the address block – obviously, this will need adjustment for your own database.
The variable lastfld is just a reminder that any loops addressing the myflds array will need to stop at 8 (arrays are zero based).
2. Get "hold off" the current table
Set tb = ActiveDataObject
ActiveDataObject returns the current table or query or relation and the line above saves a reference to this in the tb variable. This makes the macro as general as possible – no table name or type of object (table/query/relation) need be specified.
You could replace this with a specific table if necessary. For example
Set tb = ActiveDatabase.Tables("mytable")
would also do the job, providing mytable was open at the time (and pointing to the actual record you want to use).
3. Make an address block
s = ""
For i = 0 To lastfld
txt = tb.Fields(myflds(i)).Value
If Len(txt) > 0 Then
s = s & txt
If i < 2 Then
s = s & " "
Else
s = s & vbCr
End If
End If
Next
The code above loops through the fields listed in part 1 and adds them to a variable s. It checks to see if the fields is blank (in which case it's skipped) and also takes account of the first line of the address block, the code: If i < 2 then tests for this. All other address lines are added to s along with a new line (vbCr).
4. Ask the user if he wants to continue
i = MsgBox("Make a letter with this record?" & _
vbCr & vbCr & s, vbOKCancel, _
"Ability Database Macro Message")
If i = vbCancel Then
Exit Sub
End If
This shows a message box with the address block and asks the user to continue or not. If the user selects the cancel button, the macro is terminated.
5. Open a new write document and drop in the text
Set appWrite = CreateObject("AbilityWrite.Application")
Set mydoc = appWrite.Documents.Add("NORMAL")
mydoc.Text.Insert 0, s & vbCr & vbCr
mydoc.Selection.End = mydoc.Text.Count
mydoc.Selection.Start = Selection.End
appWrite.Activate
End Sub
The first two lines start Write and create a new document. The third line drops in the text.
The fourth and fifth line makes sure the cursor is at the end of the document and finally the Write application is made visible by the Activate command so the user can finish off the letter.