Can MS Word load dynamic data (for titles, author, address, phone number, etc.) from fields in an Excel file?

Question

I don't want to roll my own tool in VBA, as it would be too fragile to code quickly (and hand off to other users) or I'd end up coding way to much for such a simple end-result.

Is there's a nice built-in tool for filling out some kind of form and having it populate the word document?

If the data needs to change, you can go to this central location and make edits there instead of trying to find all the places it's referenced in the document (like having to go into the Header / Footer, or knowing that the first / last pages have a different setup than all the rest, etc.)

This is for manuals where the content, author, names and titles, change from one project to the next, but the format follows the same template.

Answer 1

To start a Form Template, or Mail Merge: Word 2016

Mailings Tab → Start Mail Merge → Step by step MM Wizard
Choose: Letters, [Next]
Choose: Use Current Doc, [Next]
Choose: Type a New List →

CREATE → customize columns → Add/Delete/Rename



Save: Microsoft Office Address Lists (*.mdb) -- this is the dynamic list for the form/template.

[Next] Write your letter. Make your template now.

Now to insert the dynamic "fields":

Mailings Tab → [Insert Merge Fields] Insert the "field" anywhere the new page will change from project to project.

This is how it looks: image (click image to watch/navigate animated GIF)

Microsoft Support - Word (Mail Merge): LINK
Microsoft Support - Mail Merge using Excel: LINK

Answer 2

Not my cleanest code, but it gets the job done.

SOLUTION

This will load a series of key-value pairs from a text file into the Word Document's ActiveDocument.Variables list. These Variales are stored persistently in the Document, but I chose to break them out to avoid duplication and orphaning of variables (see sledge hammer deletion in code).

The variable values can then be displayed using Fields throughout the Document by referencing the DOCVARIABLEs by name.

Code below has no error handling, but does some basic parsing checks to avoid crashing. Not tested very extensively though, so do your own due diligence.

USAGE:

Place code in the Document On Open Event handler. It will create a file with the same name as the Word Document, but with a ".config" extension.

On creation. the config file will have all current ActiveDocument.Variables written to it to ensure you don't open a blank file accidentally if you copy the Doc and forget or don't know about the .config file.

If the config file exists, then all doc variables are deleted from memory and the ones in the config are loaded into memory. You can change the value of a variable in the config file and it will be updated throughout the document the next time you open it.

Option Explicit

Private Sub Document_Open()

    'Dim
    Dim i As Long
    Dim Folder As String
    Dim FileName As String
    Dim FullPath As String
    Dim FileText As String
    Dim Item As Variant
    Dim Table As Scripting.Dictionary
    Dim Key As Variant

    ' Open or Create Config File
    With New FileSystemObject

        ' Setup Path
        Folder = ThisDocument.Path & "\"
        FileName = .GetBaseName(ThisDocument.Name)
        FullPath = Folder & FileName & ".config"

        If (.FileExists(FullPath)) Then

            ' Sledge Hammer Cleanup of Document Vars, avoids memory bloat by synchronizing variables with .config file
            For i = ActiveDocument.Variables.Count() To 1 Step -1
                ActiveDocument.Variables.Item(i).Delete
            Next i

            ' Open / Read
            With .OpenTextFile(FullPath, ForReading, False)

                ' Get File Contents
                If Not (.AtEndOfStream) Then
                    FileText = .ReadAll
                End If

                .Close
            End With
        Else

            ' Create / Write
            With .OpenTextFile(FullPath, ForWriting, True)

                ' Write One Key-Value pair per line
                For Each Item In ActiveDocument.Variables
                    .WriteLine (Item.Name & ":=" & Item.Value)
                Next

                .Close
            End With
        End If
    End With

    ' Parse Config Text for Runtime
    Set Table = ParseVariables(FileText)
    For Each Key In Table.Keys
        ActiveDocument.Variables(Key) = Table(Key)
    Next

    ' Update All Fields in Document
    ActiveDocument.Fields.Update

    ' Save File so user does not get nuisance prompts
    ActiveDocument.Save
End Sub


Private Function ParseVariables(text As String) As Scripting.Dictionary

' Dim
Dim i, n As Long
Dim Lines As Variant: Lines = Split(text, vbCrLf)
Dim VarTable As New Scripting.Dictionary
Dim Key, Value As String

    ' Loop
    For i = LBound(Lines) To UBound(Lines)

        Debug.Print ("Lines(" & i & ") = " & Lines(i))

        ' Find the ":=" delimiter in each line that splits the variable name from its value
        n = InStr(1, Lines(i), ":=", vbBinaryCompare)

        ' Escape if not delimited
        If (n > 0) Then

            ' Extract Key before ":=" and Value from after ":="
            Value = Mid(Lines(i), n + 2)
            Key = Trim(Mid(Lines(i), 1, Len(Lines(i)) - Len(Value) - 2))

            ' Escape if either Key or Value are empty
            If (Len(Key) > 0) And (Len(Value) > 0) Then
                VarTable.Item(Key) = Value
            End If
        End If
    Next i

    ' Return
    Set ParseVariables = VarTable

End Function