Keep the Formatting When Doing a Mail-Merge in Word 2007 with Excel 2007 Data

NOTE: This is one of those situations where I am writing this up as a blog post because I searched the Internet high and low and couldn't find this solution. It may not be the only solution - but the only one that worked for me.

If you frequent this blog, you know that I have been forced to succumb to the 2007 version of Microsoft Office. Overall, I actually have enjoyed using and teaching this package but have found some of the elements of it to be a little bit...uh...buggy.

I was hit today with someone telling me that the formatting was dropping out when doing a mail-merge in Word of an Excel spreadsheet. I remember having to deal with this in Office 2003 and that the solution was using Microsoft's Dynamic Data Exchange. The problem was, it wasn't working this time in 2007.

Every time that I would try to open the document (after adjusting the initial Step 1 settings below), I would get the following error:

"This error message can appear if you attempt to insert a database into a Word document [which I wasn't] as an object or attach an Access data source to a Word mail-merge main document.

This error message usually occurs if there is a problem communicating via Dynamic Data Exchange (DDE).

Possible remedies are to reboot the system or attach the data source using an alternate method (ODBC, DAO)."

I did a bunch of Web surfing and a bunch of experimenting and here is the only way that I was able to get it to work.

Step 1: In Word, click the Office button and 'Word Options'. Then click the 'Advanced' tab and scroll down to 'General'. Put a check next to 'Confirm file format conversion on open'. Next, in Excel, click the Office button and 'Excel Options' and then click the 'Advanced' tab. Scroll down to 'General' again and make sure there isn't a check next to 'Ignore other applications that use Dynamic Data Exchange'.

Step 2: Open the Excel spreadsheet that I will be pulling in as a mail-merge in Excel. Minimize it after opening it.

Step 3: Open Word and click the 'Mailings' tab. Then click 'Select Recipients' and then 'Use Existing List...'. Choose the Excel spreadsheet even though that goes against everything that Office stands for since it usually will consider the document locked when it is open by another program. Because you adjusted your setting in Step 1, you will need to choose the correct program. Put a check in the lower-left-hand corner of the pop-up where it says 'Show all'. Then scroll down and double-click 'MS Excel Worksheets via DDE (.xls)'.

It should load in and keep the formatting. Unless I opened the document first in Excel, it would either give the error or remove any formatting.