[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2009-04-20
Medium Priority
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
  • 3
  • 2

Expert Comment

ID: 24186591
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


Author Comment

ID: 24189817
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?

Accepted Solution

Xcone earned 2000 total points
ID: 24191436
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.

Author Comment

ID: 24191612
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.

Expert Comment

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

829 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