Many people access the material from this web site daily. Most just leech what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved, however small, would help to ensure the continued availability of this resource. Click the adjacent button to access PayPal.

Duplex merge data for postcards

With input from fellow MVPs Doug Robbins (who provided the central core of the macro code) and Greg Maxey, I offer a solution to the problem of merging data to the backs of labels, whether this is done in one pass with a printer with a duplex option, or by merging the fronts and backs in separate operations.

There are few circumstances where you would need to merge to the backs of labels. The obvious one is postcards, but you might conceivably wish to merge to a business card format. This solution will cater for either.

The problems with such merges are threefold.

1. When duplexing, the fronts and the backs of the pages are printed alternately, having merged to the front of the page, you cannot run back to the start of the data to add data from the same records to the back.

2. While the fronts of the labels read from left to right across the rows, the corresponding labels, when you turn over the sheet, read from right to left e.g.:

The front of the label document

Label 1	Label 2
Label 3	Label 4
Label 5	Label 6

On the reverse side the corresponding labels read right to left as follows:

Label 2	Label 1
Label 4	Label 3
Label 6	Label 5

3. If the number of records is not a multiple of the number of labels, blank records will be required to fill the missing labels on the front of the page.

The solution to all three issues is to recreate the data file with the records assembled in the correct order and is provided in the form of an add-in template. (The template is not protected so you can modify the code to your own requirements, should it be necessary.)

The add-in is documented in the text space of the template.

For the function to be always available, the add-in should be installed in the Word startup folder, its location defined in Word at Tools > Options > File Locations > Startup, and Word's macro security set to trust installed templates and add-ins. If you prefer to have it available only when required, put the template in some other folder (a sub folder of the startup folder is ideal) and add it to the list of add-ins at Tools > Templates and Add-ins. This will remain listed over a re-start of Word, but will not be automatically checked. Check it to make the function available.

Note:

The add-in is intended to work with a data file in Word table format (though a tool is provided to convert simple comma delimited files). A Word merge will treat an empty record as an empty record. Blank lines in a comma delimited file would be treated as an error.

Where the data source has many fields or is from a format other than a Word table, create a directory merge into a one row table, using only the fields that will be required on the merged cards and merge to a new document. Add a title row to the top of that document and use that as a data source for use with this add-in.

The add-in provides functions to create a data file to enable merge printing to both sides of a postcard merge document with a duplex printer, or to the backs only where a duplex printer is not available.

Where a duplex printer is not available, treat the fronts and backs of the cards as separate merge documents, using the original data source for the fronts and the modified data source for the backs.

Note:

A single column label format and no duplex printer, thus separate merges for fronts and backs, can use the same data for both front and back merges.

The core functions are accessed from the Duplex Post Cards Data toolbar (or by running the macro DuplexData), which should be run with the data file open.

This opens a userform which provides a number of options:

The 'Fronts & Backs' option, prompts for the label format which for postcards (or business cards) will have 1 or 2 labels per column and up to 5 rows. The code has been restricted to these formats which encompass all the common postcard and business card formats, but if you have an unusual document format, it should be possible to modify the code to accommodate it.

The macro duplicates the records for a full page of labels and passes them to a new document with each record in its correct order to produce the above pattern, according to the chosen settings for columns and rows.

Please note the warning at the bottom of the userform. With a large data file and a slow PC it may seem that the application has hung as nothing much happens on screen. It will get there in the end.

The document is renamed thus retaining its original format under the original filename.

The 'Backs Only' option works without further user interaction. For one column label formats, the original data file can be used for the merge to the backs of the cards.

The 'Convert to Table' option provides a means of converting a simple comma delimited data file to a table.

'CANCEL' is self evident.

Directory merge to create a Word Table data file

If you have a large number of data fields, e.g. when working with Outlook or other non-Word sources, you must create a Word table data file containing the required fields for reproduction on the cards. Remember these are for use with labels and there is only a finite amount of space to add fields to a label.

In the following example, the first illustration shows fields from Outlook, merged into a single row table.

You may find it helpful to work in landscape mode, or even normal view (as shown).

The following illustration shows the result of the directory/catalog merge to a new document.

Note:

Clearly Outlook's programming team and Word's are not working from the same hymn sheet as Outlook still uses the old name 'Catalog', while Word calls the same thing 'Directory'.

Add a title row and name the columns (the names do not have to be the same as in the original data file from which the merge was created, as this now will be the data file).

The conversion tools may now be applied to this table.