Edit Word Doc in an Excel Macro

[KenVBProgmer]

I am trying to do a global replace of all occurrences of the string "Comp" with the string "&&&" everywhere in the first table in a Word document using the following code executed from Excel:

Sub ScanOutlines()
EditWordTable_Method2 "C:\Test \", "Test"
End Sub
Public Sub EditWordTable_Method2(sDocPath As String, wdDocName As String)
Dim WordDocument As Object
Dim wdApp As Object
Set wdApp = GetObject("", "Word.Application")
DoEvents
wdApp.Application.Documents.Open sDocPath & wdDocName & ".doc"
wdApp.Application.Visible = True
wdApp.ActiveDocument.Tables(1).Select
'Change "Comp" to "&&&" . . .
wdApp.Selection.Find.ClearFormatting
wdApp.Selection.Find.Replacement.ClearFormatting
With wdApp.Selection.Find
.Text = "Comp"
.Replacement.Text = "&&&"
.Forward = True
.Wrap = WdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wdApp.Selection.Find.Execute Replace:=wdReplaceAll
DoEvents
wdApp.ActiveDocument.Tables(1).Range.Copy
Sheets.Add.Name = "Temp"
ActiveSheet.PasteSpecial Format:="HTML", Link:=False,
DisplayAsIcon:=False

The code successfully opens the Word file, and selects the table but does not perform the replace. Instead, the "Replace:=wdReplaceAll" execution only selects the first instance of "Comp" in the table but does not perform any actual replaces. The code after the replace runs fine also, i.e. the table gets copied and pasted into the Excel sheet "Temp" just fine. But for some reason, the code refuses to perform any edits to the Word table. Anyone know what's wrong here? Thanks!

[MartinLiss]

Are you doing this from within Excel or Word?

[si_the_geek]

I believe so:

...executed from Excel

..so thread moved to Office Development forum.


The problem is that you are using Late Binding (declaring your variables as Object, and using GetObject), and as such the constants like wdReplaceAll are not declared.

If you had used Option Explicit you would have been warned about that - but as you haven't, they were created as variables instead (with a 'default' value of 0, which is probably not correct).


You basically have 2 options for correcting this, the first is to simply add a reference to Word (but this is likely to cause errors if your Excel file is used on another computer), the other is declare the constant(s) you use.

You can either find the declaration for it by going into Word's VB editor and use the Object Browser to find the declaration for each one you have used, or you can add the relevant module from here which contains all of the constants for Word 97 (the values of the constants don't change for later versions, but you may find you need to add some 'new' ones using the method above).

[KenVBProgmer]

Thanks! All it took was to add the declaration:

Const wdReplaceAll = 2

appreciate the assistance!!!