Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Populate Word from Excel

    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

  2. #2
    Plutonium Lounger Medico's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    12,631
    Thanks
    161
    Thanked 936 Times in 856 Posts
    Just highlight the cells you wish to move to word, Ctrl+C, move to excel, Crtl+V, voila!

    PopulareWordFromExcel.jpg

    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)
    Last edited by Medico; 2011-07-27 at 15:52.
    BACKUP...BACKUP...BACKUP
    Have a Great Day! Ted


    Sony Vaio Laptop, 2.53 GHz Duo Core Intel CPU, 8 GB RAM, 320 GB HD
    Win 8 Pro (64 Bit), IE 10 (64 Bit)


    Complete PC Specs: By Speccy

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I don't want a copy/paste ... I want the Word document automatically populated in various locations with cell data from the Excel sheet.

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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?

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by kweaver View Post
    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.

  7. #7
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    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
    Last edited by Gary Frieder; 2011-07-29 at 11:30.

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by Charles Kenyon View Post
    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?

  9. #9
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    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
    Last edited by Gary Frieder; 2011-07-29 at 11:30.

  10. #10
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    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
    Last edited by Gary Frieder; 2011-07-29 at 11:30. Reason: Removed links - see Lounge Rule #12

  11. #11
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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'


    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.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •