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