Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to only open data source once per folder
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. -- Carroll |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to only open data source once per folder
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 "Carroll Muir" wrote in message ... 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. -- Carroll |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
word cannot open file as a data or header source bec it is a mail merge main doc | Mailmerge | |||
Word 97 "cannot open data source" | Mailmerge | |||
Select Table dialog box comes up twice when open data source | Mailmerge | |||
Mailmerge data source in an Access database will not open | Mailmerge | |||
Open Data Source Dialog Box | Mailmerge |