Use Dynamic Data Exchange
Dynamic Data Exchange (DDE) is one method available in the Microsoft
Windows® operating system for transferring data between applications. It uses
shared memory to exchange the data. If you use other data transfer methods,
you run into a problem: Data from the worksheet is transferred in the native
format in which Excel stores it rather than with the formatting that you
applied in the worksheet cells.
To choose the DDE data transfer method when you're connecting to an Excel
worksheet during a mail merge, all you have to do is select one check box in
Word:
1. On the Tools menu, click Options, and then click the General tab.
2. Click Confirm conversion at Open.
At the step in the mail-merge process when you connect to your data file,
after you locate the Excel worksheet you want to connect to, the Confirm Data
Source dialog box opens. Click MS Excel Worksheets via DDE (*.xls), and then
click OK. In the Microsoft Excel dialog box, for Named or cell range, select
the cell range or worksheet that contains the information you want to merge,
and then click OK. The numbers from the Excel spreadsheet will now look
exactly the same in your merged documents as they look in the worksheet cells.
NOTE It's probably a good idea to turn off the Confirm conversion at
Open option after you finish your mail merge. Otherwise, you might be
prompted to confirm your data source at times when that's the last thing you
want to worry about.
Muck around with field codes in Word
Because the DDE approach is so slick, you might be tempted to stop reading
right here. It's worth pressing on, though, because learning a little bit
about field codes can give you a lot more control over your mail-merge
results.
Format currency and other numbers by using field codes
Let's start with an example. Say you insert a Price field into a form letter
that you're preparing for a mail merge. In the main document, it looks
something like this, where «Price» is the field:
The gizmo you ordered will cost «Price».
Press ALT+F9, and you'll see the code behind the field. That code will look
like this:
The gizmo you ordered will cost { MERGEFIELD "Price" }.
You can control the formatting of the prices in that field just by typing a
few additional characters (that is, by adding a formatting switch) inside the
braces.
To include:
a dollar sign
four digits by default, and a space if the number you're merging has only
three digits
two decimal places
and a comma between the first and second numbers
this is what you type (shown in bold) in the field code:
{ MERGEFIELD "Price" \# $#,###.00 }
When you finish typing, press ALT+F9 to stop looking at field codes. Now
when you merge, all of your prices will be formatted exactly the way you
want. (You can use this same approach with numbers other than prices.)
Here's a breakdown of the elements in the switch we just used:
1. The name of the field that you inserted into your main document. It
corresponds to a column in your Excel worksheet.
2. Backslash, which starts the formatting switch.
3. Definition of the switch — in this case, to format numbers.
4. Characters that you want to include — for example, a $ that appears
before each price.
5. The maximum number of digits. If there are fewer digits in a number, Word
leaves a blank. Type commas where you want them to appear in the number.
6. Decimal point, which you type where you want it to appear. The zeros
specify the maximum number of digits after the decimal point. If there are
fewer digits, Word puts in a zero.
In the See Also box, you will find a link (called Numeric Picture field
switch) to more information about formatting numbers by using a switch.
Format dates by using field codes
You can also use a formatting switch to get dates from a Date column in your
spreadsheet to look exactly the way you want in your merged documents. If you
insert a Date field into the main document and then press ALT+F9, you see
this:
{ MERGEFIELD "Date" }
To get all the dates in the merged documents to have the format February 18,
2008 (regardless of how the dates are formatted in the worksheet cells), you
can add this formatting switch (shown in bold) to the field code:
{ MERGEFIELD "Date" \@ "MMMM d, yyyy" }
In the See Also box, you can find a link (called Date-Time Picture field
switch) to more information about formatting dates by using a switch.
Format cells in an Excel worksheet
This approach might be just the ticket if you're creating a new Excel
worksheet to use with a mail-merge project. In some ways, it's the most
straightforward approach because you can set up the dates, percentages, and
other numbers in your worksheet just the way you want them to look in Word
after the merge. Every time you merge, they'll look that way.
Number formatting problems in a mail merge occur when the cells that contain
the numbers in the Excel worksheet are formatted as Number, Currency, Date,
Percentage, and so on. The solution for getting numbers to behave properly
when you merge is to format as Text the cells that contain numbers. When
cells are formatted as Text, you can type your numbers in the cells exactly
the way you want them to look (with dollar signs, zeros, and so forth). Then
they will merge exactly as you type them.
1. To set cell formatting in Excel, select the column that contains the
cells you want to set.
2. On the Format menu, click Cells, and then click the Number tab.
3. Under Category, click Text.
Good tip about initial zeros in postal codes
You don't actually have to format cells that contain postal codes as Text to
make postal codes with initial zeros merge correctly. When you're typing the
postal code in the cell, just type an apostrophe before the initial zero. The
apostrophe doesn't show up in the cell. It just makes the zero behave.
Wishing you many happy merges,
Connie, the Answer Box monitor
About the Author
Connie Miller, a writer on the Microsoft Office User Assistance team,
collects and responds to issues that come to the attention of the Word Answer
Box. To submit a detailed question of your own that we may be able to respond
to, send feedback.
