0

On multiple documents I use to put create a PDF for, the same merge fields are seen on each document.

I have mail merge fields reading from an Excel workbook to the word documents with the following columns extracted in the Word docs.

  • Name_of_account
  • Account_number
  • Effective_date_of_account_policy

These three same rows are seen in multiple documents, and I know how to merge these fields using "preview results" tab to view the merged data for each individual document.

How would I — for example — grab all these docs at once and have all three of these fields populate the merge fields from the excel workbook, without having to open up each document, replace it with merged data, saving, and repeating it for each document I open.

Or, is there a program where I can just shoot all these documents into something, and when I open each document, the merge fields are populated?

0

1 Answer 1

0

Here are two free utilities for batch processing if you want to do them all at once:

I suspect, though, you want to use them one at a time. I have done this for years and am happy to share my method.

  1. I do not use documents but Word templates. They create a new document, based on the template that has the merge fields. The templates are not merge documents but non-merge.

  2. The template has an AutoNew macro that runs when that new document is created. It changes the new document to a letter merge document and attaches the data source. It sets the view to preview the merge.

  3. Since this was set to work in any of the computers in my office, the macro identifies that data source relative to the location set for workgroup templates. That way it works for everyone in the office.

  4. There is a macro that can be used when you are done that changes it from a merge document to a regular document and locks the merge fields so they retain their content. This is attached to a button on the Mailings tab but could simply be a triggered by a keyboard shortcut or QAT button.

    Ribbon Buttons

  5. There is another macro that can re-attach the data source and reactivate the merge fields. This one can be triggered using a QAT button.

The macro that sets the document as a merge document and attaches the data is in a Global Template. It is called from the AutoNew macro in the templates.

The other macros are in the global template. The QAT button and Ribbon button are also in the global template. That way any modifications can be made for all of the templates for the merges by changing the one in the global template.

Here are the macros in the Global Template.

Sub AttachData()
'   Written by Charles Kenyon
'   19 April 2005 revised 15 December 2006
'       revised 12 March 2014 to clean up attachment code
'
'   Requires WorkGroupPath function
'
'   Makes activedocument a mailmerge (letter) document and
'   attaches Clients_Merge.xls from Parts folder of Workgroup Templates folder.
'
'   Then displays search dialog and goes to client, makes sure merge info is
'   displayed instead of merge codes.
'
    On Error Resume Next
'
'   SET THE FOLLOWING CONSTANTS TO REFLECT THE LOCATION OF YOUR DATA IN RELATION TO THE WORKGROUP TEMPLATES FOLDER
    Const strDATAFILE As String = "Parts\Merge Data\Clients_Merge.xlsm"
    Const strSHEET_TABLE As String = "`Clients$`"
'   THE ABOVE LIKELY WILL REQUIRE CONSIDERABLE TWEAKING
'
    Call MergeFieldUnlockAllStory 'if merge fields locked, unlock
'
'   Name of file
    Dim strFileName As String
    
    Dim strProvider As String
    strFileName = WorkGroupPath & strDATAFILE
'
'   Attach Merge list
    With ActiveDocument.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=strFileName, _
            SQLStatement:="SELECT * FROM " & strSHEET_TABLE ', _
'
'       Show merge data
        .ViewMailMergeFieldCodes = False
    End With
'
'   Find client
    Application.Dialogs(wdDialogMailMergeFindRecipient).Show
'
    On Error GoTo 0
'
'   Disconnect from Merge - OPTIONAL - use if you are not going to add merge fields in document after creation
    Call DisconnectMerge
End Sub

Sub DisconnectMerge()
' Charles Kenyon November 20, 2020
' DisconnectMerge Macro
'
'
    Call MergeFieldLockAllStory ' locks merge fields with current content
    '   MAKE CURRENT DOCUMENT NON-MERGE - ORDINARY DOCUMENT
    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
End Sub

Sub MergeFieldLockAllStory()
'   Written by Charles Kyle Kenyon 15 November 2020
'
'   All Story Field Locker - Merge fields
    Dim oField As Field
    Dim oStory As Range
'    On Error Resume Next
    For Each oStory In ActiveDocument.StoryRanges
    ' This goes into headers and footers as well as the regular document
        Do
            For Each oField In oStory.Fields
                If oField.Type = wdFieldMergeField Then
                    oField.Locked = True
                End If
            Next oField
            Set oStory = oStory.Next
        Loop Until oStory Is Nothing
    Next oStory
    Set oStory = Nothing
    Set oField = Nothing
End Sub

Sub MergeFieldUnlockAllStory()
'   Written by Charles Kyle Kenyon 15 November 2020
'
'   All Story Field Locker - Merge fields
    Dim oField As Field
    Dim oStory As Range
'    On Error Resume Next
    For Each oStory In ActiveDocument.StoryRanges
    ' This goes into headers and footers as well as the regular document
        Do
            For Each oField In oStory.Fields
                If oField.Type = wdFieldMergeField Then
                    oField.Locked = False
                End If
            Next oField
            Set oStory = oStory.Next
        Loop Until oStory Is Nothing
    Next oStory
    Set oStory = Nothing
    Set oField = Nothing
End Sub

Private Function WorkGroupPath() As String
'   Written by Charles Kenyon
'   February 28, 2003 Updated November 15, 2020
'
'   Used by templates menus to set location of templates.
'   Returns workgroup tempates path with "\" at the end.
'
'   This is needed because if the folder is a network drive rather
'   than a folder, it will have the "\" already. If it is a folder,
'   it will not have the backslash. This function gives a string
'   with the backslash in either case.
'
    Let WorkGroupPath = Application.Options.DefaultFilePath(wdWorkgroupTemplatesPath)
    If Right(WorkGroupPath, 1) <> "\" Then
        Let WorkGroupPath = WorkGroupPath & "\"
    End If
End Function

Here is the macro that goes into the document templates, which have the merge fields but are not, themselves, merge documents.

Sub AutoNew()
' Charles Kenyon November 20, 2020
     Application.Run AttachData
End Sub

Here is a link to a template with this code in it and more. I expect to tweak this later to add QAT buttons and Ribbon buttons for the macros.

6
  • While well intentioned, this answer doesn’t really fit as if it were an answer that would work here. Additionally the original question is effectively asking for a software recommendation or asking one to code a macro for them. So that’s that. So that said, when you say, “If you want to do this, I would be happy to share the macros with you.” I believe this answer would be far more solid — even on an off-topic question — if you focused your answer on how to use that macro in this context. Nov 15, 2020 at 1:22
  • @Giacamo1968 The OP did not ask for a macro solution. I offered one if the poster is willing to use macros and work at it. I don't care for the effort to edit my macros to post here if they will not be of use to the OP. There is no off-the-shelf solution that I know of. I did offer two utilities that could assist in batch processing if that is what is desired. How is this question off-topic in Super User? (I originally thought it was because I thought it was posted in Stack Exchange. As you can see, I am still new here myself.) Nov 15, 2020 at 4:11
  • “How is this question off-topic in Super User?” Read up here. Specifically, “…asking for a product, service or learning material recommendation…” which this question basically is a recommendation on how to do something. How this question would be on topic is if the original poster showed their work and said, “I used this macro to do this; why is it not working as expected.” Or, “I am attempting to use these macros to do something on a large scale; what can be done next.” Otherwise, your answer is just attempting to cover all bases with no one answer. Nov 15, 2020 at 4:38
  • Most of the documents i combine to create a pdf are not Word Templates, but i can convert them. Each document should have the same 3 fields, but for each document, it will have an "endorsement number" field which is different for each document. I'd be happy to try your macros and see if it works.
    – tytds
    Nov 15, 2020 at 5:42
  • For sequential numbering of documents, see Graham Mayor's Add-In at gmayor.com/automatic_numbering_documents.htm. Nov 15, 2020 at 14:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .