+ Reply to Thread
Results 1 to 9 of 9

Create Multiple Word Docs from Excel Data

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Create Multiple Word Docs from Excel Data

    Good Morning/Afternoon/Night,

    I'm looking to create a macro that will take data from Excel, copy it into a Word document (I have a template already made up), and then save the word document. I have multiple lines of data so I will need to create multiple word documents.

    Here is an example of what I'd like the macro to do:

    1. Open the Word template I have created
    2. Copy the Data from excel to word: ie. Cell B2 in Excel will have a name, I need the name copied from excel and pasted into word about 10 spaces down after the text that says "Name". C2 data will say have an age, I need hte age copied from excel and pasted into word about 12 spaces down after the text that says "Age". Etc...
    3. After all the data has been populated, I would like the document to be saved as a combination of two of the fields in that were populated in Word.
    4. The Word document will then be closed.

    5. Steps 1 through 4 are repeated with the next line of excel data.


    I know this is pretty detailed but if someone could help me I'd greatly appreciate it!

    Thanks in advance!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Create Multiple Word Docs from Excel Data

    I think it would help a lot if you posted a sample workbook and a sample word template.

    Regards
    Last edited by TMS; 05-06-2011 at 03:05 AM.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Create Multiple Word Docs from Excel Data

    You'd better make a mailmerge document of your Word document and do the whole business in Word instead of Excel.
    Mailmerge is explicitly designed for this kind of situation.



  4. #4
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Create Multiple Word Docs from Excel Data

    Attached are the documents I use:

    The column headings match the fields in Word.

    Let me know if you have any questions.

    Thanks!
    Attached Files Attached Files

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Create Multiple Word Docs from Excel Data

    Extract the files in the attachment in the same folder.
    Open the Word-document.
    Click on the button in the Worddocument.
    Attached Files Attached Files

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Create Multiple Word Docs from Excel Data

    See attached Mail Merge template and sample output from your test data.


    Regards
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Create Multiple Word Docs from Excel Data

    Hi again,

    Mail Merge seems like it would be the best option. However, I do not want to mail these documents out to anyone. I would just like to save them to my computer. Will this be a problem?

    Thanks!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Create Multiple Word Docs from Excel Data

    You can preview the mail merge out to a new document. That was the example that I provided ... a new document on my computer with all the "letters".

    Each lettter will be on a new page.

    Mail Merge is a historic term for merging database entries into a standard template ... which you would have put in the (physical) post. Not really anything to do with "mailing out".

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Create Multiple Word Docs from Excel Data

    I hope you don't take the Carriage Return (CR, vbCR) literally either.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1