PDA

View Full Version : VBA replace not working



GoDuncan
02-28-2011, 10:09 AM
I have a puzzling problem that is giving me fits and hope someone can help me with. I have an Excel program which adds Word documents in a loop. During the loop, a find and replace is needed to change the heading text. Here's the problem - the desired text is selected, but the replace is not performed. Note that essentially this same Find/Replace code works perfectly when run as a macro from Word application, but won't work when invoked from Excel. Here's a snippet of the code that I'm running. My VBA knowledge is limited, so please keep that in mind when replying. TIA!!


On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If

For i = 1 To iRowCount
Text1 = LCase(Selection.Rows(i).Columns("A"))
Text2 = LCase(Selection.Rows(i).Columns("B"))
Text3 = Selection.Rows(i).Columns("C")
' set the SDR filename variable
tempfile = DefaultLib & Text1 & "." & Text2 & "." & Text3 & ".SDR.doc"
Set wdDoc = wdApp.Documents.Add(myWordTemp)
wdApp.Visible = True
Set wdDoc = wdApp.ActiveDocument
' replace heading texts
wdApp.Selection.Find.ClearFormatting
wdApp.Selection.Replacement.ClearFormatting
With wdApp.Selection.Find
.Text = "Old Heading1"
.Replacement.Text = Text1
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wdApp.Selection.Find.Execute Replace:=wdReplaceAll

wdDoc.SaveAs filename:=tempfile, _
FileFormat:=wdFormatDocument, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False
wdApp.ActiveDocument.Close wdSaveChanges
Next i