How to only open data source once per folder

[Carroll Muir]

I'm using mail merge to input data into a set of word precendents bundled in
a folder. I have put merge fields in the various documents and have an Excel
spreadsheet for the variables . However I have to open the data source to
effect the merge in each doc which is cumbersome. Is there a way to speed
things up so that the data source can attach to all documents in a folder,
not just one at a time? I also thought about writing a macro to open the
data source but can't find a keyboard shortcut for this to include in the
macro.

 

[Peter Jamieson]

The most you can do is to use VBA to open the data source for each word mail
merge main document, rather than doing it by hand. In this case you might
use something like the following macro. However, because it might need a
considerable amount of tweaking before it works you would have to weigh up
whether or not it is worth using in your case. For example,
a. I have assumed you are using Word 2003 (you didn't say which version you
are using, and Word 2000 is considerably different)
b. if you haven't used VBA there's quite a bit to learn (see e.g.
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm
c. to get this to work, you will probably also have to apply the changes
described in

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document"
at
http://support.microsoft.com?kbid=825765

....among other things.

here's the macro code:

Sub SetDataSourceForFolder()

Dim strDataSourceFile As String
Dim strConnection As String
Dim strQuery As String
Dim strFile As String
Dim strFolder As String
Dim objDoc As Document

' Specify the name of your folder containing .doc files in here
strFolder = "C:\Test\"

' Specify the name of your Excel data source here
strDataSourceFile = "c:\test\x.xls"

' In some cases you may need to
' define the data source connection string, e.g.
' strConnection = _
' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
' strDataSourceFile & _
' ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:Engine
Type=35;"

' Define the SQL query for the data source here

strQuery = "SELECT * FROM `Sheet1$`"

strFile = Dir$(strFolder & "*.doc")
While strFile <> ""
'Open document
Set objDoc = Documents.Open(strFolder & strFile)

' To open a .xls in Word 2003 you typically need the .xls
' path name and a query to stop the dialog box popping up
objDoc.MailMerge.OpenDataSource _
Name:=strDataSourceFile, _
sqlstatement:=strQuery ', _
'Connection:=strConnection, _
'subtype:=wdMergeSubTypeOther
' if you want to do the merge now, do it here
'objDoc.MailMerge.Execute pause:=False
objDoc.Close SaveChanges:=wdSaveChanges

strFile = Dir$()

Wend

End Sub

Peter Jamieson