[AccessD] Word Automation

Brett Barabash BBarabash at TappeConstruction.com
Wed Mar 12 13:54:00 CST 2003


>Brett,
>That's a good idea. Do you have some code you can send me that I can start
>with?

Since this is definitely on-topic, I'm posting this to the list.  Here goes:

Normally, I keep the data source text file in a consistent location, and
manually open the MailMerge data source in Word.  Access has the built-in
ability to export a table/query to a delimited file (there is even a "Mail
Merge" option in the file type list).  I use file I/O to do it instead, to
allow me more control over where the data comes from (i.e. multiple
queries).

By default Word uses tabs as field delimiters and carriage returns as record
delimiters.  Some of my documents require embedded tabs and carriage
returns, so I use tildes (~) as my field delimiters and carats (^) as my
record delimiters.  When you open the data source in the Word Mail Merge
helper, it will ask you to specify these delimiters.  

Here is a routine I wrote to generate a Mail Merge data source based on any
table or query:

Public Sub CreateMergeDataSource(ByVal strTblQry As String, ByVal
strFilePath As String)

    Const cFieldDelim = "~"
    Const cRecordDelim = "^"
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim lngFileNo As Long
    Dim strBuffer As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strTblQry, dbOpenSnapshot)
    
    'Initialize field name list (first line of data source)
    For Each fld In rs.Fields
        Select Case fld.Type
        Case dbGUID, dbLongBinary, dbMemo
            'Do not include these types of fields
        Case Else
            strBuffer = strBuffer & Chr$(34) & fld.Name & Chr$(34) &
cFieldDelim
        End Select
    Next fld
    
    strBuffer = strBuffer & cRecordDelim
    
    'Loop through all records in source table/query
    Do Until rs.EOF
        For Each fld In rs.Fields
            Select Case fld.Type
            Case dbGUID, dbLongBinary, dbMemo
                'Do not include these types of fields
            Case Else
                strBuffer = strBuffer & Chr$(34) & fld.Value & Chr$(34) &
cFieldDelim
            End Select
        Next fld
        
        strBuffer = strBuffer & cRecordDelim
        rs.MoveNext
    Loop
    
    rs.Close
    
    'Generate data source text file
    lngFileNo = FreeFile
    Open strFilePath For Output As #lngFileNo
    Print #lngFileNo, strBuffer
    Close #lngFileNo
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub



More information about the AccessD mailing list