! Word Merge !

  • Thread starter Thread starter Wembly
  • Start date Start date
W

Wembly

Hi,

I want to merge data from Access to Word and was wondering
which method is the most efficient and quickest?

I have a form where the user clicks on a button to load
another form which contains a list of letters which they
can then select to load in Word.

The data is then merged into the letter template, and
creates the merged letter (should there be two windows
open here one for the template and one for the merged
letter??)

Also, I cannot get all the data from the form as it will
also need to come from a table.

So far, the query that I've written involves embedded SQL
in a recordset. This will get me the data that I want, but
how does Word know where the data source is? Is it
possible to create a temp table with the recordset data
from code, which Word can then use as the data source?

Thanks for your help.

Wembly
 
My solution to a similar problem was to gather all the
data with a query in Access. Then output the query to an
excel file (this makes connecting to it with Word *MUCH*
easier), launch word, open the template and run a macro
embedded in the Word template to do the merge. Sounds
like a pain, but works like a charm. One benefit: it
doesn't matter what level of macro security the user has
set. Here's my code:

IN ACCESS:
DoCmd.OutputTo acOutputQuery, "{query to gather all
info}", acFormatXLS, "{your excel file}", False
' Dump the query to an excel file,
Set wordApp = CreateObject("Word.application")
'start MS Word
wordApp.Documents.Open ({template document})
' open the template
wordApp.Application.Run "Automailmerge"
' run the macro embedded in the template
wordApp.Application.Visible = True
' make Word visible
wordApp.Activate
' make Word the Active window
Set wordApp = Nothing

WORD TEMPLATE MACRO:
Sub AutoMailMerge()

With ActiveDocument.MailMerge
.OpenDataSource Name:="{your excel file}", _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="r1c1:r2c75",
SQLStatement:="SELECT * FROM `{query to gather all info}$`"

.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Windows(2).Close SaveChanges:=wdDoNotSaveChanges 'Activate

End Sub

Depending on your version of Word, the MailMerge
datasource wants either a SQL string or the connection
row/column. Passing it both covers both bases. The
r1c1:r2c75 is an arbitrary size larger than anything I
would pass - just so it would grab everything.

Hope this helps!
 
Back
Top