VBA script to substitute text from an excel sheet into various word docs by filepath

Give this a try.

Copy the template file to the same directory as the excel file.

If you edit the template file, be sure to right click open, not double click, since this will create a new file instead.

I store the path/filename on the sheet instead of navigating to it since I give the user the option to navigate to file location.

Sub CreateSummary()

Dim oWord As Object
Dim WordWasNotRunning As Boolean
Dim oDoc As Object
Dim WS As Worksheet
Dim FN As Variant
Dim Phrase$
Dim Choice As Integer

Set WS = ActiveSheet

'See if Word is already running
On Error Resume Next
Set oWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then
    'Launch a new instance of Word
    Err.Clear
    On Error GoTo Err_Handler
    Set oWord = CreateObject("Word.Application")
    WordWasNotRunning = True
    oWord.Visible = True 'Make the application visible to the user (if wanted)
End If

On Error GoTo Err_Handler

oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Add(ThisWorkbook.Path & "\assessment template.dotx")

With oDoc.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[Property name]"
    Phrase$ = WS.Range("D" & ActiveCell.Row)
    .Replacement.Text = Phrase$
    .Wrap = 0 'wdfindstop
    .Execute Replace:=1 'Word.WdReplace.wdReplaceone
End With
With oDoc.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[Comment1]"
    Phrase$ = WS.Range("E" & ActiveCell.Row)
    .Replacement.Text = Phrase$
    .Wrap = 0 'wdfindstop
    .Execute Replace:=1 'Word.WdReplace.wdReplaceone
End With
With oDoc.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[Comment2]"
    Phrase$ = WS.Range("F" & ActiveCell.Row)
    .Replacement.Text = Phrase$
    .Wrap = 0 'wdfindstop
    .Execute Replace:=1 'Word.WdReplace.wdReplaceone
End With

'Parse suggested filename
Temp = Split(WS.Range("D" & ActiveCell.Row), ",")
If Temp(0) <> "" Then
    FN = Temp(0)
    oWord.FileDialog(2).InitialFileName = ThisWorkbook.Path & "\" & FN
    Choice = oWord.FileDialog(2).Show
    If Choice <> 0 Then
        oWord.FileDialog(2).Execute
        'Store the path and filename
        WS.Range("G" & ActiveCell.Row) = oDoc.FullName
    End If
End If

oDoc.Close False

If WordWasNotRunning Then
    oWord.Quit
End If

'Make sure you release object references.

Set oWord = Nothing
Set oDoc = Nothing
Set myDialog = Nothing

'quit
Exit Sub

Err_Handler:
    MsgBox "Word caused a problem. " & Err.Description, vbCritical, "Error: " _
            & Err.Number
    If WordWasNotRunning Then
        oWord.Quit
    End If
End Sub

Originally Posted by tomca

Hi David,

My company doesn't use templates

Never too late to start.

that will do a substitution of existing word docs based on the items in the square brackets only...

Ok. After you replace the square brackets (or any string) with the data, how will you find that data again? That was the point of using the template. Every time you create a new document, the brackets will be waiting to be replaced.

We could use bookmarks, but those are too easy to delete by users.

We could use formfields, but you have to protect the document in order for the fields to work correctly.

Maybe content controls as you can set a 'not delete' property for those.

Wait, are you just trying to grab the data from an already completed file? If so, you might be able to search for the title and grab the next line down.

The target word file is just a place for me to refer to the word doc wherever it is sitting on the server,

Ok, I think I have a solution for this.

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread