Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Ohio
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MailMerge from Excel (2000)

    When mailmerging from excel to word, data from worksheet other than first is desired. First worksheet is always chosen. Tried typing in worksheet2!range in the dialog box but still get worksheet 1 data. Workaround of course is to shuffel worksheets but isn' t there a better way?

  2. #2
    Star Lounger
    Join Date
    Feb 2002
    Location
    Illinois, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MailMerge from Excel (2000)

    Sam:

    What you need to do is create a range containing the data you want to use for the merge. When you go to create your merge document (letter, labels, etc) and select and open the data source, a dialog box will appear asking you if you want to use the entire spreadsheet. In the same box the name of the range you set up in Excel should also appear. Just select the range name and the merge will then pull data from the named range and not the first worksheet.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MailMerge from Excel (2000)

    Hi Sam:
    Haven't had time to look at this for awhile, but I collected this tip some time ago:

    MailMerge: Merging from Excel Worksheets
    (Source: Allen Wyatt's WordTips Gold & KB Q116470 & Q212314)
    When using an Excel worksheet as a datasource, you may have noticed that you can only access information in the first worksheet of an Excel workbook. This is a recognized flaw in Excel.

    There are two ways you can access Excel information: using DDE (Dynamic Data Exchange) format or using ODBC (Open Database Connectivity). The problem affects Word 6, 7, 97 & 2000.
    Method 1: Using DDE Directly
    If you use DDE, which is the default method, you must make sure that the information you want to use is on the first sheet in a workbook. Additionally, if you are accessing only a named range, then you will want to make sure that the sheet containing the range is the first sheet in the workbook. If you need to move worksheets, make sure you do so before you link to the data from Word. If you try moving the sheets after linking, then Word will not recognize your changes.

    1. In the Get Data list, click Open Data Source.
    2. Locate and select the Microsoft Excel file. Make sure that the Select Method box in the Open Data Source dialog box is selected.
    3. Click Open.
    4. Select MS Excel Worksheets via DDE in the Confirm Data Source dialog box. Click OK.
    5. In the Microsoft Excel dialog box, select the Entire Spreadsheet or Named or Cell Range from the list. Click OK.
    6. Click Edit Main Document and continue with the merge process.
    Method 2: Using the Data Converter
    1. Click on Tools/MailMerge/Create.
    2. Select any type of document.
    3. Click Get Data/Open DataSource/ & select the Excel file you want to use.
    4. Make sure the Select Method check box is selected (Word 7, 97, & 2000), at the right side of the dialog box(bottom center in Word 2000). (In Word 6, you should select the Confirm Conversions check box instead.)
    5. Click on Open. This displays the Confirm Data Source dialog box.
    6. Select the Microsoft Excel Worksheet via Converter (*.xls, *.xlw) option.
    7. Click on OK. This displays the Open Worksheet dialog box. (In Word 6, it is the Open Workbook dialog box.)
    8. Using the controls in the dialog box, select the portion of the Excel workbook you want to use as your mail merge data. (If you are using Word 6, click on OK this displays the Open Worksheet dialog box. Select the portion of the worksheet you want to use.)
    9. Make sure the Format for Mail Merge check box is selected.
    10. Continue setting up your mail merge documents as you normally would.
    Method 3: Using ODBC
    This works in Word 7, 97, or 2000. Before you can use this method, however, you need to make sure that ODBC is installed on your system. You can check this out by simply opening the Control Panel and seeing if there is an ODBC applet there. If there is not, then you must install ODBC (using the Windows Setup program) before this method can work.

    1. Using Excel, define and name a range that identifies the data you want to use in the merge operation. Make sure you include the column headings in the range definition. Also, your range name should contain no spaces. Save and close your Excel workbook.
    2. Start Word.
    3. Click on Tools/Mail Merge/Create to set up your document. You can select any type of document you want to use in this mail merge operation.
    4. Click on Get Data/Open Data Source. This displays the Open Data Source dialog box.
    5. Select the Excel file you want to use as your data source (the one you closed in Step 1) but make sure the Select Method check box is selected, at the right side of the dialog box(lower center in Word 2000). (In Word 6, you should select the Confirm Conversions check box instead.)
    6. Click on Open (displays the Confirm Data Source dialog box) & select the Excel Files via ODBC (*.xls) option. Then click on OK.
    7. In the Select Table dialog box, select the data range you defined in Step 1. In Word 2000, only the first worksheet in the workbook will show, or click Options and select System Tables, and specify the worksheet you want to use. Click OK.
    8. Continue setting up your mail merge documents as you normally would.

    In summary, when merging from Excel, you have two possibilities in Office 2000.

    1. Tool/Mail Merge
    2. Get Data
    3. Select the file; activate

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •