#1
|
|||
|
|||
2 Minor Mail Merge Problems
I am having two minor but annoying issues with my mail merge document.
My document is a table, with merge fields occupying the cells (ie a fornm). In one of my table cells of the mail merge document the field is left justified (text starts at the left edge of the cell). But in the merged document the text in that cell is centred. Thoughts? And my second problem, in my footer, I have a string of "Page # of ##s" Page { PAGE } of { NUMPAGES }. In preview, it displays as 1 of 1. In the merged document of 3 pages, each page shows "Page 1 of 3", instead of "Pages 1 of 3; Pages 2 of 3; Pages 3 of 3" Good chance I haven't shared all the nitty gritty information one needs to help solve the issues, and for that I apologize. I'm not all that savvy with mail merges and stuggle with the terminology which makes explaining to the experienced difficult. Please ask me the information yuou need if you feel an urge to help. |
#2
|
||||
|
||||
The first issue suggests you've tried to override the alignment of a Style that is centred.
With the second issue, are you referring to the mailmerge preview, or a print preview?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thank you Paul for your prompt reply!
Quote:
Quote:
I hope this helps. This is one of many similar documents you worked extensively on with me in the past for which I am indebted to you for. I'm back cleaning up some cosmetic things now that the functionality seems to be in order. |
#4
|
||||
|
||||
Styles are at the heart of how Word works; it's impossible not to use them, though they can be misused. See: http://www.addbalance.com/usersguide...s.htm#Overview
How many pages are there in your mailmerge main document?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hey Paul, mail merge main document is a single page.
As I mentioned, I have several very similar reports but it is only this report in particular I am having the left justification issue, and if I recall, the page numbering. :-( |
#6
|
||||
|
||||
For the page numbering, if you want the numbers to increment, change the PAGE field to a SECTION field.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Thank you Paul, I will try that.
I'm not trying to be difficult, I'm just trying to learn. I have 11 other, as I said, very similar reports. I use with success in all the other reports {PAGE} of {NUMPAGES} with success. I don't know what is so different with this report. Could this style issue, which I'm still working on understanding, be a result of different Office Version between creation and use today? Again, the other reports created at the same time with the same software don't demonstrate the same behaviour. |
#8
|
||||
|
||||
Neither the numbering issue nor the Style issue has anything to do with the Office version. The numbering issue probably has something to do with the header/footer setup.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Cannot retain formatting in mailmerge document
Ok. Reworking the problem report.
|
#10
|
||||
|
||||
If you attach a copy of the problem mailmerge main document to a post (delete anything sensitive), I'll take a look at it. You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Thanks Paul. I think there was something just not right with that document so I reworked it. I copied the unique components of that report and pasted it to a working report that was basically similar to create anew document. I've abandoned the one giving me all the trouble.
I think something got mixed up in the original construction of it and was just too frustrating to find the cause, although I really do appreciate your willingness to investigate. I imagine once in your hands you'd have the problem identified and isolated within seconds. |
#12
|
|||
|
|||
Quote:
|
#13
|
||||
|
||||
It's not a mergefield. Open the footer and select the field that generates the page #s. Press Shift-F9. You should see:
{PAGE} Change 'PAGE' to 'SECTION'. Press F9. Done.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Found some code
Thanks Paul, i didn't realize it was a simple as just typing it in. I know for {PAGE} and {NUMPAGES} I hadn't typed them but rather selected them from a list somewhere and they were inserted in the document.
I realize you are leaning toward a problematic footer (based on the limited familiarization you have with the report and data etc) , and I'm skepticale because I have copied and pasted the same footer between 7 other similar reports, that draw from the same data, and present similarly (just a different set of data) and they populate just as expected with {PAGE} and {NUMPAGES}. As I was fixing some bugs in my Excel application (which I've been developing now for 3 years) I came across this. I'm wondering if this is the culprit as it references the report's headers and footers. In post #10 you were kind enough to offer to take a look at the report. Maybe if the offer stands I can take you up on that offer since I think there is enough questions around it that it might be more worth your time. (stand by for the file ... it is not with me right now) [s]Problem 1 - {Page} of {NumPages} as discussed in this thread.[/s] - {SECTION} worked noiw that I knew how to apply it. Problem 2 -see this post File added here [s]Problem 3 - IWhen I print these reports there is always an empty page (but still with header/hooter) printed.[/s] - Solved I think. Code:
Sub merge2(ByVal i As Long, ByVal rpt_od As String, objWord As Object, ByVal dest As Long) Dim oDoc As Object, oDoc2 As Object Dim StrSQL As String, fName As String, StrSrc As String, strFilename As String, myPath As String Dim qfile As String, st_srchfn As String, wb_qfile2 As Workbook, itype As String, isubresp As String 'Dim wb_qfile2 As Workbook Dim HdFt As Variant Const wdSendtToNewDocument = 0 Const wdSendToPrinter = 1 Const wdFormLetters = 0 Const wdDirectory = 3 Const wdMergeSubTypeAccess = 1 Const wdOpenFormatAuto = 0 work_fn = ws_vh.Range("N2") Set wb_nwb = Workbooks(work_fn) 'create workorders folder myPath = "u:\PWS\Parks\Parks Operations\Sports\Sports17\WORKORDERS\" & format(ws_vh.Range("B17"), "ddd dd-mmm-yy") If Dir(myPath, vbDirectory) = "" Then 'if not already created ... MkDir myPath End If 'close data file st_srchfn = "u:\PWS\Parks\Parks Operations\Sports\Sports17\DATA\" & ws_vh.Range("N2") If wb_nwb Is Nothing Then MsgBox wb_nwb & " is NOT open." Else wb_nwb.Close True 'saves data workbook after TYPE was updated for GS End If itype = Right(ws_th.Range("A" & i), 2) isubresp = Left(ws_th.Range("A" & i), 3) If itype = "DR" Then fName = "u:\PWS\Parks\Parks Operations\Sports\Sports17\REPORTS\v1\DR15v1.docx" ElseIf itype = "DT" Then fName = "u:\PWS\Parks\Parks Operations\Sports\Sports17\REPORTS\v1\DT15v1.docx" ElseIf itype = "FR" Then fName = "u:\PWS\Parks\Parks Operations\Sports\Sports17\REPORTS\v1\FR15v1.docx" ElseIf itype = "FT" Then fName = "u:\PWS\Parks\Parks Operations\Sports\Sports17\REPORTS\v1\FT15v1.docx" ElseIf itype = "CR" Then fName = "u:\PWS\Parks\Parks Operations\Sports\Sports17\REPORTS\v1\CR15v1.docx" ElseIf itype = "CT" Then fName = "u:\PWS\Parks\Parks Operations\Sports\Sports17\REPORTS\v1\CT15v1.docx" ElseIf itype = "GS" Then If isubresp = "HPE" Or isubresp = "HPL" Then fName = "u:\PWS\Parks\Parks Operations\Sports\Sports17\REPORTS\v1\GS15v1_GSH.docx" 'Passive : Hillside Else fName = "u:\PWS\Parks\Parks Operations\Sports\Sports17\REPORTS\v1\GS15v1_GS.docx" 'Passive : Wloo Park End If Else fName = "U:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\v1\GS15v1_GM.docx" End If StrSrc = "u:\PWS\Parks\Parks Operations\Sports\Sports17\DATA\" & ws_vh.Range("N2") StrSQL = "SELECT * FROM [DATA$] WHERE [TYPE]='" & itype & "' AND [SIG_CREW]='" & isubresp & "' " & _ "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC" Set objWord = CreateObject("Word.Application") With objWord .DisplayAlerts = False .Visible = True Set oDoc = .Documents.Open(Filename:=fName, ConfirmConversions:=False, _ ReadOnly:=True, AddToRecentFiles:=False, Visible:=True) With oDoc With .MailMerge .MainDocumentType = wdFormLetters .Destination = wdSendtToNewDocument .SuppressBlankLines = True .OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _ ReadOnly:=True, format:=wdOpenFormatAuto, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "User ID=Admin;Data Source=" & StrSrc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _ SQLStatement:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess .Execute Pause:=False End With .Close False End With .DisplayAlerts = True 'page break routine only for sports reports If (Left(itype, 1) <> "G") And (itype <> "DT") Then 'exclude GS reports With .activedocument If .Sections.count > 1 Then For Each HdFt In .Sections(.Sections.count).Headers If HdFt.Exists Then HdFt.Range.FormattedText = .Sections(1).Headers(HdFt.index).Range.FormattedText HdFt.Range.Characters.Last.Delete End If Next For Each HdFt In .Sections(.Sections.count).Footers If HdFt.Exists Then HdFt.Range.FormattedText = .Sections(1).Footers(HdFt.index).Range.FormattedText HdFt.Range.Characters.Last.Delete End If Next End If Do While .Sections.count > 1 .Sections(1).Range.Characters.Last.Delete DoEvents Loop .Range.Characters.Last.Delete End With End If End With Set oDoc2 = objWord.activedocument 'save newly created document With oDoc2 myPath = "u:\PWS\Parks\Parks Operations\Sports\Sports17\WORKORDERS\" & format(ws_vh.Range("B17"), "ddd dd-mmm-yy") .SaveAs myPath & "\" & rpt_od & ".docx" If dest = 2 Then .PrintOut End If '.Close End With Set oDoc = Nothing: Set oDoc2 = Nothing ': Set objWord = Nothing End Sub Could this be contributing to the unique problem associated with this report (itype = DT)? Last edited by JennEx; 06-01-2019 at 08:54 AM. Reason: Solved issues. |
#15
|
||||
|
||||
Switching from the PAGE field to the SECTION field is just a simple way of dealing with single-page reports in a multi-Section mailmerge output where you want the X of Y numbering. The other way is to ensure new Sections are created with the 'same as previous property. The blue code in your post has no effect on that, however, since it actually affects everything except a 'G' or 'DT' report by ensuring those other reports all have the same header & footer content as the first Section.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge Problems | jmac12 | Outlook | 2 | 07-04-2018 01:14 PM |
Mail merge problems | weamish | Mail Merge | 30 | 12-26-2014 10:41 PM |
Mail merge how to link mail merge field value to a column heading | dsummers | Mail Merge | 1 | 05-08-2014 02:59 PM |
Problems with mail merge | Joanne | Mail Merge | 1 | 09-29-2013 07:24 PM |
Merge to e-mail: two problems | MartinD_UK | Mail Merge | 15 | 06-19-2012 11:23 PM |