Template find/replace

[NHelp]

Hello,

I have a word doc template in a specific format (table) filled in with tags. Currently the tags are also located in a large excel spreadsheet with the appropriate values in column B listed next to the tags in column a.

Is there a way in word for me to do a find.replace on my excel workbook in order for me to replace my tags with the correct values while preserving my template format?

Basically I want to tell my word doc to look at my excel spreadsheet, find the tags in the table and come back to the word doc and replace the tags with the values that are adjacent to the tags in excel.

I have not found an easy what to automate this and this document is going to be extremely large... any tips would be appreciated!

[HansV]

Are the tags the only text in the table cells, or do they form part of longer pieces of text?

[NHelp]

In column a the tags are something similar to this:

<yyy_s30_mon_-800_Message>

Then column B consists of the Tag's values (mostly numbers).

There are hundreds of tags in the output excel file--but it is just one large table with Column A being the tags and column B being the values that correspond with the specific tag.

[HansV]

Let's say you create a workbook Tags.xlsx with a worksheet Tags in which the tags and descriptions are stored.
This workbook is saved in C:\Excel.

You could run the following macro in Word to replace all tags in the active document:

Code: Select all

Sub ReplaceTags()
    ' *** Modify as needed ***
    Const strFile = "C:\Excel\Tags.xlsx"
    Const strSheet = "Tags"
    Const FirstRow = 1
    ' ************************
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim blnStart As Boolean
    Dim r As Long
    Dim LastRow As Long
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        If xlApp Is Nothing Then
            MsgBox "Can't start Excel.", vbExclamation
            Exit Sub
        End If
        blnStart = True
    End If
    On Error GoTo ErrHandler
    Set xlWbk = xlApp.Workbooks.Open(strFile)
    Set xlWsh = xlWbk.Worksheets(strSheet)
    LastRow = xlWsh.Cells(xlWsh.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
    With ActiveDocument.Content.Find
        .MatchCase = False
        .MatchWholeWord = True
        .MatchWildcards = False
        .ClearFormatting
        .Replacement.ClearFormatting
        For r = FirstRow To LastRow
            .Text = xlWsh.Cells(r, 1)
            .Replacement.Text = xlWsh.Cells(r, 2)
            .Execute Replace:=wdReplaceAll
        Next r
    End With

ExitHandler:
    On Error Resume Next
    xlWbk.Close SaveChanges:=False
    If Not xlApp Is Nothing And blnStart Then
        xlApp.Quit
    End If
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[NHelp]

Exactly what I was looking for! THANK YOU!