Quickly switch excel data source in Word mail merge operation

Topic

New Reply

I have a spreadsheet with several sheet tabs, each with a list of addressees. To create my letters, I mail merge to Word using the named range on sheet one, then I want to move to the named range on sheet two. The ranges are similar in each sheet, same col heads etc. so Word picks up my new merge data okay. However it means re-attaching the data source from scratch each time. I would like to just ‘go up a level’ and select the next sheet tab range as if it was files in a folder but can’t see how to. TIA for any help with this. Frank

Reply | Quote

Replies

A mailmerge can only work with one data source. Where that data source comes from an Excel workbook, that means only one worksheet. The simplest solution would be to have a separate mailmerge document for each worksheet.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

You can record a macro attaching each data source and attach a keyboard shortcut to each macro or (Word 2003 and below) create a menu choice for each macro. In Word 2007 & 2010 you could attach each macro to a button on the QAT.

Reply | Quote

What follows is excerpted from a macro that I use every day. It was based on a recorded macro so isn’t necessarily “elegant” but it works.

Code:

Sub AttachClients()
'   Written by Charles Kenyon
'   19 April 2005 revised 15 December 2006
'
'   Makes activedocument a mailmerge (letter) document and
'   attaches data.xls from [path].
'
    On Error Resume Next
'
'   Name of file
    Dim strFileName As String
    Dim strProvider As String
    strFileName = [path]Data.xls"
'
'   Attach Merge list
    ActiveDocument.MailMerge.OpenDataSource strFileName, , , False, _
        True, False, "", "", False, "", "", "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=strFileName;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:" _
        , "SELECT * FROM `Clients$`", "", , wdMergeSubTypeAccess
'
'   Show merge data
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
'
End Sub

Note that if you use this you would have to put in your actual path and filename. I believe that `Clients$` indicates the named range that is used.

I wrote this because I use individual documents from a merge and when done with a merge I change the merge file back to a normal word document (retaining the merged data in preview mode) and save it. This lets me reopen the document without worrying about it updating to a different client’s data. If I want to use the document with a different client, I just use the macro and pick a different client.

Reply | Quote

Thanks Charles, I’ll try a modified version of your macro. Thanks Frank

Reply | Quote