![]() |
![]() |
![]() |
|||||
![]() |
![]() |
![]() |
![]() |
![]() |
|||
| 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: * 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 |
|
|||||||
| Microsoft Office support MS Office support forum |
![]() |
|
|
LinkBack | Thread Tools |
|
|
#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.
|
|
|
|
| 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 |
|
|
#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) |
|
|
|
|
|
#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.
|
|
|
|
|
|
#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
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.
__________________
Cheers macropod (MS MVP -Word) Last edited by macropod; 06-26-2009 at 10:41 PM. |
|
|
|
|
|
#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!!! |
|
|
|
|
|
#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.
__________________
Cheers macropod (MS MVP -Word) Last edited by macropod; 06-28-2009 at 06:02 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. |
|
|
|
|
|
#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) |
|
|
|
|
|
#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. |
|
|
|
|
|
#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.
__________________
Cheers macropod (MS MVP -Word) |
|
|
|
|
|
#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!!!! |
|
|
|
|
|
#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) |
|
|
|
|
|
#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
Within the merge document, a simple check for Code:
IF{MERGESEQ{={DOCPROPERTY NumRecords}}}
Thanks again for all of the help!!! |
|
|
|
|
|
#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) |
|
|
|
![]() |
| Thread Tools | |
|
|