|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 Is there a way to tell Excel VBA to update and unlink the individual merge field "<<footer>>" only? Thanks all, Rich |
#2
|
||||
|
||||
The problem with what you're doing is that you're just messing around with the mailmerge main document - you never actually execute the merge. If you did that, a new document would be produced with none of the fields (i.e. just the data).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
The merge is executed using this code and all the correct fields are in place. The code "appwd.Selection.Wholestory ... appwd.Selection.Update .. appwd.Selection.Unlink" works as well, it just doesn't capture the mergefield in the footer. Is there something I'm missing, Paul? Does executing the code automatically merge fields and then unlink like what I'm trying to manually achieve?
Thanks, Rich |
#4
|
||||
|
||||
The merge is NOT executed - ever - using your code. For that you would need a line like:
appWD.ActiveDocument.MailMerge.Execute which will create a new document with none of your mergefields remaining, in which case none of your: appWD.Selection.WholeStory appWD.Selection.Fields.Update appWD.Selection.Fields.Unlink is required.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Paul, perhaps you could clarify, "the merge is not executed"? I enter specific fields in my Excel workbook, I run the code and those fields appear in the newly created Word doc. That's not executing?
Adding the appWD.ActiveDocument.MailMerge.Execute line gives me an error, could you assist me in where to place that or what to get rid of for it to work? Thanks. |
#6
|
|||
|
|||
This is crazy but my code was working... few things I planned on fixing later but ever since adding the execute mail merge code and receiving my initial error, I keep receiving error: "run-time error '-2147023170 (800706be)': automation error the remote procedure call failed" even when the code is out.
Any idea? Thanks. Last edited by RMerckling; 05-09-2018 at 05:09 AM. |
#7
|
||||
|
||||
Quote:
Quote:
appWD.Visible = True
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
Error: "run-time error '-2147023170 (800706be)': automation error the remote procedure call failed"
Receiving this with or without execute code now, not sure why. Could there be a process happening behind the scenes that keeps causing this error? I've restarted, killed processes, etc. no luck yet. Thanks. |
#9
|
||||
|
||||
Perhaps you should re-start your PC. That said, you wouldn't be having any of these issues if you took the approach I suggested in: https://www.msofficeforums.com/word-...tml#post127871
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
As stated above, I have restarted and yes, Paul, I'm looking into using that new process but would like to understand the one I currently am using considering it was working. Any other ideas why/how I could keep receiving this error even when the code is removed?
|
#11
|
|||
|
|||
Quick update: Process still works for other users, just not me & my computer so something must be caught up in my files. I've deleted all temp word files but to no avail. Any suggestions would be greatly appreciated.
Thanks, Rich |
#12
|
||||
|
||||
Have you tried repairing the Office installation (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair)?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
Paul, sorry for the delay. This worked! Thank you. Now to find out how to unlink one individual mergefield, is this possible? Because currently everything works, updates & unlinks except for that footer mergefield I have.
Thanks again, Rich |
#14
|
||||
|
||||
If the mergefield is still in the header after your code has run, that's because you're still not executing the merge.
You really do seem determined to do things your own way instead of following the advice given...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
Paul, last time I executed the merge, I was stuck with a nasty error that wouldn't go away. My process & code works (with the exception of the footer that doesn't get unlinked with the rest of the mergefields).
Considering I have these workbooks/documents in front of me, which are complex and difficult to explain sometimes, it may be most beneficial to try and find a solution for what I'm asking, although please do not get me wrong, I do appreciate your attempts to modify the entire process and give me a more straightforward approach. Fact is, I put a lot of time into this method and I don't have the time to re-start, re-test, and re-train individuals when a process is working effectively (compared to previous processes). All I need now is this footer to unlink so when my users send their Word Docs to PDFs or go to print the Doc, the footer doesn't change to a mergefield. Aside from that, the process is great and I'd like to continue using it. With all that being said, if you don't have a solution, that's fine and again, I appreciate your time & efforts. |
Tags |
excel vba, mail merge, word vba |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I mail merge records into table columns with more than one merge field? | tech123 | Mail Merge | 1 | 04-26-2017 07:13 PM |
Mail merge how to link mail merge field value to a column heading | dsummers | Mail Merge | 1 | 05-08-2014 02:59 PM |
Mail Merge a Listing grouped by specific field | KarenLS | Mail Merge | 1 | 11-11-2012 02:41 PM |
Mail Merge: cannot display the field in Excel correctly | derricklo1980 | Mail Merge | 5 | 08-16-2012 07:15 PM |
Mail Merge: cannot display the field in Excel correctly | derricklo1980 | Excel | 1 | 08-16-2012 12:51 AM |