Excel macro to control Word Problem

[Dave R]

I am trying to write an Excel macro which will eventually perform a sor
of mail merge via Word. In theory this should be v.v. easy but I a
running into some basic trouble. More than likely it is my very limite
VBA knowledge.

Initially to get started I wrote a macro in Excel to copy a value fro
an Excel cell into a Word document using paste special. This wa
succesful. I then tried to get my Excel macro to find/replace som
values on a word document and it was then I ran into trouble. My macr
is below. Can someone recommend a good book which has more focus o
running one office package macro on another. I have never had an
trouble writing macros while staying in the same app.

Cheers,

Dave


Sub ControlWord()
' I have picked Microsoft Word from Tools>References
' in the VB editor to execute Word commands.
' I read VB Help topic "Controlling One Microsoft Office Applicatio
from Another"

Dim appWD As Word.Application
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application")
appWD.Visible = True

Sheets("Data").Select
' Copy the data for the new document to the clipboard
Range("A1").Copy
' Tell Word to create a new document
appWD.Documents.Add
' Tell Word to paste the contents of the clipboard into the ne
document
appWD.Selection.PasteSpecial Link:=False
DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False

appWD.Selection.WholeStory
'This is the problem part from here on I get Debug error 5
appWD.Selection.Find.Text = "Ping"
appWD.Selection.Find.Replacement.Text = "Pong"
appWD.Selection.Find.Forward = True
appWD.Selection.Find.Wrap = wdFindContinue
appWD.Selection.Find.Format = False
appWD.Selection.Find.MatchCase = False
appWD.Selection.Find.MatchWholeWord = False
appWD.Selection.Find.MatchWildcards = False
appWD.Selection.Find.MatchSoundsLike = False
appWD.Selection.Find.MatchAllWordForms = False
appWD.Selection.Find.Execute Replace:=wdReplaceAll
' Close the Word application
appWD.Quit
End Su

 

[Stan Scott]

Dave,

When you're controlling another application through Automation, you can't
use built-in variables. You have to convert wdFindContinue and wdReplaceAll
to their numeric equivalents. If you don't know what these are, bring up
Word and go to the Immediate window in the VBE. "msgbox(wdFindContinue)"
will give you the numeric equivalent.

Stan Scott
New York City

 

[Dave R]

Thanks for the help. I could get the numeric equivalent of wdFindConinu
etc but I must admit I don't know what to do with them now that I hav
them. I have never used numerical equivalents and was wondering if yo
could recommend a website/book to cure my ignorance. I have writte
some very complex mathematical excel macros and it has been supereas
so I am a bit surprised how difficult it is to control one app (but no
totally shocked).


Cheers for the help once more, much appreciated

Dave R
Liverpool, Englan

 

[Tom Ogilvy]

appWD.Selection.PasteSpecial Link:=False, _
DataType:=wdPasteText, _
Placement:=wdInLine, _
DisplayAsIcon:=False

Getting the value
? wdPasteText
2
? wdInLine
0


would be:

appWD.Selection.PasteSpecial Link:=False, _
DataType:=2, _
Placement:=0, _
DisplayAsIcon:=False

If you create a reference from Excel to Word, by going into the VBE and
going to Tools=>References and selecting Microsoft Word, then Excel will
know the definitions of the constants. However, if you will be distributing
the application to other users, you can not depend on the fact that they
will have the same or later version of Word. If they have an earlier
version, this can cause the reference not to be valid - so developers
usually use late binding (such is the situation Stan has described).

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible

Address some of the issues.

 

[Stan Scott]

The best method of controlling another app is to bring up the app itself (in
this case, Word) and use the Record Macro feature to go through the
necessary steps. This code may then be copied over to Excel and used --
just be sure to put your Application variable in front of each command.

Stan Scott
New York City

 

[Tom Ogilvy]

Seems like we are going in circles. The next statement would then be:

"When you're controlling another application through Automation, you can't
use built-in variables."

Unless, of course a reference is created to the other app.

--
Regards,
Tom Ogilvy

The best method of controlling another app is to bring up the app itself (in
this case, Word) and use the Record Macro feature to go through the
necessary steps. This code may then be copied over to Excel and used --
just be sure to put your Application variable in front of each command.

Stan Scott
New York City