Using excel to run Find/Replace in Word

[imru]

Here is my situation: I'm using excel as a server to an automated report generator (in word). I'm trying to do a mass find/replace in word...based on values in excel. For instance

On sheet "test" in cell "A1" there is a name. I need to take that name and find all the instances of "<NAME>" in a named Document (Doc1) and replace <NAME> with the name from excel.

Set objWord = CreateObject("Word.Application")

Set Doc1 = objWord.Documents.Add(path & file)

Doc1 is the open word doc that was automatically created based on the excel book.

Please let me know if you need anymore information. This is simple in Word, I'm just not sure how to do it from Excel.

[mdmackillop]

Option Compare Text
Option Explicit

Sub WordReplace()

'Remember: this code requires a reference to the Word object model

Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim fName As String
Dim i As Long, Rw As Long
Dim f

ChDir ActiveWorkbook.Path

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Filters.Add "Word", "*.doc", 1
.Show
On Error GoTo Exits
fName = .SelectedItems(1)
End With

Set wdDoc = wdApp.Documents.Open(fName)

With wdDoc.Content.Find
.Text = InputBox("Term to replace", , "<Name>")
.Replacement.Text = Range("A1")
.Execute Replace:=wdReplaceAll
End With
wdApp.Visible = True
Exits:
End Sub

[imru]

When I try to run the following code based on what you gave me..I'm getting an error...

Option Compare Text
Option Explicit
Dim objWord As Word.Application
Dim wdDoc1 As Word.Document, wdDoc2 As Word.Document
Dim wb As Excel.Workbook

Sub moveToWord()

Set wb = ActiveWorkbook

path = "C:\Automation\"
file = "test.dot"

Set objWord = CreateObject("Word.Application")

Set wdDoc1 = objWord.Documents.Add(path & file)

''''''''''''''''
' Find/Replace
''''''''''''''''
With wdDoc1.Content.Find 'ERROR HERE
.Text = InputBox("Term to replace", , "<Name>")
.Replacement.Text = "wowzers"
.Execute Replace:=wdReplaceAll
End With
''''''''''''''''
objWord.Visible = True
Set objWord = Nothing
End Sub

"Run-time error '91':
Object variable or With block variable not set"

However..it is set. And i can access wdDoc1 and use it other places in my subs.

[mdmackillop]

Your code works fine for me. What version of Office are you using?

[Ken Puls]

I'm getting an error in 2003 as well, actually. Let me have a play for a minute. :)

[Ken Puls]

Okay, I modified this slightly.

I was first running in to issues because the document wasn't opening. I didn't realize that I'd mistyped my test path. So that gives me the big question... is the file you want to open really .dot or is it supposed to be .doc.

If it is for opening a template, I'd tend to use this variation of the code. (I moved your variables inside the code, added Path and File declaration lines and modified the app/document creation section.)

Sub moveToWord()
Dim objWord As Word.Application
Dim wdDoc1 As Word.Document, wdDoc2 As Word.Document
Dim wb As Excel.Workbook
Dim path As String
Dim file As String

Set wb = ActiveWorkbook

path = "C:\Automation\"
file = "Test.dot"

Set objWord = New Word.Application
With objWord
.Visible = True
.Documents.Add path & file, False, 0
Set wdDoc1 = .Documents(1)
End With

''''''''''''''''
' Find/Replace
''''''''''''''''
With wdDoc1.Content.Find 'ERROR HERE
.Text = InputBox("Term to replace", , "<Name>")
.Replacement.Text = "wowzers"
.Execute Replace:=wdReplaceAll
End With
''''''''''''''''
objWord.Visible = True
Set objWord = Nothing
End Sub

HTH,

[mdmackillop]

Thanks Ken.
That works for me too, and hopefully solves the problem.

[mdmackillop]

Do you need this line for the code to run?
.Visible = True

[Ken Puls]

No, not at all. You need that line to make the new instance of Word visible. Actually, I just realized that this was already at the end:

objWord.Visible = True

Basically, if you don't set the app visible, and you don't quit it at the end of the procedure, you end up with an invisible instance running on the system. I just like to set my app visible as early as possible so that I can make sure it works when I'm stepping through it. ;)