Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quickly switch excel data source in Word mail merge operation

    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

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  3. #3
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    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.
    Charles Kyle Kenyon
    Madison, Wisconsin

  4. #4
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    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.
    Last edited by Charles Kenyon; 2012-02-18 at 08:20. Reason: format vba as code and add instruction
    Charles Kyle Kenyon
    Madison, Wisconsin

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Charles, I'll try a modified version of your macro. Thanks Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •