+ Reply to Thread
Results 1 to 4 of 4

Macro to merge Excel information and Word documents?

  1. #1
    Registered User
    Join Date
    09-06-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Macro to merge Excel information and Word documents?

    Hi,

    I used these forums a couple of years ago and found the people here to be totally invaluable - and I appreciated it so much, I'm back here again with another quandary!

    I'm trying to work out how I can combine a single (Office 2010) Excel spreadsheet with a pair of Word documents (A and B) - and the structure is as follows:

    The Excel workbook contains the names of individuals and a host of other information about them, including addresses, other personal info, and a unique code for a specific paragraph from a paragraph 'bank' held in Doc A - although each paragraph in the bank has multiple "*********" placeholders where an individual's name should go (so the information is personalised - this has historically been done through Find/Replace, but I'm trying to find a way to automate it).

    Doc B contains a series of A4 forms which contain all the names in the XLS workbook (in the same order) - these forms are, however, empty of information, and need to be filled with the relevant paragraph created through combining the XLS book and Doc A.

    Is it possible to create a macro/merge to do the following:

    1. Look up the unique code for each individual in the Excel workbook, and create the appropriate paragraph somewhere, inserting the name of the individual in the "*******" placeholders.
    2. Once it has done this - merge each complete paragraph into Doc B in the appropriate A4 form (there are normally around 20-40 separate pages in each document)?

    It's possible that is most effectively done as two separate operations (I just don't know!) - but I'm trying to reduce the manual element of the process as much as possible! I've attached extremely basic examples of the XLS book and both Word documents to show you what I mean - what the input is, and what the output should look like, after a fashion. Is this possible to recreate easily?

    I'll confess to being a little more up-to-date with Excel than with using Mail Merge, to which I am fairly new! However, any suggestions would be massively appreciated.

    (As a note, if it is easier to make the paragraph 'bank' in Doc A an Excel spreadsheet, I am more than happy to do so!)
    Attached Files Attached Files

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

    Re: Macro to merge Excel information and Word documents?

    You need only 1 Word document.
    Use Documentvariables for texts; mergefields for the relation with the Excel-file.
    You can alse use IF-fields in the Word Document to select specific text, dependent upon the value of certain fields in the Excel file.



  3. #3
    Registered User
    Join Date
    09-06-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Macro to merge Excel information and Word documents?

    Hi, snb,

    Many thanks for the quick reply - and I'm going to look a bit more stupid again in a second, but I've never used document variables before. How

    Am I right in thinking I'd need to put all the paragraphs and their codes into another Excel worksheet to do a VLOOKUP on, then do a Find/Replace to replace the ******* placeholders with the unique first name in each row before I can even think about exporting to the report Word document?

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

    Re: Macro to merge Excel information and Word documents?

    No.
    For some of my examples look here.
    If you register there you can download 3 examples (Word-merge-files & excelfiles). Don't bother the language, the files will be self-evident.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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