Introduction
This article will describe how easily you can merge a document from Microsoft Access 2003 database.
Background
It is a very common practice to create various word documents from the database and merge the document. Most VBA developers are familiar with that. In this article, I would like to demonstrate how you can fetch a record form the database tables by executing transact SQL statement and merge all the records in a document.
Using the Code
This is a very effortless way. You just need some basic idea on MailMerge.OpenDataSource
method.
MailMerge.OpenDataSource
: Attaches a data source to the specified document, which becomes a main document if it's not one already.
Sample Example
Public Sub MergeDocument(strDocumentPath As String _
, strDocumentFile As String _
, strSQLStatement As String)
On Error GoTo ErrorHandler
Dim ObjApplication As Word.Application
Dim ObjDocument As Word.Document
DoEvents
Dim strSourceName As String
Dim MergeSubType As WdMergeSubType
strSourceName = "C:\MyDatabase.mdb"
MergeSubType = wdMergeSubTypeWord2000
Set ObjDocument = GetObject(strDocumentPath & strDocumentFile, "Word.Document")
ObjDocument.Application.Visible = True
DoEvents
ObjDocument.MailMerge.OpenDataSource Name:=strSourceName,
SQLStatement:=strSQLStatement
DoEvents
With ObjDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute Pause:=True
End With
DoEvents
ObjDocument.Close wdDoNotSaveChanges
Set ObjDocument = Nothing
Set ObjApplication = Nothing
Exit_mergeDocument:
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume Exit_mergeDocument
End Sub
Conclusion
I hope that this simple example might be helpful to you.
History
- 5th November 2009: Initial post