Solved: Why would this script need a Constant

[velohead]

Hi All,

I have the following script that seems to work.
I dont understand why this extract...
Const wdReplaceAll = 2
....is there or what it does.
Can anyone enlighten me ?

Thx.



Sub microsoft_003_find_replace_contoso_fabrikam()
'technet.microsoft.com/en-us/library/ee692875.aspx
Const wdReplaceAll = 2
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Open("I:\HOFolders\Raymond\Word Development\Ex 04 - RD Use Excel to Control Word\Ex 04 - Blank.doc")
Set objSelection = objWord.Selection
objSelection.Find.Text = "Contoso"
objSelection.Find.Forward = True
objSelection.Find.MatchWholeWord = True
objSelection.Find.Replacement.Text = "Fabrikam"
objSelection.Find.Execute , , , , , , , , , , wdReplaceAll
End Sub

[Tinbendr]

(OK I'll attempt it.)

Within the scope of the Script you are using, it doesn't know what wdReplaceAll is. To the script, it's unqualified reference it doesn't understand.

So, to help the humans out, we define it as a Constant. Because I know what
Selection.Find.Execute , , , , , , , , , , wdReplaceAll will do, but not necessarily
Selection.Find.Execute , , , , , , , , , ,2Hope this helps.
David

[velohead]

Hope this helps.
David

Yes it does help, very much.



Can I be cheeky and ask why this script will find / replace the first occurances, but not the second.

The script is same as post #1, but slightly augmented.
NB - The script resides in excel, and opens word, then performs word actions.


Sub microsoft_004_find_replace_cars()
'technet.microsoft.com/en-us/library/ee692875.aspx
Const wdReplaceAll = 2
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Open("I:\HOFolders\Raymond\Word Development\Ex 04 - RD Use Excel to Control Word\Ex 04 - Blank.doc")
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set objSelection = objWord.Selection
objSelection.Find.Text = "subaru"
objSelection.Find.Forward = True
objSelection.Find.MatchWholeWord = True
objSelection.Find.Replacement.Text = "porsche"
objSelection.Find.Execute , , , , , , , , , , wdReplaceAll
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set objSelection = objWord.Selection
objSelection.Find.Text = "allegro"
objSelection.Find.Forward = True
objSelection.Find.MatchWholeWord = True
objSelection.Find.Replacement.Text = "ascari"
objSelection.Find.Execute , , , , , , , , , , wdReplaceAl
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set objSelection = objWord.Selection
objSelection.Find.Text = "ford"
objSelection.Find.Forward = True
objSelection.Find.MatchWholeWord = True
objSelection.Find.Replacement.Text = "ferrari"
objSelection.Find.Execute , , , , , , , , , , wdReplaceAl
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

[geekgirlau]

Typo ... wdReplaceAlL

[geekgirlau]

You could also tidy this up a little:


Sub microsoft_004_find_replace_cars()
'technet.microsoft.com/en-us/library/ee692875.aspx
Const wdReplaceAll = 2
Set objWord = CreateObject("Word.Application")

objWord.Visible = True
Set objDoc = objWord.Documents.Open("I:\HOFolders\Raymond\Word Development\" & _
"Ex 04 - RD Use Excel to Control Word\Ex 04 - Blank.doc")

With objWord.Selection.Find
.Text = "subaru"
.Forward = True
.MatchWholeWord = True
.Replacement.Text = "porsche"
.Execute , , , , , , , , , , wdReplaceAll
End With

With objWord.Selection.Find
.Text = "allegro"
.Forward = True
.MatchWholeWord = True
.Replacement.Text = "ascari"
.Execute , , , , , , , , , , wdReplaceAll

End With

With objWord.Selection.Find
.Text = "ford"
.Forward = True
.MatchWholeWord = True
.Replacement.Text = "ferrari"
.Execute , , , , , , , , , , wdReplaceAll
End With
End Sub

[velohead]

Typo ... wdReplaceAlL

ops , I should have spotted that...:o:


Thanks for your second post too, I'll look at that now,
but being a novice, I may take a slow pace to digest it.

[geekgirlau]

That's why you should always use Option Explicit. It forces you to declare all variables and constants, and will pick up typing errors like this for you!

[fumei]

You can also replace:
.Execute , , , , , , , , , , wdReplaceAll
with:

.Execute Replace:=wdRepaceAll


Also, you could do the processing without making the Word instance Visiable (if you want) by using arrays and a Word range object.
Sub ChangeCars()
Const wdReplaceAll = 2
Dim List_A() As String
Dim List_B() As String
Dim var
Dim r As Word.Range

List_A = Split("subaru,allegro,ford", ",")
List_B = Split("porsche,ascari,ferrari", ",")
Set objword = CreateObject("Word.Application")

Set objDoc = objword.Documents.Open("I:\HOFolders\Raymond\Word Development\" & _
"Ex 04 - RD Use Excel to Control Word\Ex 04 - Blank.doc")

Set r = objDoc.Range
For var = 0 To UBound(List_A())
Set r = objword.Range
With r.Find
.Text = List_A(var)
.Replacement.Text = List_B(var)
.Execute Replace:=wdReplaceAll
End With
Next
End Sub

[velohead]

That's why you should always use Option Explicit. It forces you to declare all variables and constants, and will pick up typing errors like this for you!

Point taken - it is a bad habit (not using Option Explicit) that i need to break

[velohead]

Hi Fumei,

In the code...
List_A = Split("subaru,allegro,ford", ",")
List_B = Split("porsche,ascari,ferrari", ",")


...what are the final few characters for, or do ?
Just so i can learn, please.

ie the .... ", ",")

[geekgirlau]

Split takes a string and splits it into an array. There are 2 parameters: the string that you want to split ("subaru,allegro,ford") and the character that separates the values (in this case, a comma).

[fumei]

In other words:

Split("subaru,allegro,ford", ",")

makes an array of:

subaru
allegro
ford

The delimiter can be anything.

Split("subaru|allegro|ford", "|") produces the exact same thing (the delimiter is the |).

Split("subaru allegro ford", " ") produces the same thing (the delimiter is a space).

Split("subaru Zyadda Zwhatever", " Z") produces the same thing (the delimiter is " Z" - four spaces with a Z)

Although on review, this site trims out "extra" spaces, so call it one space and Z.