#1
|
|||
|
|||
Significant Blank Space At Top Of Page When Records Merged 1 Per Page
Dealing now with what I hope is the last problem with this mail merge document.
I wish to have one record per page. It is doing this however, each page has a significant blank space preceding the actual text. With each new page, the empty space appears larger. I'm unsure as to why I all of a sudden have this extra space. Thoughts? |
#2
|
||||
|
||||
Do you realise you have a field coded as:
{ if{ =mod({ mergeseq },1) \# 0}= 0 { quote 12 }} after your table and before all those unnecessary paragraph breaks? What that field does is to insert a manual page break before those unnecessary paragraph breaks - and they precede the Section break.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks Paul. It took me a bit to digest the terminology (Word and word processing are not strong suits), but simply removing the unnecessary page breaks after that field took care of most of the problem. I didn't realize they were there. I knew that the manual page break field was there. I hope that is what you were guiding me towards. But I'm left now with my reports having a trailing blank page.
|
#4
|
||||
|
||||
That's the result of changing to a letter merge. The field coded as:
{ if{ =mod({ mergeseq },1) \# 0}= 0 { quote 12 }} was used when you were doing a directory/catalog merge with two reports per page.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
So, it's unavoidable? If again I understand correctly, with this document where unlike my other documents I only put one report per page, I could get away with removing this field? But doing so now tries to squeeze multiple reports on a page and they end up broken, now.
And (thinking this should maybe be a new post) I have a field in the footer of all my mail merge documents ... Page {PAGE} of {NUMPAGES} . It doesn't want to populate. I've deleted it, and rebuilt it, copied it to text then again converted to fields with the same results. Thoughts? |
#6
|
||||
|
||||
Presumably you still have an empty last record in the data source for the purposes of the directory/catalog merges. In that case, the empty last page is unavoidable unless you specify the range of records to merge via the Finish & Merge dialogue.
Quote:
Conversely, with a directory/catalog merge, one could ensure there was only a page/Section break between records. If you change back to a directory/catalog merge and edit the field code in which the table is embedded by inserting: {IF{MERGESEQ}> 1 {QUOTE 12}} after: { if{ MERGEFIELD "trn_ui1" }> 4 " thus: { if{ MERGEFIELD "trn_ui1" }> 4 "{IF{MERGESEQ}> 1 {QUOTE 12}} you'll get page breaks between records and no empty page at the end. It works for me, regardless of whether one uses a directory/catalog merge or a letter merge.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
It still doesn't look good Paul. I did add the extra field as you suggested, although I'm unsure if maybe I missed an important step " change back to a directory/catalog merge". I can admit to not knowing how to intentionally do this. I only have this problem with this report.
And I'm stumped as to why I am unable to get the 'Page of Pages' fields to not do anything in any report. There doesn't appear to be anything complicated about them. My other merge fields work in the footer. I don't even think I can blame the data for this. And it works for you. |
#8
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Thanks Paul,
When I apply my datasource to your revised version of my report ... I have no more blank pages and the Page of Pages fields populate. But, the times are all decimals. This report was a result of merging directly from Word. Feel free to have a look at this document by downloading it from here. Interestingly, when I execute this merge by way of my Excel VBA application, I get different results. I get multiple records per page but the Page of Pages does populate. This document can be downloaded here. I am leaning towards Excel being the problem? As I look closer at the code, I do see reference to page breaks.... For what it's worth, here is the code from Excel that is applying the merge ... 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" 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 |
#10
|
||||
|
||||
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Wow. This is blowing my mind. I somehow feel that we've gone in a complete circle. LOL I have learned though, not to rely on the results of any mail merge testing unless such tests are done through the macro. I had no idea that the results would be different between merging one way over the other. I'm also suspect that I am getting different results between different computers which may be a source of some frustration.
Quote:
The mailmerge document created from the macro is all perfect except for the page breaks (the datasource is open). I don't need to worry about the decimal times as I'm only getting that with testing outside the macro. The 'Page of Pages' is populating well thank to your help, but I've given up on my other reports. |
#12
|
||||
|
||||
Yep, that's the one!
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
I am really confused Paul.
You said in post #10 "The only way I can see the times as decimals happening is if the workbook is open - which it must be when you use the macro". My last post was based on running the merge (the one you provided me) through the macro. The data workbook was open. I did not get decimals. The report was great except for having not having reports on individual pages. I got the decimals when I wasn't running through the macro, which isn't how this merge is meant to work anyway. So, your idea of reusing the original field code is to get rid of the decimals correct? Do I really need to do this as The times aren't the issue. And I would have to change all my times, and you know there are a lot represented in my report. I'm also going to get myself in a bind dealing with the effects of this change to this issue in this thread: https://www.msofficeforums.com/mail-...me-fields.html I'm sorry for this being so complicated. But going back to what I had originally would take me back to the original problem from post 1. |
#14
|
||||
|
||||
I'm not entirely sure what's going on here.
When I use a macro (not yours) to execute the merge with the document I attached to post #8, the decimal times appear. That's because the macro I'm using is in the workbook used as the data source. The decimal times don't appear when I do the merge manually with the same mailmerge main document without the workbook being open, but do appear when it is open. Having studied your macro more closely, it seems part of the explanation for the difference in our experiences is that your macro seems to be running the merge from a different workbook than the one containing the data source. Furthermore, it closes the mailmerge source workbook if it's open. Hence, the mailmerge source workbook is not actually open when the merge is done and you wouldn't get the decimals in that scenario. The upshot of this is that you don't need to revert to the field coding described in https://www.msofficeforums.com/mail-...tml#post114362 for the macro process you're currently using. That still doesn't explain why you'd get the decimals when merging directly from Word (per post #9) - unless the mailmerge source workbook was open. As for the page breaks, Your code has a special process for anything other than 'GS reports' that deletes all the Section breaks. If you want to keep those breaks, delete or comment-out: Code:
Do While .Sections.Count > 1 .Sections(1).Range.Characters.Last.Delete DoEvents Loop .Range.Characters.Last.Delete
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
||||
|
||||
If you don't I can say for certian I have NO idea ....
Quote:
Quote:
Quote:
Quote:
If you refer to the document created by the macro launched merge in post 9 (https://drive.google.com/file/d/0B9E...ew?usp=sharing) with the file you provided, these merge fields populate although I have more than one record per page. So it can work. It isn't now, but it has. There must be a combination of factors present in one query that doesn't exist in another. As I said, all my records experiences this "bug". |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Blank space at top of page | Vecchia | Word | 4 | 12-26-2014 07:44 AM |
Removing space before blank page / page break | greasel | Word | 5 | 10-17-2014 07:04 PM |
Insert page break vs blank page | harvey4 | Word | 2 | 08-20-2014 07:15 PM |
Multiple records on the same page | Hugh | Mail Merge | 2 | 04-04-2014 05:24 PM |
First section will not 'insert' blank page even though sectn 2 is 'odd' page break | Pat_Hodgson | Word | 6 | 12-01-2013 04:22 PM |