Merge multiple Word printouts from Access database into one

[AccessThis]

Hi everyone,

I’m fairly new to Access and VBA, but I have set up the following code to export all the records in my database to a word template (C:\Template.do t) with bookmarks (bkfield1, bkfield2, etc.). Of course, when I run this code, each record prints out in a separate Word document. Is there any way for all the records to print out in a single Word document (i.e. to merge all the documents into one?)?

Thanks in advance for your help!



Dim objWord As Word.Applicatio n
Set objWord = New Word.Applicatio n
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT [Table].[Field1], [Table].[Field2], [Table].[Field3], [Table].[Field4], [Table].[Field5] FROM [Table] ORDER BY [Table].[Field1] DESC, [Table].[Field2];"

rs.Open strSQL, CurrentProject. Connection

Do While Not rs.EOF

objWord.Documen ts.Add "C:\Template.do t"
objWord.Visible = True

If Not IsNull(rs!Field 1) Then
objWord.ActiveD ocument.Bookmar ks.Item("bkfiel d1").Range.Te xt = rs!Field1
End If
If Not IsNull(rs!Field 2) Then
objWord.ActiveD ocument.Bookmar ks.Item("bkfiel d2").Range.Te xt = rs!Field2
End If
If Not IsNull(rs!Field 3) Then
objWord.ActiveD ocument.Bookmar ks.Item("bkfiel d3").Range.Te xt = rs!Field3
End If
If Not IsNull(rs!Field 4) Then
objWord.ActiveD ocument.Bookmar ks.Item("bkfiel d4”).Range.Text = rs!Field4
End If
If Not IsNull(rs!Field 5) Then
objWord.ActiveD ocument.Bookmar ks.Item("bkfiel d5").Range.Te xt = rs!Field5
End If

rs.MoveNext

Loop

Set rs = Nothing
Set objWord = Nothing

[FishVal]

Hi everyone,

I’m fairly new to Access and VBA, but I have set up the following code to export all the records in my database to a word template (C:\Template.do t) with bookmarks (bkfield1, bkfield2, etc.). Of course, when I run this code, each record prints out in a separate Word document. Is there any way for all the records to print out in a single Word document (i.e. to merge all the documents into one?)?

Thanks in advance for your help!

Hi, there.

Here is an example how to merge multiple (5 in the example) copies of Word document.
Word document "Template.d oc" mentioned below supposed to have bookmark "MergePosit ion" at the end.

Public Sub MergeMultipleDocumentCopies()
 
    Dim appWord As Word.Application
    Dim docDocument As Word.Document
 
    Set appWord = CreateObject("Word.Application")
 
    With appWord
        Set docDocument = .Documents.Add
        .Visible = True
        .Selection.InsertFile _
            FileName:="F:\mibd\Access\Samples\Docs\Template.doc", _
            Link:=False, Attachment:=False
    End With
 
    For i = 1 To 4
        With docDocument
            .Bookmarks("MergePosition").Select
            appWord.Selection.InsertFile _
                FileName:="X:\Template.doc", _
                Link:=False, Attachment:=False
        End With
    Next i
 
    Set docDocument = Nothing
    Set appWord = Nothing
 
End Sub
 
 

Hope this makes sence.

[AccessThis]

Hi, there.

Here is an example how to merge multiple (5 in the example) copies of Word document.
Word document "Template.d oc" mentioned below supposed to have bookmark "MergePosit ion" at the end.

Public Sub MergeMultipleDocumentCopies()
 
    Dim appWord As Word.Application
    Dim docDocument As Word.Document
 
    Set appWord = CreateObject("Word.Application")
 
    With appWord
        Set docDocument = .Documents.Add
        .Visible = True
        .Selection.InsertFile _
            FileName:="F:\mibd\Access\Samples\Docs\Template.doc", _
            Link:=False, Attachment:=False
    End With
 
    For i = 1 To 4
        With docDocument
            .Bookmarks("MergePosition").Select
            appWord.Selection.InsertFile _
                FileName:="X:\Template.doc", _
                Link:=False, Attachment:=False
        End With
    Next i
 
    Set docDocument = Nothing
    Set appWord = Nothing
 
End Sub
 
 

Hope this makes sence.

Thanks for your reply, FishVal! I'm just confused about the file you refer to in line 12 of your code. Is this an existing file or a file that is created from the database?

What I would like to do is have all the records in my database print out into a single Word file. In other words, I would like the merging to take place before a separate file is created for each. Does this code do that? I tried to work with it in my database, but since I don't understand line 12, I wasn't able to manipulate it well.

Thank you so much for your help. Sorry if I seem like a dunce, but I'm very new to VBA and Access!

[FishVal]

Thanks for your reply, FishVal! I'm just confused about the file you refer to in line 12 of your code. Is this an existing file or a file that is created from the database?

What I would like to do is have all the records in my database print out into a single Word file. In other words, I would like the merging to take place before a separate file is created for each. Does this code do that? I tried to work with it in my database, but since I don't understand line 12, I wasn't able to manipulate it well.

Thank you so much for your help. Sorry if I seem like a dunce, but I'm very new to VBA and Access!

Hi, there.

Surely the code should look like.

Public Sub MergeMultipleDocumentCopies()
 
    Dim appWord As Word.Application
    Dim docDocument As Word.Document
 
    Set appWord = CreateObject("Word.Application")
 
    With appWord
        Set docDocument = .Documents.Add
        .Visible = True
        .Selection.InsertFile _
            FileName:="X:\Template.doc", _
            Link:=False, Attachment:=False
    End With
 
    For i = 1 To 4
        With docDocument
            .Bookmarks("MergePosition").Select
            appWord.Selection.InsertFile _
                FileName:="X:\Template.doc", _
                Link:=False, Attachment:=False
        End With
    Next i
 
    Set docDocument = Nothing
    Set appWord = Nothing
 
End Sub
 
 

The code just give a hint how to perform document copies merge

Your code supposed to do the following.

Required: a template document "X:\Template.do c" with all your bookmarks defining where to drop field values and one additional bookmark defining where to merge the next record

Code logic

• open new document and insert to the document start template document "X:\Template.do c"
• start loop iterating through records while not eof
• drop field values on the bookmarks - this operation delete bookmark BTW
• merge "X:\Template.do c" to merge position bookmark, so you have document with bookmarks set ready for next iteration
• close loop

I hope this makes a sence.