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
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!