what code sets table cell inner margins

amenu · #1 03-29-2018, 02:18 PM

Paul, I assure you I have been paying attention.

I thought the person I am trying to help would have to invoke the mail merge wizard, start with selecting the label template, and go though the following steps to complete the mail merge. I did not realize she could open a Word document with a pre-formatted table with all the merge fields entered. I have created such a document. She will have to click on Yes and then Finish and Merge to produce the printable labels.

To ensure her acceptance of a process that she has never used I wanted to come up with a one-click solution by recording a macro.

It turns out that the recorded macro does not work when run in an empty document. It produces one merged label per page instead of the 30 labels per page that the labels template calls for.

I would be interested in learning why. I have recorded macros that work fine and are most useful, so this puzzles me. Perhaps you or someone else who has been following this thread can offer insight. I have copied the code below.

Thank you for your patience with a novice.-- Art

Code:

Sub Macro3()
'
' Macro3 Macro
'
'
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "E:\Data backup 24 Nov 2017\DATA\Centennial Treasurer\Centennial contacts.xlsm" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=E:\Data backup 24 Nov 2017\DATA\Centennial Treasurer\Centennial contacts.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Eng" _
        , SQLStatement:="SELECT * FROM `MailingContacts$`", SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
        , Text:="""MAILING_ADDRESS"""
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub

macropod · #2 03-29-2018, 02:30 PM

Quote:

Originally Posted by amenu

To ensure her acceptance of a process that she has never used I wanted to come up with a one-click solution by recording a macro.

It turns out that the recorded macro does not work when run in an empty document. It produces one merged label per page instead of the 30 labels per page that the labels template calls for.

I would be interested in learning why.

At best, a macro will save just two mouse clicks in this case - one to answer 'Yes' to the SQL prompt, the other to choose the destination (e.g. printer vs new document). The macro also wouldn't let the user choose which records to process if only a few labels were required.

The reason your macro doesn't work as expected is that it lacks any code to tell Word that it's a label merge. Furthermore, you're adding a single mergefield to the document and are not propagating that to all the labels. It also seems clear to me that you're not actually starting off with an empty document - it already has the empty labels in place. Had you populated the labels manually (again, a once-off exercise), there'd be no need for your code to add any mergefields or to propagate them to all the labels.

The last thing to note is that, if your user was to save the document after running your macro, it would now be a mailmerge main document and would thus generate the SQL prompt next time the document is opened...

amenu · #3 03-29-2018, 03:07 PM

Hi Paul,

Quote:

Originally Posted by macropod

It also seems clear to me that you're not actually starting off with an empty document - it already has the empty labels in place.

You are right. I did start off with a preformatted table.

Since my previous reply I have recorded a macro that seems to work. It opens the Word document ("MAILING.docx") that contains my latest preformatted table, asks for a Yes, and when Yes is clicked, creates the labels ready for printing. The disadvantages of using a macro are as you say, although reducing the process to one click is more likely to induce acceptance by the person I am hoping to help.

Here's the code:

Code:

Sub Macro4()
'
' Macro4 Macro
'
'
    ChangeFileOpenDirectory _
        "E:\Data backup 24 Nov 2017\DATA\Centennial Treasurer\"
    Documents.Open FileName:="MAILING.docx", ConfirmConversions:=False, _
        ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
        PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
        WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Colour code mail merge header table cell backgrounds	ScotsMaverick	Mail Merge	25	11-04-2021 02:07 PM
Can't modify cell margins	Sztrogacsev	Word Tables	1	11-19-2015 04:27 AM
how adjust cell width manually when table extends beyond paper margins?	dylansmith	Word	1	11-06-2014 12:01 AM
Formatting imported data into a table with variable record sets	ash6540	Excel	6	03-27-2014 04:13 AM
Problem with Cell Margins	Aston	Word Tables	6	07-15-2011 12:02 PM