Significant Blank Space At Top Of Page When Records Merged 1 Per Page

JennEx · #1 04-29-2017, 05:43 PM

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?

macropod · #2 04-29-2017, 08:56 PM

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.

JennEx · #3 04-30-2017, 05:40 AM

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.

macropod · #4 04-30-2017, 06:09 AM

Quote:

Originally Posted by JennEx

But I'm left now with my reports having a trailing blank page.

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.

JennEx · #5 04-30-2017, 07:17 AM

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?

macropod · #6 04-30-2017, 04:16 PM

Quote:

Originally Posted by JennEx

So, it's unavoidable?

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:

Originally Posted by JennEx

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.

The only way I can see that happening is if you also changed the merge type back from a letter merge to a directory/catalog merge. The downside to that, of course, is that you then can't have mergefields in the header/footer.

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.

Quote:

Originally Posted by JennEx

I have a field in the footer of all my mail merge documents ... Page {PAGE} of {NUMPAGES} . It doesn't want to populate.

It works for me, regardless of whether one uses a directory/catalog merge or a letter merge.

JennEx · #7 04-30-2017, 05:25 PM

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.

macropod · #8 04-30-2017, 07:14 PM

Quote:

Originally Posted by JennEx

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.

You'd only do either of those things if you did both! And, as I said:

Quote:

The downside to that, of course, is that you then can't have mergefields in the header/footer.

Try the attached. I've reviewed & revised some of your field coding. You will need to reconnect to your own data source.

JennEx · #9 05-01-2017, 10:30 AM

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

Hopefully having posted external links to my files are OK. I do need to maintain control over their access, and this is the only way I can see doing it.

macropod · #10 05-01-2017, 09:26 PM

Quote:

Originally Posted by JennEx

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

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, but needn't be for merging direct from the mailmerge main document. And, if the workbook is open, you need to revert to the kind of field construction used to convert the decimals to hh:mm referred to in: https://www.msofficeforums.com/mail-...tml#post114362 (minus the \* MERGEFORMAT switches)

JennEx · #11 05-02-2017, 05:06 AM

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:

Originally Posted by macropod

..., you need to revert to the kind of field construction used to convert the decimals to hh:mm referred to in: https://www.msofficeforums.com/mail-...tml#post114362 (minus the \* MERGEFORMAT switches)

Are you referring to the original code I had in my original report in post #1 of that thread?

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.

macropod · #12 05-02-2017, 04:02 PM

Quote:

Originally Posted by JennEx

Are you referring to the original code I had in my original report in post #1 of that thread?

Yep, that's the one!

JennEx · #13 05-02-2017, 07:31 PM

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.

macropod · #14 05-02-2017, 08:09 PM

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

JennEx · #15 05-03-2017, 03:41 PM

Quote:

Originally Posted by macropod

I'm not entirely sure what's going on here.

If you don't I can say for certian I have NO idea ....

Quote:

Originally Posted by macropod

... is that your macro seems to be running the merge from a different workbook than the one containing the data source.

Correct ... my 'application' workbook holds the code. It relies on a workbook serving as a database as each date has it's own. The application can call on any date to pull reports from.

Quote:

Originally Posted by macropod

Furthermore, it closes the mailmerge source workbook if it's open.

I did not know that. When I go to my application worksheet, I can access the data workbook ... but ... it's hidden. If I unhide it, I have access to it as it is open. (?) Could the fact the data workbook is hidden play a role?

Quote:

Originally Posted by macropod

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.

Yay!

Quote:

Originally Posted by macropod

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

Done! No blank pages, times are all correct, formats are correct. I would call that success. But, the 'Page of Pages' still refuses to populate. This is what I find to be most mysterious.
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