Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to specify Excel sheet name as mail merge source
In Word 2000 the "Confirm conversions on open" option does not normally
affect mail merge data sources. Instead, there is a "Select Method" checkbox in the Open Data Source dialog box which does more or less the same thing. If you seleselect it, you should see three possible ways to connect to an Excel data source: DDE, Converter, and ODBC. In Word 2000, DDE is the default method. It opens Excel (if neecssary) and usually gets the data more or less as you see it in the Excel display. But it can only see the first sheet in the Excel workbook. ODBC is probably the better bet. With ODBC, you will need to check the Options button in the next dialog box and check all the options (Tables, views, system etc.) to see all the things you can connect to, and you may have more trouble formatting your data (e.g. blanks in Excel numeric columns appearing as zeroes in Word, date fields need formatting switches in Word MERGEFIELD fields) than with DDE. Peter Jamieson "UnclePaul" wrote in message ... Thanks Doug, I found the 'Confirm conversions on open' box and checked it, but the behavior did not change. Details: In the 'Mail Merge Helper' dialog, the second step or button 'Get Data' leads to some options, where I choose 'Open Data Source' and navigate to the multi-sheet workbook and Open it. A dialog box appears, titled simply 'Microsoft Excel', prompting for 'Named or Cell Range', with a drop-down list. The only item in the list is 'Entire Spreadsheet'. I suspect that at this point I could specify the sheet to be used (by typing over 'Entire Spreadsheet' with my selection), but I don't know the syntax for specifying the sheet name. I think it may be the sheet name either preceeded by, followed by, or surrounded by exclamation points; I have tried several combinations but it always insists on using the data from the first sheet (the one represented by the leftmost tab) of the workbook. "Doug Robbins" wrote: I don't remember if in Word 2000, there is a "Confirm conversions on open" item under ToolsOptionsGeneral. If there is, and it has the same effect as in later versions of Word, when the box next to is checked, when you attach a mail merge data source, you will be presented with a number of options for the way in which the data source is connected and with one of those, you should be able to select the worksheet that you want to use. -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "UnclePaul" wrote in message ... I want to use one sheet of a multi-sheet Excel 2000 workbook as the data source for a Word 2000 mail-merge. How / where do I specify the name of the worksheet that contains the data that is to be merged into the Word document? |
#2
|
|||
|
|||
I am trying to set up some mergeable documents that will help non-users be
able to run a mail merge in Word 2k with a minimum of effort (by clicking the merge button). I have tried to use the suggestion below to run a mail merge off the second sheet of an Excel spreadsheet I have pre-formatted for data entry and keep getting an "unable to connect" error. If I used the name of the named range on the sheet using ODBC, it transferrs all blank lines below the data as the named range is columns A:I (allowing for new entries by users). Is there a way to suppress the blank lines so the named range does not need to be reset every time a merge is run? "Peter Jamieson" wrote: In Word 2000 the "Confirm conversions on open" option does not normally affect mail merge data sources. Instead, there is a "Select Method" checkbox in the Open Data Source dialog box which does more or less the same thing. If you seleselect it, you should see three possible ways to connect to an Excel data source: DDE, Converter, and ODBC. In Word 2000, DDE is the default method. It opens Excel (if neecssary) and usually gets the data more or less as you see it in the Excel display. But it can only see the first sheet in the Excel workbook. ODBC is probably the better bet. With ODBC, you will need to check the Options button in the next dialog box and check all the options (Tables, views, system etc.) to see all the things you can connect to, and you may have more trouble formatting your data (e.g. blanks in Excel numeric columns appearing as zeroes in Word, date fields need formatting switches in Word MERGEFIELD fields) than with DDE. Peter Jamieson |
#3
|
|||
|
|||
Can you use the filtering options (Mail Merge Helper|Query Options) to
eliminate your blank records? (If you can set up the merge data source using VBA and OpenDataSource it may be slightly easier to make this work). Peter Jamieson "Diana Jordan" wrote in message ... I am trying to set up some mergeable documents that will help non-users be able to run a mail merge in Word 2k with a minimum of effort (by clicking the merge button). I have tried to use the suggestion below to run a mail merge off the second sheet of an Excel spreadsheet I have pre-formatted for data entry and keep getting an "unable to connect" error. If I used the name of the named range on the sheet using ODBC, it transferrs all blank lines below the data as the named range is columns A:I (allowing for new entries by users). Is there a way to suppress the blank lines so the named range does not need to be reset every time a merge is run? "Peter Jamieson" wrote: In Word 2000 the "Confirm conversions on open" option does not normally affect mail merge data sources. Instead, there is a "Select Method" checkbox in the Open Data Source dialog box which does more or less the same thing. If you seleselect it, you should see three possible ways to connect to an Excel data source: DDE, Converter, and ODBC. In Word 2000, DDE is the default method. It opens Excel (if neecssary) and usually gets the data more or less as you see it in the Excel display. But it can only see the first sheet in the Excel workbook. ODBC is probably the better bet. With ODBC, you will need to check the Options button in the next dialog box and check all the options (Tables, views, system etc.) to see all the things you can connect to, and you may have more trouble formatting your data (e.g. blanks in Excel numeric columns appearing as zeroes in Word, date fields need formatting switches in Word MERGEFIELD fields) than with DDE. Peter Jamieson |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merge with attachments | Mailmerge | |||
Mail merge data source problem | Mailmerge | |||
mail merge error using excel | Mailmerge | |||
Mail merge error occurs when filtering Excel data source | Mailmerge | |||
How do you set up a label mail merge with Excel as source in 2003. | Mailmerge |