Opening Excel During a Mail Merge

It’s weird how people panic when something is slightly different to how they expect

I client called me this morning as she was trying to do a mail merge but it was behaving differently to how her other mail merges work

Basically when she opened the Word document with the mail merge in, Excel also opened at the same time. She was able to view the data and make any changes she needed while doing the merge

This didn’t happen with the other merge

I was initially a bit boggled by this but then noticed as Word was opening the Excel file the status bar indicated it was trying to send DDE messages

DDE is an old technology that allow Windows programs to share data. Previous versions of Word used to DDE as part of the mail merge process.

The default method for Word 2003 (which is what she was using) is to use OLEDB, allowing Word to “talk” directly to the Excel file

You can change this behaviour though

Click on the “tools” menu and select “options”. Then click the “General” tab and put a tick in the “Confirm conversion at option” box. When you create a new mail merge and point to an Excel file it ask you how you want to talk to it and are able to select DDE

I later found out the original mail merge was set up many years ago and the problem she having was with a brand new merge, which is why she’d not seen this before

She could have done her merge using OLEDB but she was in such a panic as the file hadn’t opened that she thought there was a problem

The mail merge actually works fine using both methods but because it wasn’t working as expected she thought there was a problem

Also as a side note…as well as spotting the DDE commands I also noticed a difference in the warning message when opening the Word document

When you open a Word document (as of XP SP3) that talks to external data you get a warning message

Opening this document will run the following SQL command:
SELECT * FROM your datasource
Data from your database will be placed in the document. Do you want to continue?

Details on why this happens and how to disable the prompt (if you want to) are in this KB article

825765- You receive the "Opening this will run the following SQL command" message when you open a Word mail merge main document that is linked to a data source

The DDE based mail merge showed the following SQL command

SELECT * FROM C:\ExampleFilePath\ExampleFile.xls

whereas the OLEDB based merge displayed

SELECT * FROM ExampleSheetName$

This was when I realised the problem was to do with how the data was being retrieved which then led me onto the DDE thing

All good in the end!