unable to open Excel data source while performing a mail merge in Word

When using the mail merge wizard - When I try to open my Excel data source when browsing my recipients, a box comes up and asks me to "select a table" - I pick "sheet 1" and then it asks me select another table - workbook - and it gives the location of my Excel document, but then tells me it's unable to open data source.
Answer
Answer

As an explanation of what is going on, when Word opens the data source, it can use a number of different "connection methods". In the case of Excel, it

 a. tries OLE DB - which results in the first dialog box you see. But something is preventing Word from successfully opening using OLE DB, so it

 b. tries ODBC method, which is why you see the second dialog box with "Workbook". Frankly, if OLE DB fails, then ODBC is also very likely to fail anyway, but problems with the ODBC dialog box are that

  (1) the workbook you originally selected is not necessarily the one selected in the dialog box. You have to ensure that the correct one is selected, and that is often difficult because the pathname may be long enough that you cannot actually see the file name (and you can't increase the size of the box), and

  (2) you usually have to click Options, then select all the checkboxes before you see any worksheets/ranges listed

 c. if ODBC fails, Word tries the old DDE method, which opens Excel and gets it to open the workbook. (From your description, it sounds as if that doesn't work either).

However, that doesn't solve your problem, which is that Excel itself can probably open the file, but there's some corruption or something else in it that prevents Word from opening it. Personally, I'd verify that a simple, newly created workbook works OK. If not, then you probably need to repair Office.

Otherwise, you may be able to work around this if the number of columns does not exceed Word's maximum (around 64), by selecting the sheet in Excel and copying/pasting into a new Word document. That creates a Word table. Save the document, and use it as the data source. But if possible, I'd try to recreate the Excel workbook. If it's very simple (e.g. just rows and columns of data and no formulas) you may be able to export in .csv format and import into a new workbook.

 

 


Peter Jamieson

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated December 8, 2020 Views 8,018 Applies to: