How do I Auto Merge / customise multiple folders of word Documents to a single data file

Posted on 2009-04-20
Last Modified: 2012-08-13
We have a library of industry best practice (MSword) documents in multiple folders.  All up about 300 letters, forms, checklists and resource documents.  2 master "index" documents contain the lists with hyperlinks to the individual documents.  The library is constantly being refined.  

We want to package the bundle of documents to distribute to other partners of our business network (who all have different company names, logo's, staff contact details etc) in such a way that they can fill out a simple form that will customise all of the docs to suit their businesses.  

The first thing we need is an extended Find & Replace tool to run through through our library to replace our existing data (and logos) with dummy data (or merge fields) and then we need to create a form or excel list where partners can enter their own business specific details and press the "DO IT" button to replace the dummy data across the entire library with their own content.

The solution must support "bottom up" hyperlinking so the folder works inside its own directories without referring to a drive location (so it can be copied from CD or unzipped to any network location)  and include customised data located in headers and footers of documents.

The solution needs to be able to "merge" a company logo into headers and photos of staff members into the document space. (we can instruct our partners to put pre-sized boss.jpg, staff1.jpg, staff2.jpg, etc images into an IMAGES folder in the directory easily as they are small companies with few staff)

Ideally, upon deployment of the master folder onto a server, partners will be able to drop thier logo and images into the images folder, fill out the form, and auto-customise the entire library with one click.  EASY!

Any suggestions appreciated, and happy to award points to any assisted solutions.
Question by:DexterIT
    LVL 4

    Expert Comment

    You could custom make it by doing a file search. See attached code for a macro example. This example returns the filecount, and uses a reference parameter to return an array with all the files.

    For relative paths, you could use the ThisDocument.FullName value. This is the filename of the file containing the executing macro. Presuming you documents are all in subfolders of the folder containing the macro document, extract the path (see attached code) and use it in the previously mentioned FileSearch macro.

    Then loop them all to open them one by one. For each document you open you can use Bookmarks to find positions where something neat needs to happen. Bookmarks are unique locations within a document and can be easily referenced by from macro. To add bookmarks to your documents. Select the position and press Ctrl + Shift + F5, give it a name and submit. Then do to that bookmark what you want to do. You can access bookmarkrange from macro using "ActiveDocument.Bookmarks("YourBookmarkName").Range".

    You could use smart naming conventions to automaticly detect what kind of data is expected for it.
    IE: TXT_MyBossesName1, or IMG_MyBossesPicture1. The 3 character prefix will allow you to create a macro to detect what data is expected. Then use the rest of the name (minus the trailing number) to fill it with a specific value used in you fill-in form.

    ' Find Files
    Function FindFiles(ByVal AFolder As String, ByVal AFilter As String, ByRef AFileList As Variant, Optional ByVal AIncludeSubFolders As Boolean = True, Optional ByVal ARelative As Boolean = False) As Long
      Dim i As Integer
      Dim s As String
      With Application.FileSearch
        .LookIn = AFolder
        .SearchSubFolders = AIncludeSubFolders
        .FileType = msoFileTypeAllFiles
        .FileName = AFilter
        If .Execute(msoSortByFileName, msoSortOrderAscending, True) Then
          FindFiles = .FoundFiles.Count
          ReDim AFileList(FindFiles - 1)
          For i = 1 To .FoundFiles.Count
            s = .FoundFiles(i)
            If ARelative Then
              s = Right(s, Len(s) - Len(AFolder))
            End If
            AFileList(i - 1) = s
        End If
      End With
    End Function
    ' Extract Path
    Function ExtractFilePath(ByVal APath As String) As String
      Dim Result As String
      Result = Left(APath, InStrRev(APath, "\"))
      If Result = "" Then Result = Left(APath, InStrRev(APath, ":"))
      ExtractFilePath = Result
    End Function

    Open in new window

    LVL 1

    Author Comment

    Thanks Xcone.

    Most of that is a bit over my head, but I think I follow what you're getting at.  I've never used macros before so I'll need to do a few tutorials to get a basic understanding.  If I'm reading you right, this method will require me to manually insert bookmarks into all of my 300 files which I'm hoping to avoid.  If I have to edit all the docs, I can use a simple "Paste Special" link method to force the documents to draw content from a list when they open, although I haven't been able to make this work with the logo.

    is there an extended find replace tool that will replace my custom data (Mr. Joe Boss, Phone numbers etc) with merge fields or even those bookmarks?
    LVL 4

    Accepted Solution

    Adding bookmarks is a one time action only. You setup all your documents once by inserting bookmarks. When it's done, you can run the macro code as many times as needed without manually editing bookmarks. (make backups!!).

    Ofcourse bookmarks are just 'a' way to make this possible. You just need somekind of reference point within your document to let your code know where the data is supposed to be put. This could be fields (ctrl + F9), textual tags ( '<MyTextProperty>') using a search in your document (possibly slow!) or bookmarks. Bookmarks are designed to be just that, reference points :-). If you allready have some kind of reference points setup in your document, feel free to use them.

    You could try mergefields instead of bookmarks. Mergefields allow duplicates, and have the ability to automaticly merge with a datasource. This will save you the bookmark text replace trouble (but will add mergefields ..). However, it only works on 1 document, so you still have to loop through all your documents. AND it won't work with images, so it still needs a bit of manual work. So I don't think mergefields will work well for you.

    I'm afraid Word does not have anything on it's own that will suit your needs, so macro's is the easiest alternative. Remember you can record macro's. If you don't know to do it in code, just enable record, perform it manually and stop recording. Good chance the recorded macro contains (a part) of the code you need.

    As for the search/replace:
    Again Word doesn't facilitate on the exact thing you need. But you can easily reach the same results by using the 'Selection.Find' object in macro's. 'Selection.Find' is the macro equivalent of the search/replace box in Word itself. But through macro, you have the ability to do with the found result what suits your need.

    I'd like to explain more, but I really have to go now (work work ....). Please let me know if you need more help.
    LVL 1

    Author Comment

    Thanks Xcone.  We're in the GMT +8 zone so it will often take a while for me to pickup posts.  I'll do some more work overnight and get back to you.
    LVL 4

    Expert Comment

    It's been silent for a while. Any progress?

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    This article provides the solution to a question ( posed here at Experts Exchange. The asker of the question has many JPG images in many folders, and all of t…
    Online collaboration is quickly becoming embedded in the workplace, and its benefits are tangible. See what the current landscape looks like and what the future holds for collaboration tools and the future of work.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now