Extracting specifc cells from Excel for mailmerge in Word

[Nicole Knapp]

I have a document that is a certificate. I have a spreadsheet that is an
attendance sheet for a training session.

The Word document is a mail merge which pulls data for "credit hours",
"course", "Name", "Location" and "date". This is pulled via SQL from a
table created separately based on the spreadsheet.

My spreadsheet has header information in the first 11 rows that basically
stays the same between each - the "hours", "Course", "Location" and date are
all in the same cells each session. The names are located in a specific
column below the header

I would like to eliminate moving the data out of the spreadsheet to the
table and have Word and Excel interact directly to create certificates for
each attendee.

If I'm understanding you correctly, you should be able to use the Excel
sheet as a mail merge data source.

Thanks.

That's what I thought, but it wants to use the Excel sheet as a table & Mail
Merge won't correctly pull it in.

I need to use information from:

4 cells (A5, E5, A7, E7) This information is static for each certficate.
1 column (B13:B32) - This information is the name that changes for each
certificate.

Thanks,
Nicole

 

[Doug Robbins - Word MVP]

You are either going to have to use a Visual Basic "roll-your-own"
alternative to mail merge or set up another sheet in the Workbook that
references the cells in what you have now starting from the twelfth row.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

Try this:

1. In your Excel sheet, make sure that there is one row above your names
column containing the column name(s) (e.g. "Student"
2. Select the data in that column from the new cell containing the column
hading down to the last data item. Use Excel Insert|Name|Define to define a
"range name" (for example, "Student")
3. Keep the spreadsheet open. In Word, start creating your mail merge main
document.
4. Select the data source - if you are using Word 2002 or later, you should
see a list of "tables" in the workbook, including "Student" - select that.
Insert the student mailmerge field in the usual way.
5. Then in Excel, select cell A5 and Edit|Copy, then in Word, Edit|Paste
Special|Paste Link, and select the format you need - I would probably choose
unformatted text. Then repeat for the other 3 cells you want to include.
6. Save your Word file, and do some tests: probably
a. a test merge to an output document.
b. close the Word document, change some of the data in the 4 "special"
cells in the Excel document, then re-open the Word document. You may see the
"SQL" question that MailMerge asks. Depending on your Word settings you may
also see a prompt to update the links - say yes. (If you don't see that, use
ctrl-A to select the document when it is open and press F9 to update the
fields. You should see the updated values
c. close the Word document. Change the Excel sheet again and save and close
it. Re-open the Word document - this time you will probably see the same two
prompts as in (b), but Excel will try to open the sheet and will say it is
already locked (because Word has already opened it as a data source). I
don't think there's much you can do about that except accept "open
read-only.

Apart from all those prompts you have to respond to, the other thing you
have to do is recreate the range in Excel each time you change the number of
names in the Student column - reselect the heading cell and cells containg
data, and re-insert the range name. An Excel person may know a simpler way.

In the end, that may well be more complicated and less reliable than the
method you already have. It might be possible to simplify it in various
ways, but in the end your existing, general approach of having some
automation to create database records is probably the approach that you
actually need.

Peter Jamieson