Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem importing recipients from nominated Excel worksheet
I'm attempting to do a mail merge on a letter created in Word 2007. When I
chose "Select Recipients", I then selected "Use existing list" then browsed to locate the correct Excel 2007 file. The correct worksheet was identified. No problem so far. However, when I selected the worksheet, the "Select table" screen shows an entirely different file name and location. If I arrow down, other file names and locations are listed, but not the one I selected. There is nothing in the table list. Any thoughts on a solution? -- ajb |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem importing recipients from nominated Excel worksheet
It sounds to me as if you are seeing the ODBC connection dialog box when
you should be seeing the OLE DB dialog box. When Word tries to connect to an Excel data source, it tries OLE DB first, but if it cannot open the Excel workbook using OLE DB, it tries ODBC, which is an older method. (Then if that does not work, it tries DDE, which is even older). The ODBC dialog box behaves in the way you describe - it does not necessarily select the file name you specified (in fact, if the path name is long, you may not be able to see the entire path name at all). If you can select the correct file, you should be able to see all the worksheets and named ranges by clicking Options and selecting all four options. However, the fact that you are seeing the ODBC dialog at all is a sign that there could be something wrong with your workbook, are at the very least, something that OLE DB cannot "understand." For example, if the workbook is password-protected, OLE DB cannot open it. But ODBC cannot do so either. In that particular case you would have either to remove the protection, or use DDE to open the workbook. If you need to do that, you can either wait until ODBC connection has failed (in which case you should see the DDE dialog) or - check Word Office Button-Word Options-Advanced-General-"Confirm File format conversion on open". - go through the connection process again. You should see a dialog box titled "Confirm Data Source". It will probably just show "OLE DB Database Files". - click "Show all" - scroll down the list and select "MS Excel Worksheets via DDE (*.xls)" - click OK Excel should open (if it is not already open) and open the workbook (if necessary), at which point you will see any password dialog. NB you can only get data from the first worksheet in the workbbook with DDE, or from named ranges in that sheet. Further, you don't get Unicode data (same with ODBC). If you do not know a reason why your workbook would not open with OLEDB, you could consider reconstructing it. Although it seems that working with lists/databases within Excel (aplying filters/grouping) can sometimes cause this kind of problem, I have never got to the bottom of what causes problems for OLE DB. Peter Jamieson http://tips.pjmsn.me.uk Blackard wrote: I'm attempting to do a mail merge on a letter created in Word 2007. When I chose "Select Recipients", I then selected "Use existing list" then browsed to locate the correct Excel 2007 file. The correct worksheet was identified. No problem so far. However, when I selected the worksheet, the "Select table" screen shows an entirely different file name and location. If I arrow down, other file names and locations are listed, but not the one I selected. There is nothing in the table list. Any thoughts on a solution? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem importing recipients from nominated Excel worksheet
- go through the connection process again. You should see a dialog box
titled "Confirm Data Source". It will probably just show "OLE DB Database Files". NB, if it's a .xsls or .xlsm, you just see OLE DB Database files. If it's a .xls, you should see the OLE DB, ODBC and DDE options. Peter Jamieson http://tips.pjmsn.me.uk Peter Jamieson wrote: It sounds to me as if you are seeing the ODBC connection dialog box when you should be seeing the OLE DB dialog box. When Word tries to connect to an Excel data source, it tries OLE DB first, but if it cannot open the Excel workbook using OLE DB, it tries ODBC, which is an older method. (Then if that does not work, it tries DDE, which is even older). The ODBC dialog box behaves in the way you describe - it does not necessarily select the file name you specified (in fact, if the path name is long, you may not be able to see the entire path name at all). If you can select the correct file, you should be able to see all the worksheets and named ranges by clicking Options and selecting all four options. However, the fact that you are seeing the ODBC dialog at all is a sign that there could be something wrong with your workbook, are at the very least, something that OLE DB cannot "understand." For example, if the workbook is password-protected, OLE DB cannot open it. But ODBC cannot do so either. In that particular case you would have either to remove the protection, or use DDE to open the workbook. If you need to do that, you can either wait until ODBC connection has failed (in which case you should see the DDE dialog) or - check Word Office Button-Word Options-Advanced-General-"Confirm File format conversion on open". - go through the connection process again. You should see a dialog box titled "Confirm Data Source". It will probably just show "OLE DB Database Files". - click "Show all" - scroll down the list and select "MS Excel Worksheets via DDE (*.xls)" - click OK Excel should open (if it is not already open) and open the workbook (if necessary), at which point you will see any password dialog. NB you can only get data from the first worksheet in the workbbook with DDE, or from named ranges in that sheet. Further, you don't get Unicode data (same with ODBC). If you do not know a reason why your workbook would not open with OLEDB, you could consider reconstructing it. Although it seems that working with lists/databases within Excel (aplying filters/grouping) can sometimes cause this kind of problem, I have never got to the bottom of what causes problems for OLE DB. Peter Jamieson http://tips.pjmsn.me.uk Blackard wrote: I'm attempting to do a mail merge on a letter created in Word 2007. When I chose "Select Recipients", I then selected "Use existing list" then browsed to locate the correct Excel 2007 file. The correct worksheet was identified. No problem so far. However, when I selected the worksheet, the "Select table" screen shows an entirely different file name and location. If I arrow down, other file names and locations are listed, but not the one I selected. There is nothing in the table list. Any thoughts on a solution? |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem importing recipients from nominated Excel worksheet
I realise now that if it's a .xslx/.xlsm, you really do see the wrong
tables in the ODBC dialog box even when you have selected the correct file. And that occurs even if you explicitly select the ODBC option. So I guess it's either "fix the .xlsx/.xlsm if possible," or use DDE. Sorry about that - another nail in the coffin of Word MailMerge usability. Peter Jamieson http://tips.pjmsn.me.uk Peter Jamieson wrote: It sounds to me as if you are seeing the ODBC connection dialog box when you should be seeing the OLE DB dialog box. When Word tries to connect to an Excel data source, it tries OLE DB first, but if it cannot open the Excel workbook using OLE DB, it tries ODBC, which is an older method. (Then if that does not work, it tries DDE, which is even older). The ODBC dialog box behaves in the way you describe - it does not necessarily select the file name you specified (in fact, if the path name is long, you may not be able to see the entire path name at all). If you can select the correct file, you should be able to see all the worksheets and named ranges by clicking Options and selecting all four options. However, the fact that you are seeing the ODBC dialog at all is a sign that there could be something wrong with your workbook, are at the very least, something that OLE DB cannot "understand." For example, if the workbook is password-protected, OLE DB cannot open it. But ODBC cannot do so either. In that particular case you would have either to remove the protection, or use DDE to open the workbook. If you need to do that, you can either wait until ODBC connection has failed (in which case you should see the DDE dialog) or - check Word Office Button-Word Options-Advanced-General-"Confirm File format conversion on open". - go through the connection process again. You should see a dialog box titled "Confirm Data Source". It will probably just show "OLE DB Database Files". - click "Show all" - scroll down the list and select "MS Excel Worksheets via DDE (*.xls)" - click OK Excel should open (if it is not already open) and open the workbook (if necessary), at which point you will see any password dialog. NB you can only get data from the first worksheet in the workbbook with DDE, or from named ranges in that sheet. Further, you don't get Unicode data (same with ODBC). If you do not know a reason why your workbook would not open with OLEDB, you could consider reconstructing it. Although it seems that working with lists/databases within Excel (aplying filters/grouping) can sometimes cause this kind of problem, I have never got to the bottom of what causes problems for OLE DB. Peter Jamieson http://tips.pjmsn.me.uk Blackard wrote: I'm attempting to do a mail merge on a letter created in Word 2007. When I chose "Select Recipients", I then selected "Use existing list" then browsed to locate the correct Excel 2007 file. The correct worksheet was identified. No problem so far. However, when I selected the worksheet, the "Select table" screen shows an entirely different file name and location. If I arrow down, other file names and locations are listed, but not the one I selected. There is nothing in the table list. Any thoughts on a solution? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel worksheet in Word doc | Microsoft Word Help | |||
Problem printing Word document with embedded Excel worksheet | Microsoft Word Help | |||
Microsoft Excel Worksheet | Microsoft Word Help | |||
embedding an excel worksheet | Tables | |||
Word-Excel 2003 - Mail Merge Recipients problem | Mailmerge |