Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-23-2019, 07:41 PM
JennEx JennEx is offline 2 Minor Mail Merge Problems Windows XP 2 Minor Mail Merge Problems Office 2013
Competent Performer
2 Minor Mail Merge Problems
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 05-23-2019, 09:56 PM
macropod's Avatar
macropod macropod is offline 2 Minor Mail Merge Problems Windows 7 64bit 2 Minor Mail Merge Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 05-24-2019, 03:19 AM
JennEx JennEx is offline 2 Minor Mail Merge Problems Windows XP 2 Minor Mail Merge Problems Office 2013
Competent Performer
2 Minor Mail Merge Problems
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Thank you Paul for your prompt reply!

Quote:
The first issue suggests you've tried to override the alignment of a Style that is centred.
I can't deny that because I don't know really what that is (Style etc) and if it was changed it was certianly unitentional and done without knowing it was being done. How do I check, and how can I resolve it?

Quote:
With the second issue, are you referring to the mailmerge preview, or a print preview?
The print preview and the actual printed document exhibit that behaviour of page numbering. The mailmerge preview shows as "Page 1 of 1".

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.
Reply With Quote
  #4  
Old 05-24-2019, 04:46 AM
macropod's Avatar
macropod macropod is offline 2 Minor Mail Merge Problems Windows 7 64bit 2 Minor Mail Merge Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 05-24-2019, 05:53 AM
JennEx JennEx is offline 2 Minor Mail Merge Problems Windows XP 2 Minor Mail Merge Problems Office 2013
Competent Performer
2 Minor Mail Merge Problems
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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. :-(
Reply With Quote
  #6  
Old 05-24-2019, 03:58 PM
macropod's Avatar
macropod macropod is offline 2 Minor Mail Merge Problems Windows 7 64bit 2 Minor Mail Merge Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 05-24-2019, 04:58 PM
JennEx JennEx is offline 2 Minor Mail Merge Problems Windows XP 2 Minor Mail Merge Problems Office 2013
Competent Performer
2 Minor Mail Merge Problems
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 05-24-2019, 05:01 PM
macropod's Avatar
macropod macropod is offline 2 Minor Mail Merge Problems Windows 7 64bit 2 Minor Mail Merge Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #9  
Old 05-29-2019, 04:44 PM
JennEx JennEx is offline 2 Minor Mail Merge Problems Windows XP 2 Minor Mail Merge Problems Office 2013
Competent Performer
2 Minor Mail Merge Problems
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default Cannot retain formatting in mailmerge document

Ok. Reworking the problem report.
Reply With Quote
  #10  
Old 05-29-2019, 04:51 PM
macropod's Avatar
macropod macropod is offline 2 Minor Mail Merge Problems Windows 7 64bit 2 Minor Mail Merge Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #11  
Old 05-29-2019, 05:01 PM
JennEx JennEx is offline 2 Minor Mail Merge Problems Windows XP 2 Minor Mail Merge Problems Office 2013
Competent Performer
2 Minor Mail Merge Problems
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 05-31-2019, 10:12 AM
JennEx JennEx is offline 2 Minor Mail Merge Problems Windows XP 2 Minor Mail Merge Problems Office 2013
Competent Performer
2 Minor Mail Merge Problems
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Quote:
For the page numbering, if you want the numbers to increment, change the PAGE field to a SECTION field.
Hi Paul, I'm still isolating the page number of numbers problem. I'm having in my footer. You had suggested swapping out the {PAGE} field with {SECTION}. I am unable to figure out how to insert this field. I am unable to find any list of merge fields to install other than my database ones.
Reply With Quote
  #13  
Old 05-31-2019, 03:16 PM
macropod's Avatar
macropod macropod is offline 2 Minor Mail Merge Problems Windows 7 64bit 2 Minor Mail Merge Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #14  
Old 06-01-2019, 05:32 AM
JennEx JennEx is offline 2 Minor Mail Merge Problems Windows XP 2 Minor Mail Merge Problems Office 2013
Competent Performer
2 Minor Mail Merge Problems
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default 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
This is code that I believe you helped me with earlier designed (if I understand correctly) to ensure only one record per page, unlike the other reports that fit as many records on a page as will fit.


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.
Reply With Quote
  #15  
Old 06-01-2019, 03:16 PM
macropod's Avatar
macropod macropod is offline 2 Minor Mail Merge Problems Windows 7 64bit 2 Minor Mail Merge Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

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
2 Minor Mail Merge Problems Problems with mail merge Joanne Mail Merge 1 09-29-2013 07:24 PM
2 Minor Mail Merge Problems Merge to e-mail: two problems MartinD_UK Mail Merge 15 06-19-2012 11:23 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:47 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft