Populate Word from Excel

Topic

New Reply

I can’t believe there isn’t a way other than a mail merge to populate various portions of a Word document with cells from an Excel sheet. But, I can’t for the life of me figure out how — assuming this is possible.

Thanks in advance.

Kevin

Reply | Quote

Replies

Just highlight the cells you wish to move to word, Ctrl+C, move to excel, Crtl+V, voila!

28521-PopulareWordFromExcel

The left side shows an Excel spreadsheet. I highlighted several cells, opened Word and pasted . (Sorry I had inverted the cell color to make it easier for me to read, hence white on black)

Reply | Quote

I don’t want a copy/paste … I want the Word document automatically populated in various locations with cell data from the Excel sheet.

Reply | Quote

I don’t want a copy/paste … I want the Word document automatically populated in various locations with cell data from the Excel sheet.

If you want specific data inserted into, say, table cells, VBA is the way to go, If you want rectangular areas of your worksheet imported, you could try inserting a Link using Paste Special, or maybe INCLUDETEXT with a named range (“bookmark” in Word-speak). Note: that was based on a little searching; I never do that myself.

Reply | Quote

There is a thing called office automation where, through code, you can open word from Excel or Excel from word and populate one from the other. You would need to use Visual Basic for Applications, to do that, though.

Reply | Quote

Mail Merge is the a way to do it without programming. VBA, as I said, can be used too.
Anyway, what is wrong with mail merge?

Reply | Quote

To continue a bit of what has already been said, what is wrong with using mailmerge? People think and talk about mailmerge for producing multiple documents from a data set (a mass mailing), but it works very well for producing a single document based on data in Excel; you just have to name your Excel columns and put an identifier in your rows. I use it regularly to prepare custom legal documents. I link to the data set and call up a particular record; I never actually run the merge; just customize the document with the merged data. If I’m saving the document, I change its type to a regular word document to freeze the data in place.

Charles Kenyon
Madison, Wisconsin

Reply | Quote

I link to the data set and call up a particular record; I never actually run the merge; just customize the document with the merged data.

How does that work? Are you inserting merge fields or link fields?

Reply | Quote

I insert merge fields.

Which version of Word are you using? I’ll try to walk you through it.

I create a template with the text and interspersed merge fields in it. The template is attached to the data set.

A new document is created based on this template. That document is automatically connected to the same Excel data as the template.

I view this through the merge preview having picked the record that contains the data I need. I change it from a merge document to an ordinary word document to break the link to the Excel spreadsheet. The data remains filled in. I then save the document. I then may customize it to add additional language or delete portions that don’t apply. Finally, I resave it and print it out.

I have hundreds of records in the Excel spreadsheet. I organize them by last name, first name of the client. Word allows you to search for a particular record (Select Recipient). If needed, you can step through the records one at a time.

When I want to prepare a similar document for another client I first make sure that the client’s data is in the Excel worksheet, then create another new document based on the template.

It is a bit of work at first to set up, but once you do, you can pump out documents populated with your data on demand very quickly and easily.

Charles Kenyon
Madison, Wisconsin

Reply | Quote

To clarify, you never run the merge. You don’t end up with a merge result document but rather with a merge ready to run in preview mode with just one record.

Charles Kenyon
Madison, Wisconsin

Reply | Quote

I do this all the time with dates, numbers, etc. The way I do it is as follows:

[*]Have an excel cell that holds the value I want to put in the word doc
[*]Give that cell a range name
[*]copy the cell
[*]flip over to word and use ‘paste special’ with the following options

[*]paste link
[*]paste as ‘unformatted text’

[/LIST]

This adds a field that is linked to the excel file / tab / range name. If you Alt-F9 (reveal codes) it should look something like this:

{ LINK Excel.Sheet.8 “C:\blah blah path\blah\blah\workbook.xls” “Sheet1!R11C3” a t }

Changing the value in the workbook and refreshing the link will update the word doc.

Reply | Quote