How to Restore the Confirm Data Source Dialog in Word Mail Merge
Question details
The user needs to restore the Confirm Data Source dialog during a Word Mail Merge to select the DDE connection option, which ensures specific Excel formatting is preserved.
- Product
- Microsoft Word and Excel
- Device & OS
- not provided
- Scenario
- Performing a Mail Merge in Word using an Excel data source where cell formatting, such as leading zeros in ZIP codes, must be retained exactly as displayed.
- Observed behavior
- The Confirm Data Source dialog is hidden by default, preventing the user from bypassing the default OLE DB connection to select the MS Excel Worksheets via DDE method.
Ensure that both your Word document and Excel data source are saved and completely closed before modifying advanced application settings to prevent data conflicts.
Enable Format Conversion Confirmation in Word and Configure Excel DDE
Adjusting the advanced options in both Word and Excel forces the Confirm Data Source dialog to appear, allowing you to select the DDE connection method for your mail merge.
By default, Word connects to Excel data sources using OLE DB, which imports raw data values and strips formatting like leading zeros. Forcing Word to ask for format conversion enables the older DDE (Dynamic Data Exchange) method, which reads the data exactly as it looks in Excel.
In Microsoft Word, navigate to File > Options > Advanced. Scroll down to the General section, check the box for 'Confirm file format conversion on open', and click OK.
Start your Mail Merge and select your Excel file as the data source. When the Confirm Data Source dialog appears, check the 'Show all' box, select 'MS Excel Worksheets via DDE (*.xls, *.xlsx)', and click OK.
If Word fails to connect or freezes, open Microsoft Excel. Go to File > Options > Advanced. Scroll to the General section, ensure that 'Ignore other applications that use Dynamic Data Exchange (DDE)' is unchecked, and click OK.
Try WPS Office for Seamless Mail Merge and Document Formatting
If you are tired of struggling with hidden settings and legacy DDE configuration errors in Microsoft Word, consider switching to WPS Office. It provides a highly compatible and user-friendly alternative for all your document processing needs without the configuration headaches.
Frequently Asked Questions
Why are leading zeros missing in my Word Mail Merge?
By default, Word imports data from Excel using OLE DB, which reads the underlying raw numeric values rather than the applied visual formatting. This causes numeric values like ZIP codes to lose their leading zeros.
What is DDE in Microsoft Office?
Dynamic Data Exchange (DDE) is a legacy technology that allows Microsoft Office applications to share data. In Mail Merge, using DDE forces Word to open Excel in the background and read the data exactly as it is formatted on the screen.
Why does the Confirm Data Source dialog not show the DDE option?
If the DDE option is missing, you must check the 'Show all' box in the Confirm Data Source dialog. This expands the list of available connection methods, including 'MS Excel Worksheets via DDE'.
Can I format numbers directly in Word without using DDE?
Yes, instead of changing the connection method to DDE, you can press Alt+F9 in Word to reveal field codes and manually add formatting switches (like \# "00000" for a 5-digit ZIP code) to format the merged data dynamically.
