VBA Macro to open and replace text in Word from Excel

Peter B

I am trying to make my excel macro open a word file and replace all occurrences of a certain string with another string.

I have been able to open the word file fine but cannot replace all occurrences of a certain string with another string.

file_path = "C:\word docs\Letter.doc"
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open(file_path)

wrdApp.Visible = True

Above is my open document code which works fine.

I have been trying to use the code below for my replacement of text in the word document with no success.

wrdApp.Selection.Find.ClearFormatting
wrdApp.Selection.Find.Replacement.ClearFormatting
With wrdApp.Selection.Find
    .Text = "find"
    .Replacement.Text = "replace"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll

Any help?

EL_Loco

should you not be using the "wrdDoc" object as this is the file you've actually opened?

Just a guess atm, I'll have a go myself. brb..........

EL_Loco

it's a weird one alright. It opens the document and it highlights the first "find" string it comes across, but then doesn't execute the replacement. I took out the "with" statement to see if that would help but it didn't.

It may all boil down to excel VBA not executing this line for a word doc. I ran it under a word macro and it worked.

wrdApp.Selection.Find.Execute Replace:=wdReplaceAll

Here's the code so far, as I say, still not quite there yet.
You could try having an intermediate word file that had the macro in it and call that from your excel vba. A bit long winded but the replacement behaves itself through word at least.

Sub interactWord()
        
Dim wrdApp As Object
Dim wrdDoc As Object


Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("C:\temp\letters1.doc")

wrdDoc.Activate
wrdApp.Visible = True


    wrdApp.Selection.Find.ClearFormatting
    wrdApp.Selection.Find.Replacement.ClearFormatting
    wrdApp.Selection.Find.Text = "Find"
    wrdApp.Selection.Find.Replacement.Text = "xx"
    wrdApp.Selection.Find.Forward = True
    wrdApp.Selection.Find.Wrap = wdFindContinue
    wrdApp.Selection.Find.Format = False
    wrdApp.Selection.Find.MatchCase = False
    wrdApp.Selection.Find.MatchWholeWord = False
    wrdApp.Selection.Find.MatchWildcards = False
    wrdApp.Selection.Find.MatchSoundsLike = False
    wrdApp.Selection.Find.MatchAllWordForms = False
    wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
    'Set wrdDoc = Nothing
    'Set wrdApp = Nothing
End Sub

Seth Brundle

I haven't really looked at it but might this be of any use...
http://www.xtremevbtalk.com/showthread.php?t=125085

EL_Loco

in the visual basic editor in excel (press alt+F11)
go to Tools, References
and tick "Microsoft Word 9.0 object library"

My code above worked after that.

Peter B

EL_Loco wrote: »

in the visual basic editor in excel (press alt+F11)
go to Tools, References
and tick "Microsoft Word 9.0 object library"

My code above worked after that.

Brilliant, that worked perfectly!

Thanks