0

I need to dynamically generate word documents using text from an Access database table. The caveat here is that some of the text coming from the database will need to be modified into MergeFields. I am currently using Interop.Word (VBA Macro) and VB.NET to generate the document.

My steps so far look like this:

  1. Pull standard .docx Template
  2. Fill In template with pre-defined filler text from table
  3. Add MergeFields by replacing pieces of the filler text with actual mergefields
  4. Attach Datasource and execute Mail Merge

After testing, I noticed that I cannot simply store MergeFields into the access database as a string, they do not feed over into the actual document. What I am thinking then is creating a table of known strings that should be replaced with MergeFields by the coding.

For Example:

Step 2 will insert "After @INACTIVE_DATE@ your account will no longer be active." which will be in the database text.

Step 3 will find/replace @INACTIVE_DATE@ with a MergeField «INACTIVE_DATE». I am using Word Interop to do this, so theoretically I can loop through the document.

I wasnt able to do a "Find And Replace" from text to MergeField, so how should I go about implementing this?

Tagging VBA additionally as I am seeking a "VBA" style answer (Word Interop).

Improve this question

1 Answer 1

Reset to default
1

You've left out quite a lot of detail, so I'll go about answering this in somewhat general terms.

What you want to do is definitely achievable. Two possible solutions immediately come to mind:

  1. Replacing ranges using Find
  2. Inserting tokens using TypeText

Replacing ranges using Find

Assuming the text has already been inserted, you can search the document for the given pattern and replace it with a merge field. For instance:

Sub FindInsertMerge()
    Dim rng As Range
    Set rng = ActiveDocument.Range

    With rng.Find
        .Text = "(\@*\@)"
        .MatchWildcards = True
        .Execute

        If .Found Then
            ActiveDocument.MailMerge.Fields.Add rng, Mid(rng.Text, 2, Len(rng.Text) - 2)
        End If
    End With
End Sub

Will find the first occurence of text starting with @, matches any string and ends with @. The contents of the found range will then be replaced by a merge field. The code above can easily be extended to loop for all fields.

Inserting tokens using TypeText

While I would normally advice against using Selection to insert data, this solution makes things simple. Say you have a target range, rng, you tokenize the database text to be inserted, select the range, start typing and whenever a designated mail merge field is found, insert a field instead of the text.

For instance:

Private Sub InsertMergeText(rng As Range, txt As String)
    Dim i As Integer
    Dim t As String
    Dim tokens() As String

    tokens = Split(txt, " ")
    rng.Select

    For i = 0 To UBound(tokens)
        t = tokens(i)

        If Left(t, 1) = "@" And Right(t, 1) = "@" Then
            'Insert field if it's a mail merge label.
            ActiveDocument.MailMerge.Fields.Add Selection.Range, Mid(t, 2, Len(t) - 2)
        Else
            'Simply insert text.
            Selection.TypeText t
        End If

        'Insert the whitespace we replaced earlier.
        If i < UBound(tokens) Then Selection.TypeText " "
    Next
End Sub

Call example:

InsertMergeText Selection.Range, "After @INACTIVE_DATE@ your account will no longer be active which will be in the database text."
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

sorry for lack of detail but you answered this perfectly. In my current state, I was looking at using TypeText to insert a whole string, then pass over the words and do the replacement. Replacing as it inserts as you have shown seems like a better choice. Why do you advise against using Selection to insert data?
I've found changing the Selection unreliable in some cases, and I'm generally prefer not messing with the user's session. For data insertion, bookmarks or content controls is the optimal choice, but this would complicate the code further. I don't think using Selection in this case is any trouble, though.
Ok, I see. I am using a bookmark to mark where the content goes. This should be transparent to the user. Thanks for your help, I'll make sure to do plenty of testing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.