Welcome to Tech Support Forum home to more then 136,000 problems solved. Issues have included: Spyware, Malware, Virus Issues, Windows, Microsoft, Linux, Networking, Security, Hardware, and Gaming Getting your problem solved is as easy as:
1. Registering for a free account
2. Asking your question
3. Receiving an answer

Registered members:
* Get free support
* Communicate privately with other members (PM).
* Removal of this message
* See fewer ads.
* And much more..

 



Want to know how to post a question? click here Having problems with spyware and pop-ups? First Steps
Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support
User Name
Password
Site Map Register Donate Rules Blogs Mark Forums Read


Microsoft Office support MS Office support forum

Reply
 
LinkBack Thread Tools
Old 06-23-2009, 05:18 AM   #1 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Mail Merge Format Problems

I'll try to explain this as best I can. The attached PDF shows what I want it to look like. I've managed to get the logic working using a MOD function to get the even/odd rows. But I can't seem to get the paragraph border/shading to work quite right. I've attached a PDF of what it's looking like. Note the extra blank shaded area as well as the extra CRs.
Attached Files
File Type: pdf Forms.pdf (39.3 KB, 7 views)
File Type: pdf WordDirectory1.pdf (19.7 KB, 6 views)
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Important Information
Join the #1 Tech Support Forum Today - It's Totally Free!

TechSupportForum.com is a leading support website for your computer needs. We offer free, friendly and personalized computer support. Why pay to have your computer fixed when you can do it for free.

Join TechSupportforum.com Today - Click Here

Old 06-23-2009, 05:19 AM   #2 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

I tried to post the code here but can't seem to get that to work so I've also attached a PDF of the document with the field codes turned on.
Attached Files
File Type: pdf SubHandReceipt_Merge.pdf (22.7 KB, 2 views)
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-25-2009, 05:36 AM   #3 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

Apparently noone has any ideas on this?
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-25-2009, 09:44 AM   #4 (permalink)
TSF Enthusiast
 
Join Date: Apr 2008
Location: Australia
Posts: 559
OS: Vista


Re: Mail Merge Format Problems

Hi ShosMeister,

What you're seeking help with requires fairly specialised knowledge, and I don't get to check every thread every day.

If you could post a copy of your actual mailmerge main document, I could probably fix the coding for you - fixing it from a PDF is rather difficult.
__________________
Cheers
macropod
(MS MVP -Word)
macropod is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-25-2009, 01:37 PM   #5 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

Can I attach a .doc here? didn't think so and I tried pasting the code in but the field codes wouldn't come over.
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-25-2009, 05:10 PM   #6 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

I'll try to attach it when I get to work tomorrow. And yes I do realize I'm well past the limits of a normal merge :) Thanks for offering to look at it.
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-26-2009, 02:05 AM   #7 (permalink)
TSF Enthusiast
 
Join Date: Apr 2008
Location: Australia
Posts: 559
OS: Vista


Re: Mail Merge Format Problems

Hi ShosMeister,

If you can't attach a Word doc, try zipping it first.
__________________
Cheers
macropod
(MS MVP -Word)
macropod is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-26-2009, 05:17 AM   #8 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

Here they are. Again, thanks for taking a look.
Attached Files
File Type: doc SubHandReceipt_MergeShadded.doc (42.5 KB, 4 views)
File Type: txt DPAS_SubHandReceipt.txt (51.9 KB, 2 views)
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-26-2009, 10:36 PM   #9 (permalink)
TSF Enthusiast
 
Join Date: Apr 2008
Location: Australia
Posts: 559
OS: Vista


Re: Mail Merge Format Problems

Hi ShosMeister,

Here's your modified document back.

A few of points:
1. I don't think you'll be able to get the mergefields in the header to work with a Directory merge. Accordingly, I've taken the material out of the header and put it into the document body.
2. I don't think you'll be able to get the record and total aggregation to for each group to display with a Directory merge - the values get reset before they're output. Accordingly, I've used a table structure with a hidden column to overcome that limitation.
3. To output the last group of records, you'll need to add a dummy last record to your data source. This will cause the true last group to be output.
4. Your table data don't seem to aligne correctly with the column headings. I'd suggest using paragraph breaks rather than manual line breaks for each line, so that each line can have its own tab alignments.

After running the merge, each goup will consist of a series of tables. You'll then need to run the following macro to join those tables and update the field that does the record count and value aggregation:
Code:
Sub TableJoiner()
Dim oPara As Paragraph
For Each oPara In ActiveDocument.Paragraphs
  With oPara.Range
    If .Information(wdWithInTable) = True Then
      With .Next
        If .Information(wdWithInTable) = False Then
          If .Text = vbCr Then .Delete
        End If
      End With
    End If
  End With
Next
ActiveDocument.Fields.Update
End Sub
If you want to explore how you can use Word's Catalogue/Directory Mailmerge facility (the terminolgy depends on the Word version), check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.
Attached Files
File Type: doc SubHandReceipt_MergeShadded.doc (42.0 KB, 3 views)
__________________
Cheers
macropod
(MS MVP -Word)

Last edited by macropod; 06-26-2009 at 10:41 PM.
macropod is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-28-2009, 05:15 AM   #10 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

MacroPod,

Thanks. I hadn't quite thought about using tables, but, that may work.

Problem though: You've setup the logic pretty much where I started and the issue is that other than the first record/group, every subsequent group drops the first record. If you look at the dataset, AJM should have 6, BJP should have 4 and the merge has 5 and 3 respectively.

As for the header, I never had a problem with that. If you notice in the PDF I attached, it works fine - although I just noticed I had one of the fields wrong. Probably because of the fact that those fields are exactly the same for every record and always will be.

The totals were working for me without any problems before. It was just the formatting and extra CR. Maybe incorporating your table idea into my old copy will help although I was trying to avoid using VB, but, I may have to.

BTW, is there a way to calculate the number of records so I know when I'm on the last record?

Thanks again and I'll be sure to look at the tutorial!!!
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-28-2009, 05:59 AM   #11 (permalink)
TSF Enthusiast
 
Join Date: Apr 2008
Location: Australia
Posts: 559
OS: Vista


Re: Mail Merge Format Problems

Hi ShosMeister,

OK, Here's an update that preserves the missing record from the 2nd & subsequent groups.

Regarding the headings & calculations, the 'SubHandReceipt_MergeShadded' document you posted didn't merge the heading data (the fields were converted to plain text representations of the mergefields), the record count remained at 1 and the totals at 0. The merged output also doesn't match the WordDirectory1.pdf attached to your post.

The header you're using in the 'SubHandReceipt_MergeShadded' document you posted can never represent more than one group's records. That's because the document has only 1 Section; even with the 'different first page' and 'differrent odd and even' page layout settings invoked, you can't have more than 3 apparent headers per Section in a mailmerge output. Unlike Letter merges, Directory merges put all their output into one Section.

If you have a version of the 'SubHandReceipt_MergeShadded' document that handles the calculations correctly, I'd be interested to see it. It would certainly help simplify things.
Attached Files
File Type: doc SubHandReceipt_MergeShadded.doc (48.0 KB, 1 views)
__________________
Cheers
macropod
(MS MVP -Word)

Last edited by macropod; 06-28-2009 at 06:02 AM.
macropod is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-28-2009, 06:24 AM   #12 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

Must have been the "really" broke file that I originally uploaded. Understand the limitation on the heading, but, every record has the exact same data so I think that "may" be okay. If not, it's easily moved as you have done. I do remember reading somewhere (but can't find it now) about forcing contiuous section breaks to combat that problem though.

I've attached the copy that I've had that does total the records and dollars properly along with the merged output file.
Attached Files
File Type: doc Directory50.doc (175.0 KB, 2 views)
File Type: doc SubHandReceipt_MergeShadded_Mine.doc (43.0 KB, 1 views)
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-28-2009, 06:43 PM   #13 (permalink)
TSF Enthusiast
 
Join Date: Apr 2008
Location: Australia
Posts: 559
OS: Vista


Re: Mail Merge Format Problems

Hi ShosMeister,

When I ran the mailmerge using your latest document in Word 2000, it still didn't update the counts or totals - both remained resolutely fixed at 0. The headings did update though. I did see that the counts and totals in your Directory50 file had updated. Conversely, With Word 2007, the counts and totals update but the headings don't. Curious.

Regarding the headings, you said "every record has the exact same data". However, when I look in the data file you posted, the field from which the heading data are sourced are different, depending on which group of records they belong to.

As for not wanting to use a macro, you could achieve the same results on the table output I produced without one. Basically, you'd use Find/Replace to delete all paragraphs in a 1-point font, followed by Ctrl-A then F9 to update the fields.

I'll do a bit more work on the calculation side of things to see if I can come up with something that works across all Word versions, independently of the table structure I've used.
__________________
Cheers
macropod
(MS MVP -Word)
macropod is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-28-2009, 07:18 PM   #14 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

Actually, while I appreciate the effort to make it compliant, not necessary as this is going to be used by a select group of people at work and they all have the same version - 2003.

That's also why I wanted it to be "automatic" for them as they are just users. I have absolutely no problem doing the extra work by hand, but, my bosses want it to be distributed to everyone and they aren't as familiar with Word let alone merge documents.

I'll take a look at the heading again as I may either have an incorrect field in there, or, I was given the wrong field to include. Thanks for catching that .... again :)

If I get stuck going to VBA, I'm wondering if it wouldn't be better/easier to do the entire thing in VBA? Never tried that though.
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-29-2009, 05:40 AM   #15 (permalink)
TSF Enthusiast
 
Join Date: Apr 2008
Location: Australia
Posts: 559
OS: Vista


Re: Mail Merge Format Problems

Hi ShosMeister,

Try the attached. It's based on the previous version I posted, plus tweaks to get the calculations working correctly without the need for the hidden column. What gets generated is a series of tables with a 1pt space separating them. IMHO the formatting look OK but, If you still don't like the gaps, the previous macro will remove them (you can delete the line 'ActiveDocument.Fields.Update') - the Find/Replace approach I mentioned before doesn't seem to work, though.

If you look at the data file, you'll see that it has a string of 0s as the new last row. You need something like this (or at least a 0 for the Sub_Custodian_Nbr field) to force the completion of the report for the last group of records.

As for cross-version compatability, I think you need to plan for whenever your organisation upgrades to the next Word version. The approach I've used should survive that.
Attached Files
File Type: zip SubHandReceipt_MergeShaded.zip (20.2 KB, 1 views)
__________________
Cheers
macropod
(MS MVP -Word)
macropod is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-30-2009, 05:19 PM   #16 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

I managed to get a macro working that will set a document variable = number of records. Then, within the merge, I check for MERGESEQ=that and if so, print the last total.

I had been working with your previous one and was trying basic find/replace but they don't seem to work. Probably because of the tables. I'll take a look at the jointable macro you provided and see if I can figure out how to make that run after the merge is done.

I figure since I have to have VBA in there, may as well have it do everything so I also have it prompting the user for the file and executing the SQL command to select * and do the sort.

The only problem I have not been able to lick yet is if a list for one person goes past 1 page. The second page has no page header. I figure once I have everything else working and formatted, I can just count the records per user and force a page break and header at that point. It's a cludge I know, but, hey is Word :)

Thanks again for the help!!!!
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-30-2009, 05:38 PM   #17 (permalink)
TSF Enthusiast
 
Join Date: Apr 2008
Location: Australia
Posts: 559
OS: Vista


Re: Mail Merge Format Problems

Hi ShosMeister,

If you're going to join the tables, then getting the header rows to appear on the 2nd & subsequent pages for a give group is as simple as applying the 'heading rows repeat' attribute to the header rows. This can be done in the mailmerge main document so that it doesn't have to be done afterwards.

You asked in a previous post whether it's possible for a mailmerge to tell how many records there are. Unfortunately there isn't - the process simply continues blindly until all the available records (or the user-selected range) have been processed. Accordingly, you need some other approach, such as the macro you're using.
__________________
Cheers
macropod
(MS MVP -Word)
macropod is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 06-30-2009, 08:06 PM   #18 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

I'm out of the office the rest of the week but I'll take a look at the table header idea when I get back next week. Thanks!
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 07-29-2009, 06:33 AM   #19 (permalink)
Registered User
 
Join Date: Jan 2008
Location: Harpers Ferry, WV
Posts: 400
OS: OS X 10.4.x


Re: Mail Merge Format Problems

Just to close this out:

Setting the repeating rows worked fine - sorry I didn't think of that myself. The code to start the process is simple:
Code:
Sub SetNumRecsAndMerge()
  Dim mm As Word.MailMerge
  Dim dlgOpen As FileDialog

  ActiveDocument.MailMerge.MainDocumentType = wdCatalog
  
  Set mm = ActiveDocument.MailMerge
  Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
    
  With dlgOpen
    .AllowMultiSelect = False
    .Filters.Add "Text Files", "*.txt"
    .Filters.Add "Comma Files", "*.csv"
    If .Show = -1 Then
      FileName = .SelectedItems.Item(1)
    End If
  End With
  
  mm.OpenDataSource _
      Name:=FileName, ConfirmConversions:=False, ReadOnly:=True, _
      LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
      PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", _
      Revert:=False, Format:=wdOpenFormatAuto, Connection:="", _
      SQLStatement:="SELECT * FROM DPAS_SubHandReceipt.txt ORDER BY Sub_Custodian_Nbr, Asset_Id", _
      SQLStatement1:="", SubType:=wdMergeSubTypeOther
  
  mm.DataSource.ActiveRecord = wdLastRecord

  If ActiveDocument.CustomDocumentProperties("NumRecords") > 0 Then
    ActiveDocument.CustomDocumentProperties("NumRecords").Value = _
                   mm.DataSource.ActiveRecord
  Else
    ActiveDocument.CustomDocumentProperties.Add Name:="NumRecords", _
                  Value:=mm.DataSource.ActiveRecord, _
                  Type:=msoPropertyTypeString, LinkToContent:=False
  End If
  
  mm.Execute
  Call TableJoiner
End Sub

Sub TableJoiner()
  Dim oPara As Paragraph
  Dim DelFlag As Boolean
  
  DelFlag = True
  Application.ScreenUpdating = False
  
  While (DelFlag = True)
    DelFlag = False
    For Each oPara In ActiveDocument.Paragraphs
      With oPara.Range
        If .Information(wdWithInTable) = True Then
          With .Next
            If .Information(wdWithInTable) = False Then
              If .Text = vbCr Then
                .Delete
                DelFlag = True
              End If
            End If
          End With
        End If
      End With
    Next
  Wend
  
  Application.ScreenUpdating = True
  ActiveDocument.Fields.Update
End Sub
and once it's finished, it automatically runs the TableJoiner to get rid of the extra CR/LFs in there.

Within the merge document, a simple check for
Code:
IF{MERGESEQ{={DOCPROPERTY NumRecords}}}
will determine if we have just printed the last record and can then print the final summary part.

Thanks again for all of the help!!!
ShosMeister is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Old 07-29-2009, 05:22 PM   #20 (permalink)
TSF Enthusiast
 
Join Date: Apr 2008
Location: Australia
Posts: 559
OS: Vista


Re: Mail Merge Format Problems

Hi ShosMeister,

This has been a useful exercise for me too. I might incorporate some of what we've worked through here into my tutorial.
__________________
Cheers
macropod
(MS MVP -Word)
macropod is offline  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Reddit!
Reply With Quote
Reply


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off




All times are GMT -7. The time now is 06:13 AM.



Copyright 2001 - 2009, Tech Support Forum
Home Tips Plus | Outdoor Basecamp | Automotive Support Forum

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85