0

Despite my lack of coding experience and using code stolen from various other posts, I have managed to cobble together an Excel VBA that opens up an existing Word document and then customizes it using values in specific cells in my excel workbook. So for example, the VBA searches the Word document for specified phrases like "[Client Name]" and "[Contract Date]" (and so on), and then replaces them with the specified Excel cell values such as "Bob's Burgers, Inc." and "Jan. 9, 2011" (as the case may be). It works great except the "[Client Name]" in each of the Word document's four Headers are completely ignored by the find/replace process.

In short, I am looking for the snippet of code that would modify the existing macro so that it finds/replaces in the headers/footers at the same time as the main body of the document. If this isn't possible, what would the vba code to add an additional find/replace macro to find each instance of "[Client Name]" in each header and replace with the needed custom phrase from Excel?

Here is the find/replace part of my macro as it currently reads:

    Set WA = CreateObject("Word.Application")
    WA.Documents.Open (pathh)
    WA.Visible = True

    For oCell = 1 To 44
        from_text = Sheets("ReplaceLIST").Range("A" & oCell).Value
        to_text = Sheets("ReplaceLIST").Range("B" & oCell).Value
        With WA.ActiveDocument
            Set myRange = .Content
            With myRange.Find
                .Execute FindText:=from_text, ReplaceWith:=to_text, Replace:=2
            End With
        End With
    Next oCell
Tommy214
  • 1
  • 1
  • You might find that using Microsoft Access reports as a better option to the whole process. My guess is that this will open up a lot of doors for other projects. – KeithL Aug 06 '19 at 17:50
  • Have you looked at [this answer](https://stackoverflow.com/a/11675527/4717755) or [this website](https://wordmvp.com/FAQs/Customization/ReplaceAnywhere.htm)? – PeterT Aug 06 '19 at 20:15
  • PeterT: This link appears to have the answer I needed, so I copied the VBA procedure to which you linked but I now receive an error message reading "Compile error: argument not optional" with the ".Find" highlighted in the first instance of of the line "With myStoryRange.Find". Any thoughts as to what I'm doing wrong? Does it need to be Set as something, for example (and if so, what?). I am brand new at this and am not an IT person - just a guy doing his best! – Tommy214 Aug 12 '19 at 21:17

0 Answers0