I am having an issue executing Find & Replace VBA code from MSExcel operating on instance of MSWord.
The following code instances demonstrate the difference when identical VBA code is executing in MSExcel and the same code is executed in MSWord.
In the code, 6 lines of "Normal" style Text are created. Three of the lines are marked with "!# ", to provide tags to convert to "List Paragraph" style.
The following code is executed from MSWord and works correctly:
Sub TestCodeOperatingInWord()
Dim wdDoc As Object
Application.ScreenUpdating = False
Set wdApp = CreateObject("Word.Application")
Set wdDoc = Documents.Add 'Open new Word document
wdDoc.Activate
Visible = True
Dim rng As Object
Set rng = Application.ActiveDocument.Range(Start:=0, End:=0)
Selection.EndKey wdStory, wdMove
Selection.TypeText Text:="New text Line 1"
Selection.InsertParagraph
Selection.EndKey wdStory, wdMove
Selection.TypeText Text:="New text Line 2"
Selection.InsertParagraph
Selection.EndKey wdStory, wdMove
Selection.TypeText Text:="New text Line 3"
Selection.InsertParagraph
Selection.EndKey wdStory, wdMove
Selection.TypeText Text:="!# A. New Indented Text Line 1"
Selection.InsertParagraph
Selection.EndKey wdStory, wdMove
Selection.TypeText Text:="!# B. New Indented Text Line 2"
Selection.InsertParagraph
Selection.EndKey wdStory, wdMove
Selection.TypeText Text:="!# C. New Indented Text Line 3"
Selection.InsertParagraph
With ActiveDocument.Content.Find
.ClearFormatting
.Style = ActiveDocument.Styles("Normal")
With .Replacement
.ClearFormatting
.Style = ActiveDocument.Styles("List Paragraph")
End With
.Execute FindText:="!# ", ReplaceWith:="", _
Format:=True, Replace:=wdReplaceAll
End With
ActiveDocument.Range.Select
With Selection.Find
.Text = "!# "
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
The following code executed from MSExcel does not perform Find and Replace:
Sub TestCodeOperatingOnWordInstanceInExcel()
Dim wdDoc As Object
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add 'Open new Word document
wdDoc.Activate
wdApp.Visible = True
Dim rng As Object
Set rng = wdApp.Application.ActiveDocument.Range(Start:=0, End:=0)
wdApp.Selection.EndKey wdStory, wdMove
wdApp.Selection.TypeText Text:="New text Line 1"
wdApp.Selection.InsertParagraph
wdApp.Selection.EndKey wdStory, wdMove
wdApp.Selection.TypeText Text:="New text Line 2"
wdApp.Selection.InsertParagraph
wdApp.Selection.EndKey wdStory, wdMove
wdApp.Selection.TypeText Text:="New text Line 3"
wdApp.Selection.InsertParagraph
wdApp.Selection.EndKey wdStory, wdMove
wdApp.Selection.TypeText Text:="!# A. New Indented Text Line 1"
wdApp.Selection.InsertParagraph
wdApp.Selection.EndKey wdStory, wdMove
wdApp.Selection.TypeText Text:="!# B. New Indented Text Line 2"
wdApp.Selection.InsertParagraph
wdApp.Selection.EndKey wdStory, wdMove
wdApp.Selection.TypeText Text:="!# C. New Indented Text Line 3"
wdApp.Selection.InsertParagraph
wdApp.ActiveDocument.Range.Select
With wdApp.ActiveDocument.Content.Find
.ClearFormatting
.Style = wdApp.ActiveDocument.styles("Normal")
With .Replacement
.ClearFormatting
.Style = wdApp.ActiveDocument.styles("List Paragraph")
End With
.Execute FindText:="!# ", ReplaceWith:="", _
Format:=True, Replace:=wdReplaceAll
End With
wdApp.ActiveDocument.Range.Select
With wdApp.Selection.Find
.Text = "!# "
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wdApp.Selection.Find.Execute Replace:=wdReplaceAll
End Sub
Please note that MSWord definitions, e.g. wdFindContinue, may need to be converted to their elaborations:
Public Const wdFindContinue As Long = 1
Help greatly appreciated; this one has me stumped.