Solved

Word Merge with Repeating Rows - I am close

Posted on 2014-09-23
5
236 Views
Last Modified: 2014-09-28
I have done lots of research on this and am close but there is something I don't understand on how Merge processes the data. It is a classic example. I have a spreadsheet that has multiple rows per person. Each row has some repeating fields (name address etc) and then some unique fields (a CODE, STATUS, and NOTE). I want to create a merge letter that lists all of a persons CODEs+Status+Notes on a single page.

So turn this:
12345 Steve ABC  OPEN  Steve has an open ABC item
12345 Steve XYZ   CLOSED Steve has a closed XYZ item

Into this:

Dear Steve;

Here is some info for you.

ABC   OPEN      Steve has an open ABC Item
XYZ    CLOSED  Steve has a closed xyz item

Sincerely,
Your friend

Using the below code I am able to get the merge to only print the repeating data on the first page, but it still jumps to a new page for each of the repeating items (ABC...   XYZ... each on a separate page).

Here is the code - - thanks very much for any help you can provide. I have included the results of the merge also.

{ IF { MERGESEQ } = "1" "{MERGEFIELD ID }" " " }{SET Place1 { MERGEFIELD ID }}
{Place1}
{Place2}
{ IF { Place2} <> {Place1} "{MERGEFIELD ID } { MERGEFIELD CODE } { MERGEFIELD STATUS } { MERGEFIELD NOTE }"  "{ MERGEFIELD CODE } { MERGEFIELD STATUS } { MERGEFIELD NOTE }"}{SET Place2 {MERGEFIELD ID}}
0
Comment
Question by:COBOLforever
  • 4
5 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40341984
I think you would do better using Automation code to do the merge.  Your project seems to require two linked tables, one for person records, one for status records, with (possibly) multiple records per person.  This is similar to the creation of a Word Northwind invoice, covered in my old Access Archon #44 (Merging to Word from Linked Access Tables), updated in my Working with Word ebook.  The code uses doc properties to write the main record's data to the Word document, and TypeText to fill in a table with item details, as many as are needed per invoice.  Here is a link for the article:

http://www.helenfeddema.com/Files/accarch44.zip
0
 

Author Comment

by:COBOLforever
ID: 40342558
Thanks Helen - -that is a nice piece of work, but I know it is much easier than that now. If I were to try your method, I have a Memo field - -how does that fit into your scenario?

Thanks much,
-Jason
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40344889
For a Memo field, you could try using a bookmark and pasting the contents of the Memo field to the bookmark.  DocProperty fields are too small for that.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40344905
My Working with Word ebook (3rd edition coming out soon) has an example of writing data to Word using bookmarks.  The example just copies a block of text from an unbound textbox to write to the bookmark, but it would most likely work with text from a Memo field too.  Here is the code that writes data from the textbox to various bookmarks in a Word document:

      'Open a new letter based on the selected template
      Set doc = appWord.Documents.Add(strTemplateNameAndPath)
      
      'Write information to Word bookmarks
      strBookmark = "Name"
      Call UpdateBookmark(doc, strBookmark, strContactNameAndJob)
      
      strBookmark = "Address"
      Call UpdateBookmark(doc, strBookmark, strAddress)
      
      strBookmark = "CompanyName"
      Call UpdateBookmark(doc, strBookmark, strCompanyName)
      
      strBookmark = "Salutation"
      Call UpdateBookmark(doc, strBookmark, strSalutation)
               
      strBookmark = "TodayDate"
      Call UpdateBookmark(doc, strBookmark, strLongDate)
      
      strBookmark = "EnvelopeName"
      Call UpdateBookmark(doc, strBookmark, strContactNameAndJob)
      
      strBookmark = "EnvelopeCompany"
      Call UpdateBookmark(doc, strBookmark, strCompanyName)
         
      strBookmark = "EnvelopeAddress"
      Call UpdateBookmark(doc, strBookmark, strAddress)
      
      If strTemplateName = "Contact Letter BM.dotx" Then
         strBookmark = "LetterBody"
         Call UpdateBookmark(doc, strBookmark, Me![txtLetterText].Value)
      End If
      

Open in new window

The variables are set from listbox columns, except for strBookmark.  The UpdateBookmark procedure is listed below:

Public Sub UpdateBookmark(doc As Word.Document, _
   strBookmark As String, strText As String)
'Created by Helen Feddema 6-Jun-2014
'Last modified by Helen Feddema 6-Jun-2014

On Error GoTo ErrorHandler

   Dim rngBM As Range
   
   If doc.Bookmarks.Exists(strBookmark) = True Then
      Set rngBM = doc.Bookmarks(strBookmark).Range
      rngBM.Text = strText
      doc.Bookmarks.Add strBookmark, rngBM
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in UpdateBookmark procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
   
 End Sub

Open in new window

0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40344911
Or, if the Word document has tables, the Memo field data could be written to a specific cell in a table, as in the Northwind Invoice code.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now