make word vb code work from access

[slickdock]

I posted this in VBA for beginners, but got no reply. I'll try here...

I have this simple macro in word, and I'd like to execute it from an msAccess
module:

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
..Text = "a"
..Replacement.Text = "b"
..Forward = True
..Wrap = wdFindContinue
..Format = False
..MatchCase = False
..MatchWholeWord = False
..MatchWildcards = False
..MatchSoundsLike = False
..MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

I know enough to preface the above code with:
Dim wd As Object
Dim wdActiveDoc As Object
Dim wdField As Object
Set wd = CreateObject("Word.Application")

Then I know enough to add the following wd. prefix to these lines:
wd.Selection.Find.ClearFormatting
wd.Selection.Find.Replacement.ClearFormatting
With wd.Selection.Find

But after the END WITH, it's bombing out on this line:
wd.Selection.Find.Execute Replace:=wdReplaceAll

Any help would be greatly appreciated.

 

[Peter Jamieson]

have you "made a reference" to the Word object (i.e. with the VBA module
open, go to Tools->References, locate the appropriate "Microsoft Word
Object" library, and check it)?

If you haven't, then constants such as wdFindContinue and wdReplaceAll
will not have the correct values and the macro will likely fail.

Alternatively, you can look up the actual values in the Word object
model and use them instead of the constant names.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

 

[slickdock]

Thank you. I prefer to learn how to, as you suggest, look up the actual
values in the Word object model and use them instead of the constant names.
How do I do that? How do I "look up values in the Word object model?" Sorry,
I'm a beginner...

 

[Peter Jamieson]

To do that, you either need to open Word and its VBA editor, or
reference the Word object model in the Access VBA Editor (along the
lines I mentioned earlier).

Starting in the Access VBA Editor,
a. go to Tools->References
b. look for Microsoft Word xx.0 Object Library. For example, for Word
2007, this would be Microsoft Word 12.0 Object Library
c. check that, and click OK
d. go to View->Object Browser. In the first field, you will probably
see "<All Libraries>". You can either leave that, or click the dropdown
and select "Word", which should restrict any search to the Word object
library
e. in the text box under that, type the name you want to look for,
e.g. wdReplaceAll, and click the search button (i.e. the pair of
binoculars). In this case you will probably see a single line, and at
the bottom of the dialog box you should see something like

Const wdReplaceAll = 2
Member of Word.WdReplace

which tells you that instead of using wdReplaceAll in your code, you
could use the integer 2.

[you can ignore the following if you want...
Alternatively, after step (c), you could type the following in the
"Immediate" window in the VBA editor (have a look at the View menu if
you do not see it):

? wdReplaceAll

This should display the value of "wdReplaceAll", so you may see the
response "2". However, this is not completely reliable because there
could in theory be more than one "wdReplaceAll" defined in different
libraries or "namespaces". You can use

? Word.wdReplaceAll

or even

? Word.WdReplace.wdReplaceAll

to try to remove the potential ambiguity, but of course that assumes
that you know that wdReplaceAll is part of the WdReplace enumeration of
the the Word object in the first place
]

[ you can ignore this too if you want...
At this point, of course, you have set up the reference you need anyway,
so why use "2" instead of "wdReplaceAll" - well, you might want to do
that if you do not want to reference a specific version of the Word
object library in your code (because you want to try to make your code
work with, say, Word 2003 and 2007). However, if you want to do that,
you will probably have to deal with other problems
]


Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

 

[Pesach Shelnitz]

Hi,

The Word enumerations are documented with the values of all the constants in
the MSDN Libraray (http://www.msdn.com). To find a value, open the MSDN
Library and search for the applicable enumeration or constant. For example,
the value of wdFindContinue is documented at
http://msdn.microsoft.com/en-us/library/bb213734.aspx, and
the value of wdReplaceAll is documented at
http://msdn.microsoft.com/en-us/library/bb238124.aspx.

 

[slickdock]

Thank you both. I won't even have to go this route if I can get an answer to
this question. I posted it in OLE Interoperability and User Designed Forms
with no reply...Can you help?

I have a merge doc that has this command:

{DATABASE \d "c:\\tmp\\odbc.mdb" \s "select * from \"t_address\" }

The t_address table has one field only: the address.

I need the addresses to be in a certain sort order, so I tried adding a
number field to the table. This fixes the sort order problem, but now the
number field gets inserted into my word doc along with the address. Is there
a way to specifiy that I only want the address field to insert into my doc?

 

[Pesach Shelnitz]

Hi,

Try replacing the asterisk (*) by the name of the field (something like
t_address.address) that you want to include. The asterisk selects all the
fields. Also you need another quotation mark at the end to close the
quotation mark before the word select.