Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Blackard Blackard is offline
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel worksheet in Word doc diana Microsoft Word Help 0 July 10th 08 09:52 PM
Problem printing Word document with embedded Excel worksheet jon Microsoft Word Help 1 March 10th 06 04:51 PM
Microsoft Excel Worksheet Jen Microsoft Word Help 6 November 29th 05 09:50 PM
embedding an excel worksheet facilitatoram Tables 2 February 28th 05 11:27 PM
Word-Excel 2003 - Mail Merge Recipients problem AYager Mailmerge 2 January 11th 05 05:11 AM


All times are GMT +1. The time now is 09:20 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"