View Single Post
 
Old 05-01-2018, 07:39 AM
RMerckling RMerckling is offline Windows 7 64bit Office 2010 32bit
Novice
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA

Hey all,

Currently, I have some code that runs a mail merge from my Excel Workbook to a Word Doc. The code also selects all within the Word Doc, updates the fields and then unlinks them from the mail merge.

Code:
    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    Worksheets("Data").Activate
       
    'Opens New Plan Doc Template
    Set appWD = CreateObject("Word.Application")
    appWD.Visible = True

    appWD.Documents.Open Filename:=PlanDocTemplate
    
    ActiveDocument.MailMerge.OpenDataSource Name:=strWorkbookName, _
    Format:=wdMergeInfoFromExcelDDE, _
    ConfirmConversions:=True, _
    ReadOnly:=False, _
    LinkToSource:=True, _
    AddToRecentFiles:=False, _
    PasswordDocument:="", _
    PasswordTemplate:="", _
    Revert:=False, _
    Connection:="Entire Spreadsheet", _
    SQLStatement:="SELECT * FROM `Data$`", _
    SQLStatement1:="", _
    SubType:=wdMergeSubTypeOther
    
    appWD.Visible = True
   
    appWD.Selection.WholeStory
    appWD.Selection.Fields.Update
    appWD.Selection.Fields.Unlink
    'New Code Here to update & unlink <<footer>> merge field
    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
    appWD.ActiveDocument.Save
This works great for now, but the footer of the word doc also has a merge field "<<footer>>" which doesn't get selected through the code, appWD.Selection.WholeStory. I'm imagining there's a way to select the entire footer and appWD.Selection.Fields.Update & .Unlink but then that would capture the automated page number at the bottom and leave it static, which I wouldn't want. I.E. every page number at the bottom would be Page 1, Page 1, Page 1...

Is there a way to tell Excel VBA to update and unlink the individual merge field "<<footer>>" only?

Thanks all,
Rich
Reply With Quote