3

I have this Excel file that has multiple sheets that I wish to import to Microsoft Word's letter format.

Firstly I started mail merge to this excel file and selected this particular sheet/table, then I populated the letter with the necessary fields and done.

After that I wanted to mail merge to the same file again but this time selected another table with the different columns that I wish to populate the letter with that data. When I tried to do so, I got an error that said I had to remove the previously populated field and replaced it with this newly selected one.

The error message:

This merge field is used in the main document, but it does not exist in the data source. You can remove the invalid merge field from the main document. Or, you can replace it with a valid merge field from the data source.

The columns I needed are from different tables in the Excel file, that's why I retrieved the data separately.

How do I rectify the error?

0

MailMerge for multiple documents should have a single table with a row for each entry. The easiest way to do this would be to combine your sheets so you have a single table. Populating using that table will create x documents, where x is the number of non-header rows in the table.

Sample data table for multiple documents

  • Hi jmac, besides combining sheets to have a single table, are there other ways? I know that would be the easiest method but the thing is we are not supposed to combine all the sheets together but to leave them separately. – Cheryl Aug 28 '13 at 8:50
  • You can probably re-open the word template document (the one you want to merge) and select a new table -- as long as you don't save it before closing, it shouldn't be linked to the previous table in excel. This is a lot more manual work (it may be easier just to create a temporary combined table in excel and then delete it afterwards). – jmac Aug 29 '13 at 2:19
0

IMO the way jmac has suggested is probably the simplest and most reliable unless you have access to good coding resources. Or accept that you have to copy and modify your Mail Merge Main Document to merge data from each sheet.

There are other ways, but most involve programming or methods that I would say are not reliable enough for typical scenarios, and the other one will only work in some circumstances.

What can be used also depends on whether you want to combine all the data from several sheets into a single output, or whether you want separate outputs per sheet.

If you are using ADDRESSBLOCK or GREETINGLINE fields, which rely on field mapping, things are even trickier.

For "separate outputs per sheet",

a. if every sheet uses the same columns, but with different names, you may be able to use the column number instead of the name. For example, suppose your workbook contains 2 sheets, Sheet1 and Sheet2, and on Sheet1 column 1 is called namea and on Sheet2 column 1 is called nameb. Then (using Alt-F9 to look at the field names) you would need the following in your Mail Merge Main Document for Sheet1:

{ MERGEFIELD namea }

for Sheet2

{ MERGEFIELD nameb }

But

{ MERGEFIELD 1 }

should work in both cases. (This isn't documented anywhere I know).

b. you could use "Address fields" rather than "Database fields". This suffers from a limitation which I would normally think of as a showstopper (I'll describe it later), but it works like this:

Instead of using the column name ("namea") via { MERGEFIELD namea }, you use an Address Field like this

{ MERGEFIELD "First Name" \m }

This uses a standard set of field names which you "match" (or "map") to the column names in your Excel sheet. You can pick these fields from the full Insert Field dialog box by selecting the "Address" option instead of the default "Database" option, and you can remap the fields using the Match fields... button in that dialog box. You need to check the Remember this matching for this set of data sources on this computer box at the bottom. Then, when you switch from one data source to another, Word should remember the correct mappings.

That's the theory, but in practice I think it is an unworkable approach in most scenarios. That's because Word only remembers (in the Windows Registry) one mapping for each Database name. So if you have a field called namea in one sheet and you need to map that to First Name, but in another sheet you need to map namea to Last Name, you can't do it (AFAICS). Worse, if someone changes the mappings, existing merge documents could end up pulling in the wrong data and the user would have no warning at all.

Approaches that involve programming include the following:

c. you have code (e.g. VBA code) which collects and saves the names you need in each sheet, then inserts the correct names for each MERGEFIELD field. You could use nested fields for that, in effect doing your own mapping. Using the example in (a) again, you could for example have a document variable called "thename", use VBA to populate it either with namea or nameb, and use the following nested field:

{ MERGEFIELD { DOCVARIABLE thename } }

(and this only works if you can get your VBA to make the substitutions before Word tries to verify the field names)

d. (if you require your chosen sheets to be merged in a single merge, not one merge per sheet), you either write code to automate Excel to combine the sheets using the Excel object model, or you get Excel (or Access) to do it by setting a up a UNION query to combine the sheets. Using our example again, the UNION might look like this:

SELECT 1 AS [thesheet], namea AS [thename] FROM [Sheet1$]

UNION

SELECT 2, nameb FROM [Sheet2$]

Although you can set up a query along these lines in Word VBA, Word has a limitation of 511 characters in its MailMerge queries that you are highly likely to exceed. You can set up a query in a new Excel Workbook to do something similar, but I do not know whether there are query length or other limitations in that product. Also, you will probably lose any "memo" data (longer than 255 characters), so again, this is only an option for some types of data source.

  • You can loop through code to create a MailMerge in Excel. With objTemplate set as the Word Document I want merged in to, here is the code: objTemplate.MailMerge.OpenDataSource Name:=(current excel file path & name), Connection:="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=(current excel file path & name)" , SQLStatement:="SELECT * FROM `(sheet name)`" – jmac Aug 29 '13 at 2:24
0

Have you tried going in to "Select Recipients" and selecting the source sheet again? If not, do that and select the correct worksheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.