Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Extracting specifc cells from Excel for mailmerge in Word
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. "Suzanne S. Barnhill" wrote: 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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Extracting specifc cells from Excel for mailmerge in Word
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 "Nicole Knapp" wrote in message ... 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. "Suzanne S. Barnhill" wrote: 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 & 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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Extracting specifc cells from Excel for mailmerge in Word
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 "Nicole Knapp" wrote in message ... 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. "Suzanne S. Barnhill" wrote: 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 & 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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting specific Excel data into word | Microsoft Word Help | |||
Extracting Headers and Body text from MS Word to use in Excel | Microsoft Word Help | |||
excel--to-excel linked cells not updated in word mailmerge | Mailmerge | |||
Extracting meta-data from Active Directory (AD) via mailmerge | Mailmerge | |||
Excel cells can reference other cells to display its contents/any. | Microsoft Word Help |