This is the list of data to be merged into the destination template.

It should be on a separate worksheet by itself.

It must be in the format of field names in row 1 with the data itself in any rows below that, with or without gaps.

The columns can be in any order.

Hidden (or filtered as hidden) rows will be ignored.

The field names must NOT have the brackets around them on this sheet ie for a merge field called "<<Surname>>" in the destination template, the header on this sheet must be "Surname" (no quotes). This must be an exact match and it is case sensitive.

Note.

• If there is data on this sheet that is not required on the destination template then it is ignored.
• Hidden rows will be ignored - useful if you wish to use Excel's Autofilter to filter out certain rows.

Special Merge Field Names

1. For merging into one new Excel workbook with a separate worksheet for each merge you may use the following header on row 1 of your source data:

TabName - this is optional, but if present will be used (if possible*) to name the worksheet for that merge

* It may not be possible depending on the length of the text, whether it includes disallowed characters, and whether it is a duplicate of another worksheet.

2. For merging into different Destination Template worksheets for each line of data:

DstWorksheet - this must be the name (case sensitive) of a destination worksheet in the same workbook.

3. To insert an image (jpg, gif etc) into the destination worksheet you may use the following header on row 1 of your source data:

• InsertImage - the data must have the full path and filename; images will be inserted into Cell A1 by default, but you can specify a different cell location (format D2 for example) in the column immediately to the right - this column containing the cell location does not require a header.

4. When printing, you may select a different number of printouts for each merge, using the header on row 1 called "PrintCopies".

5. Use the header SaveFileName to specify a full path and file name for each merge. Please note:

• This must be a complete path and filename eg C:\My Merges\abc.xls
• Any existing files will be overwritten without warning
• You should use a drive letter rather than server name path ie c:\etc rather than \\server1\etc
• If you do not provide a proper file name then the merge will fail
• You can use a file name for some rows and leave it blank for others, in which case the blank ones will revert to the filename given in the Merge window
• You can produce 'dynamic' filenames using a formula such as ="C:\test\merge" & A2 & ".xls" (see more here)

top

This is the template for the form, letter etc which is to receive the source data.

The worksheet can contain graphics, formulae, etc - in fact anything that a normal worksheet can contain.

The merge fields must be in the format <<abc01>>, <<abc02>> etc (up to a maximum of 99 ie <<abc99>>) where abc is the merge data field name (NB make sure you use the digit zero and not the letter O).

The merge fields can be contained within other text in a cell, be by themselves in cells, or be contained in a formula in a cell. Because the software always works in automatic recalculation mode the formulae will be computed afresh before each merged document is produced. This is a particular strength of using this tool within Excel for mail-merging. 

Tip. To concatenate (put together) some text with a calculated formula in a single cell in Excel you can use a formula like this:

="Machine <<Machine01>> gave a reading of "&12.2*D1&" mm."

ie Use the equals sign to designate a formula, the text (including the merge field) goes inside double quotes, and the formula part is adjoined to text using the ampersand character.

A cell may contain one or more merge fields.

Merge fields can be contained within merged cells.

Merge fields can be anywhere on a sheet, can be contained more than once, and they do not need to be in any order.

If in doubt, try it! - best with a single merge, using Print Preview.

Important Note: When you prepare your destination template you should ensure that any cells containing merge fields have sufficient space to allow the merge field(s) to be replaced with the longest piece of source data without having overlap problems or text disappearing out of the print area.

top

The results of the mail merge can be output to the following.

1) Print Preview is probably most useful for testing a few samples. If you accidentally send a large amount you will have a chance to cancel after each set of 5 outputs.

2) Printer sends each output to your printer.

Note: In some cases the software may send output faster than your printer can print - in this case it will usually buffer the output, but in some cases the buffer may fill up. If this happens you may need to run the merge in batches. Hence the software always leaves the active cell on the next row to be run. To assist with this you may find it useful to put a few seconds delay between each merge using the option on the screen.

3) File will save the merges results into

• text file(s). Unformatted text files do not look smart and lose any graphics etc, but they have the advantage of being small, being readable by any PC and easily imported to most software packages. You can save either
  • one text file with page breaks between each (- this can be opened in Microsoft Word or other software as required), or
  • individual text files for each merge. These will use the file name you specify but with a numeric suffix eg C:\My Documents\Results.txt will give you files C:\My Documents\Results00001.txt, C:\My Documents\Results00002.txt, C:\My Documents\Results00003.txt, etc. NB If any of these files already exist they will be overwritten without any warning.
• Excel workbooks - each one with just one worksheet with your merged data. These will use the file name you specify but with a numeric suffix eg C:\My Documents\Results.xls will give you files C:\My Documents\Results00001.xls, C:\My Documents\Results00002.xls, C:\My Documents\Results00003.xls, etc. NB If any of these files already exist they will be overwritten without any warning.

4) Separate sheets in new workbook will create the merged documents in one new Excel workbook with a separate worksheet for each merge and leave this workbook open. Note: The software does not save this file.

top

This file contains no macros, passwords or other protection.

Instructions:

• Simply open this file and then run PATools Page Merge. It can be opened by clicking on the drop-down.

You can try it with different options.

Use this file (or make your own) to try out the tools. If you corrupt it you can always reinstall it or download a new copy from the PATools website.

top

For each merge there is a checkbox called "Remove formulas from outputs". This may be useful when you create Excel file results and do not wish your result to include formulae but simply to show the values.

In addition, from the drop-down at the lower right of the screen you can select 'Options' for the general use of the software.

1) Use the following cell content (default value is Text) may help when you do not get the exact merge results you need. The difference between the three is best explained by a cell, formatted to 2 decimal places, that contains the formula "=SUM(A1:A2)" will show the value as 26 and the text as 26.00.

In order to find what works best for you we recommend good old trial and error!

2) Automatically select last used row (default value is ticked) lets you specify whether the default value of 'To Row' automatically selects the last row used on the source worksheet.

3) Save copy of workbook before each merge: By default, before you run a merge, the software automatically saves a backup copy of your workbook in the folder where you installed the software; the file is called LastMergeFile.xls. The advantage of this is that in the unlikely event that your workbook gets corrupted during the merge, and you did not previously save a copy, you have this backup copy to retrieve. The disadvantage is that there is a slight delay at the start of each run whilst Excel saves this backup file.

top