Merge multiple Word printouts from Access database into one | Newbie | | Join Date: Jul 2007
Posts: 7
| | |
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.dot) 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.Application
Set objWord = New Word.Application
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.Documents.Add "C:\Template.dot"
objWord.Visible = True
If Not IsNull(rs!Field1) Then
objWord.ActiveDocument.Bookmarks.Item("bkfield1"). Range.Text = rs!Field1
End If
If Not IsNull(rs!Field2) Then
objWord.ActiveDocument.Bookmarks.Item("bkfield2"). Range.Text = rs!Field2
End If
If Not IsNull(rs!Field3) Then
objWord.ActiveDocument.Bookmarks.Item("bkfield3"). Range.Text = rs!Field3
End If
If Not IsNull(rs!Field4) Then
objWord.ActiveDocument.Bookmarks.Item("bkfield4”). Range.Text = rs!Field4
End If
If Not IsNull(rs!Field5) Then
objWord.ActiveDocument.Bookmarks.Item("bkfield5"). Range.Text = rs!Field5
End If
rs.MoveNext
Loop
Set rs = Nothing
Set objWord = Nothing
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Merge multiple Word printouts from Access database into one
Quote:
Originally Posted by 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.dot) 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.doc" mentioned below supposed to have bookmark "MergePosition" 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.
| | Newbie | | Join Date: Jul 2007
Posts: 7
| | | re: Merge multiple Word printouts from Access database into one
Quote:
Originally Posted by FishVal Hi, there.
Here is an example how to merge multiple (5 in the example) copies of Word document.
Word document "Template.doc" mentioned below supposed to have bookmark "MergePosition" 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!
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Merge multiple Word printouts from Access database into one
Quote:
Originally Posted by AccessThis 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.doc" 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.doc"
- start loop iterating through records while not eof
- drop field values on the bookmarks - this operation delete bookmark BTW
- merge "X:\Template.doc" to merge position bookmark, so you have document with bookmarks set ready for next iteration
- close loop
I hope this makes a sence.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,295 network members.
|
