Microsoft Office Forums Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-01-2018, 07:39 AM
RMerckling RMerckling is offline Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
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
  #2  
Old 05-05-2018, 03:01 AM
macropod's Avatar
macropod macropod is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-07-2018, 06:38 AM
RMerckling RMerckling is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 05-07-2018, 02:18 PM
macropod's Avatar
macropod macropod is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #5  
Old 05-08-2018, 02:01 PM
RMerckling RMerckling is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 05-08-2018, 06:41 PM
RMerckling RMerckling is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 05-08-2018, 08:41 PM
macropod's Avatar
macropod macropod is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by RMerckling View Post
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?
No, that's not executing - all it does is get you to the mailmerge preview stage.
Quote:
Originally Posted by RMerckling View Post
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?
Above:
appWD.Visible = True
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 05-09-2018, 05:17 AM
RMerckling RMerckling is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 05-09-2018, 05:19 AM
macropod's Avatar
macropod macropod is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #10  
Old 05-09-2018, 05:33 AM
RMerckling RMerckling is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default

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?
Reply With Quote
  #11  
Old 05-09-2018, 09:33 AM
RMerckling RMerckling is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 05-09-2018, 02:32 PM
macropod's Avatar
macropod macropod is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Have you tried repairing the Office installation (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair)?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 05-17-2018, 08:48 AM
RMerckling RMerckling is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default

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
Reply With Quote
  #14  
Old 05-17-2018, 02:33 PM
macropod's Avatar
macropod macropod is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #15  
Old 05-17-2018, 03:08 PM
RMerckling RMerckling is offline Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Windows 7 64bit Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Office 2010 32bit
Novice
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA
 
Join Date: Jan 2018
Posts: 29
RMerckling is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
excel vba, mail merge, word vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA 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
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Mail Merge a Listing grouped by specific field KarenLS Mail Merge 1 11-11-2012 02:41 PM
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Mail Merge: cannot display the field in Excel correctly derricklo1980 Mail Merge 5 08-16-2012 07:15 PM
Update &amp; Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA Mail Merge: cannot display the field in Excel correctly derricklo1980 Excel 1 08-16-2012 12:51 AM


All times are GMT -7. The time now is 06:41 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft