When you run a mail merge in Word and try to connect to an Excel workbook as the data source, you may see an error message that says Word cannot open the data source or cannot establish a connection. This problem usually happens because of a mismatch between the data provider used by Word and the way Excel is configured, or because the Excel file is currently open in another program. This article explains the root cause of the connection failure and provides step-by-step fixes to restore the link between Word and your Excel data source.
Key Takeaways: Fixing the Word-to-Excel Mail Merge Connection
- Close the Excel workbook before running the mail merge: Word cannot read an Excel file that is open in Excel or another application.
- Use the OLE DB provider instead of ODBC: Word connects to Excel files through a data provider; switching to Microsoft OLE DB Provider for Excel resolves many connection errors.
- Confirm the Excel file is not corrupted: A damaged workbook can prevent Word from reading the data source; test with a new blank workbook.
Why Word Loses the Connection to an Excel Data Source
Word uses a data provider to read the contents of an Excel workbook during a mail merge. The default provider is often the Microsoft ODBC Driver for Excel. This driver has known limitations and can fail when the Excel file is open, when the file path contains special characters, or when the file is stored on a network share that requires different credentials. Additionally, Word expects the first row of the selected worksheet or named range to contain field names. If the Excel file lacks a defined range or has blank top rows, the connection attempt fails silently.
The error typically appears as: “Word cannot open the data source” or “Word cannot establish a DDE connection”. Both messages point to the same underlying issue — Word cannot negotiate a proper data channel with the Excel file. In some cases, the problem is caused by a missing or misconfigured registry entry for the Microsoft Office Data Source Object (DSO).
Steps to Reconnect Word Mail Merge to Excel
- Close the Excel workbook completely
Word cannot open an Excel file that is currently open in Excel. Switch to Excel and close the workbook. If the workbook is embedded in an email or another document, save a copy to your local drive first. Then in Word, click Mailings > Select Recipients > Use an Existing List and browse to the closed file. - Use a named range or a defined worksheet
In Excel, select the data range that you want to use for the mail merge. Click Formulas > Define Name, type a name such as MyData, and click OK. Save and close the workbook. In Word, when you select the data source, choose New Source under the connection options and pick the named range. This avoids ambiguity about which part of the sheet to read. - Switch to the Microsoft OLE DB Provider for Excel
In Word, start the mail merge and select your Excel file. When the Confirm Data Source dialog appears, click Show All. Select Microsoft Excel via OLE DB Provider from the list and click OK. If this dialog does not appear, cancel the mail merge and restart it. The OLE DB provider handles file locking and network paths better than the ODBC driver. - Disable DDE (Dynamic Data Exchange)
DDE is the protocol Word uses to request data from Excel. Some security updates block DDE connections. Open Word and go to File > Options > Advanced. Scroll to the General section and uncheck Confirm file format conversion on open. Then restart Word. This setting forces Word to use a direct file read instead of DDE for Excel data sources. - Move the Excel file to a local folder
Network drives, OneDrive folders, and SharePoint locations can trigger permission errors. Copy the Excel workbook to a local folder such as C:\MailMergeData. Remove any special characters or spaces from the file name. For example, rename Sales Data (2024).xlsx to SalesData2024.xlsx. Then reconnect the mail merge to this local copy. - Repair the Microsoft Office installation
If the data provider itself is corrupted, repair Office. Open Settings > Apps > Installed apps in Windows 11. Find Microsoft 365 or Microsoft Office in the list, click the three dots, and select Modify. Choose Quick Repair and follow the prompts. A Quick Repair takes a few minutes and does not remove your files or settings. - Create a new Excel workbook with only the data you need
A workbook with many sheets, complex formulas, or external links can confuse Word. Create a new workbook. Copy only the data columns and rows that you need for the merge. Save it as .xlsx in a local folder. Use this clean workbook as the data source in Word.
If Word Still Has Issues After the Main Fix
Word says “Word cannot open the data source” even after closing Excel
This error often means the Excel file is locked by a background process. Restart both Word and Excel. If the problem persists, open the Excel file in Excel, click File > Save As, and save a copy as .xlsb (Excel Binary Workbook) or .xlsx. Use the copy in Word. The binary format reduces file size and eliminates certain compatibility issues.
Mail merge runs but pulls data from the wrong columns
Word reads the first row of the data source as field names. If your Excel file has blank cells in the first row, Word assigns generic names like Column1. Open the Excel file and fill every cell in the first row with a unique column name. Avoid spaces and special characters in the column names. Save and close the file before reconnecting in Word.
Connection fails when the Excel file is on OneDrive or SharePoint
Word cannot always open Excel files directly from cloud sync folders because the sync client holds a file lock. Download the file to a local folder. Right-click the file in File Explorer and choose Always keep on this device. After the file syncs locally, close Excel and run the mail merge from Word using the local copy.
Data Source Connection Methods: OLE DB vs ODBC vs DDE
| Item | OLE DB | ODBC | DDE |
|---|---|---|---|
| Description | Direct provider that reads Excel file structure | Legacy driver using SQL-based queries | Protocol that requests Excel to send data live |
| Reliability | High — works with locked and network files | Moderate — fails with open files and long paths | Low — blocked by security updates and file locks |
| Speed | Fast for small to medium data sets | Moderate — adds overhead for SQL parsing | Slow — requires Excel to be open and responsive |
| Best use case | Local .xlsx files with named ranges | Legacy .xls files or SQL Server data sources | Not recommended for new mail merges |
In most cases, the OLE DB provider is the best choice for connecting Word mail merge to an Excel data source. It avoids the file-locking problems of DDE and the compatibility limits of ODBC.
You can now reconnect Word to any Excel workbook by closing the file first, using a named range, and selecting the OLE DB provider when prompted. If the error persists, disable DDE in Word Options or move the file to a local folder. As an advanced step, consider converting your Excel data into an Access database or a SQL Server table for large mail merges — these sources handle concurrent access more reliably than Excel files.