Mail Merge comes out blank - data is not passed across

[MHUK]

Hi
I am urgently trying to print out some labels using mail merge. All the data is in 6 columns in Excel. I do the mail merge procedures as normal which are
- Save and close Excel document with all the data
- Open up new word document and go to Tools and Mail Merge
- Go to Create then select Mailing Labels
- Click Active Window
- Then select Get Data, Open Data Source and find the excel doc with all my data and double click this.
- Then a box comes up saying "Named or Cell range" and in a box below it says "Entire Spreadsheet". I click Ok. I would have preferred it if it gave an option to use a 'Print Area' - does anyone know how to do this?? Also it will not allow me to actually enter a cell range in the normal way such as A1:F70...does anyone know if this is the correct format?? - (I have set up and saved a print area in the excel document as there are a few columns of data I do not wish to come out on the labels)
- I then click "Set Up Main Document", and it then prompts me to select the type of label to use. I do this and just click OK.
- It then comes up with a box saying "Create Labels".
- There is a button which says "Insert Merge Field" which I click and it shows 8 options (I'm not sure why it is 7 as the print area in Excel only includes 6 columns) - the options are "Automerge Field", "Automerge Field 1" and carries on to "Automerge Field 7". I select all of these and they appear below in a white area called 'sample label'.
- I then click OK and then click the button called 'Merge'.
- A document is created that shows where the label sections are but no data is in them.

I have tried several times over without success. I have tried using the Excel document with and without a print area but makes no difference. I am sure the problem lies with where it asks me to select 'Entire Spreadsheet'. This is very limited as I do not wish to select the entire spreadsheet, only a range of cells or ideally the print area I have set.

Please can anyone me?

Thank you for any help. I really appreciate it.

MHUK

 

[jfhewitt]

The easiest method is to put your data in an Excel "database" or list. First row contains column headings, which become merge fields. Define the entire list as a range named "database". When you refer to your data source, select "database". This makes it possible to add data to your list using Data/Form...and add new records to the database.

 

[sawedoff]

Be sure you have headings in the top row of your Excel table (Name, address, etc.). Then in Excel, name the range you want to use. Then when you open the datasource, you will have the option to select only the named range. Then also, when you choose your fields, they will have the column names instead of the AutoMergeField1, etc. Having the named range should eliminate the problem.

Sawedoff

 

[MHUK]

Hi thank you both for your replies. How do I 'name a range of cells'? Do I highlight the column headings and rows to include then name it somehow? I have been doing something similar except when I highlight the headings and rows I set it as the print area. Then I try to refer to the 'print area' as this being the datasource to use, except it only comes up with a default saying "Entire Spreadsheet" so trying to set the print area and then select this as the range to use does not work. However if I am able to "name" the range of cells to use, and then use this as the datasource it may work. Firstly please can you tell me how to - name the range of cells to use, and then when I go to get the data do I literally just type this name instead of going for the default option of 'entire spreadsheet'?

Thank you again for your help.

 

[Dreamboat]

MHUK, from what I'm hearing, there's no need for you to set a named range. If you have 255 columns and only want to use one, then just insert one merge field in your document. It's not like the operation will be more efficient because you choose a named range of one column...

The only thing I suggest is that all cell BELOW your current data be CLEAN. If they're dirty (you had data in them, but deleted it and didn't Edit-Clear-All on the cells), then it could think that blank rows that are below your actual data are data too, and returning blanks.

See how to create a proper Excel database:

http://www.theofficeexperts.com/excel.htm#FileLayoutForDatabases

and scroll down a bit further and look at the paragraph JUST ABOVE "too many formats" to see how to ensure your cells are clean.

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[jfhewitt]

You can name a range in two ways: 1. Select the cells you want to define as a range, say from A1 to M10. Go to the cell address location (the little window just above cell A1; if your insertion point is in cell A1, the window will show "A1"). Click on the cell until the address turns blue, then type the range address. Hit return. This will assign the range name to the selected block of cells.
2. Select cells, go to menu, Insert/Name/Define.