Word/Excel VBA cooperation? 3

[smooveb]

Hi everyone!

what I need to accomplish is very simple.

Based on an excel table with two columns:
Excel columns:
"Find What" "Replace With"

I will need to open up an XML file using Word, and then do find replaces within that Word file. Thoughts?

Thanks!
Barrett

 

[PHV]

What have you tried so far and where in your code are you stuck ?
Tip; in word VBA you can automate excel to read the Find/Replace values.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smooveb]

Hi, thanks PHV. My vba knowledge is such that I could easily do this if it was all within the context of Excel. I'd probably tape a macro and go through the motions in order to get the syntax. But since the macro won't record once I leave excel to open the file using word, I'm not sure what to try next. So I'm kind of stuck at the beginning...

Not sure, maybe it's easier to do it all within the context of Word, eschewing Excel altogether?

Barrett

 

[bubba100]

There is a macro recorder in Word. That might help get you started.

 

[smooveb]

Yeah, the trouble is that when I try to have it (the Word macro recorder) record me opening an Excel file using Excel, I've had no success thus far.

Based on my research in this forum, I'll probably want to:

Set appWd = CreatObject("Word.Application")
appWd.documents.open Filename:="c:\abcd.xml"
select all
' or something like that
With Selection.Find
.Text = "b"
' I'll replace this with cell(1, 1) from excel
.Replacement.Text = "c"
' I'll replace this with cell(1, 2) from excel
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Do you guys think something like this might work?

 

[smooveb]

actually, here's what I've tried. I'm getting a compile error highlighting the CreateObject command stating Sub or Function not defined. For some reason I thought CreateObject would be an internally defined function of some sort, that Excel would use to reference an external word object:

Sub test()

Set appWd = CreatObject("Word.Application")
appWd.documents.Open Filename:="c:\word test.xml"
Selection.WholeStory
' or something like that
With Selection.Find
.Text = "b"
' I'll replace this with cell(1, 1) from excel
.Replacement.Text = "c"
' I'll replace this with cell(1, 2) from excel
.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

 

[bubba100]

Add the dim statement (this method is called late binding). The first set statement is saying get the open word object which is fine IF Word is open. Then the next lines takes care of when Word is not open.

Dim appWd as Object

On Error Resume Next
Set appWd = GetObject(, "Word.Application")
If Err <> 0 Then
Set appWd = CreateObject("Word.Application")
Err.Clear
End If

 

[PHV]

Don't forget to use full qualified objects, ie appWd.Selection instead of Selection

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

1. And don't forget to have Option Explicit. Do not know if it was a typo here (likely...but)

Set appWd = CreatObject("Word.Application")

is missing an "e"

Set appWd = CreateObject("Word.Application")

Option Explicit makes it a lot easier to eliminate syntax errors.

2. Use Range, rather than Selection, with your Find. It is much better...and if you are declaring the Range from VBA initiating in Excel, make SURE you declare it as a Word range object!

Dim r As Word.Range

NOT....

Dim r As Range

The second would be an Excel range object and would not work within Word.

It is mentioned above that using Dim has a connection to late-binding.

Add the dim statement (this method is called late binding).

This is incorrect. There is no connection between using Dim and late or early binding.

The difference is what you Dim.

If you Dim as Object then yes, it is likely late binding.

Dim wrdApp As Word.Application

would be early binding.

Both use Dim.

faq219-2884

Gerry
My paintings and sculpture

 

[smooveb]

Thanks for all the great help and ideas!

Here's my code right now. For some reason, instead of processing all the replacements, it instead simply selects the first instance of "D" in the word doc before it exits the procedure without error. Any thoughts?

Thanks,
Barrett


Sub test()

Dim appWd As Object
Set appWd = GetObject(, "Word.Application")
If Err <> 0 Then
Set appWd = CreateObject("word.application")
Err.Clear
End If


appWd.documents.Open Filename:="c:\FCS August.xml"
appWd.Selection.WholeStory
With appWd.Selection.Find
.Text = "D"
.Replacement.Text = "c"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
appWd.Selection.Find.Execute Replace:=wdReplaceAll



End Sub

 

[fumei]

Try moving the replaceall inside the With statement.

I will also reiterate that it is better to use a range.

faq219-2884

Gerry
My paintings and sculpture

 

[smooveb]

Thanks Femei,

I've tried using a Range instead, but don't have the syntax correct. It's giving me a compile error of User Defined Type not Defined. Any ideas? Specifically, it looks like it's highlighting the Dim rng line. Here's the code:

Sub test()

Dim appWd As Object
Set appWd = GetObject(, "Word.Application")
If Err <> 0 Then
Set appWd = CreateObject("word.application")
Err.Clear
End If

Dim rng As appWd.Word.Range
appWd.documents.Open Filename:="c:\FCS August.xml"
Set rng = activedocument.Range
appWd.Selection.WholeStory
With rng.Find
.Text = "D"
.Replacement.Text = "c"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
appWd.Selection.Find.Execute Replace:=wdReplaceAll
End With


End Sub

 

[PHV]

Use late binding and full qualified objects:

Sub test()
Dim appWd As Object
On Error Resume Next
Set appWd = GetObject(, "Word.Application")
If Err <> 0 Then
    Set appWd = CreateObject("Word.Application")
    Err.Clear
End If
Dim rng As Object
appWd.Documents.Open Filename:="c:\FCS August.xml"
Set rng = appWd.ActiveDocument.Range
appWd.Selection.WholeStory
With rng.Find
    .Text = "D"
    .Replacement.Text = "c"
    .Forward = True
    .Wrap = 1 'wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Find.Execute Replace:=2 'wdReplaceAll
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smooveb]

Thanks - this compiles alright, but does not actually do any Find/Replace work. Perhaps the range is not defined correctly?

Thx
Barrett