J West: Using Word capabilities in Excel and vv?

[Ed]

In a previous thread I posted here and to vbscript, I was explaining that I
was trying to use a script to get some functionalities offerred by Word to
work in Excel and vice versa. The instance I was working on was a wildcard
text search in Excel; the only way I could find to make it work was to read
each cell in Excel into a string, open a Word doc and write in the string,
perform the search, then write the answers back into Excel. It worked, but
was very kludgy.

Jonathan West commented in that thread that there are better ways to
cross-utilize functionalities across applications, and keeping everything
within VBA is better than calling an external script and passing variables
back and forth. (I was trying a script to use a RegExp text search. I have
since let that slip down the list of things to work on.)

I have the utmost confidence in Jonathan's wisdom and experience, and would
like to see if we might be able to explore this further. Thinking about it,
there seems to be no reason why I can't instance Word's Find object and use
the built-in wildcard search capabilities from within an Excel macro. I'm
not quite so sure about using Excel's AutoFilter or Data Validation on a
Word table, though, but it would be very nice.

What are the limits and boundaries here in trying to cross-utilize
functionalities like this? Is it worth pursuing?

Ed

 

[Jonathan West]

In a previous thread I posted here and to vbscript, I was explaining that
I was trying to use a script to get some functionalities offerred by Word
to work in Excel and vice versa. The instance I was working on was a
wildcard text search in Excel; the only way I could find to make it work
was to read each cell in Excel into a string, open a Word doc and write in
the string, perform the search, then write the answers back into Excel.
It worked, but was very kludgy.

Yes, it would be

Jonathan West commented in that thread that there are better ways to
cross-utilize functionalities across applications, and keeping everything
within VBA is better than calling an external script and passing variables
back and forth. (I was trying a script to use a RegExp text search. I
have since let that slip down the list of things to work on.)

If and when that moves back up the list, take a look at the web page John
Nurick quoted on calling RegExp from VBA.

I have the utmost confidence in Jonathan's wisdom and experience,

and would like to see if we might be able to explore this further.
Thinking about it, there seems to be no reason why I can't instance Word's
Find object and use the built-in wildcard search capabilities from within
an Excel macro.

Well, actually you can't, because Word's Find object is applicable only to a
Word Selection or Range object, not to a text string.

But depending on the kind of search you want to do, you might be able to
extract the contents of an Excel cell into a string, and then do a wildcard
search either using RegExp or the VBA "Like" operator. (Look up "Like" in
the VBA Help. You may be surprised how powerful it is.)

I'm not quite so sure about using Excel's AutoFilter or Data Validation on
a Word table, though, but it would be very nice.

I doubt that this will be possible either.

What are the limits and boundaries here in trying to cross-utilize
functionalities like this?

You need to understand what each feature applies to. Most of the VBA
commands are in the form

expression.keyword(param1, param2)

where keyword is the name of the command, param1 and param2 (or more) are
parameters for the command, and expression is the kind of object the command
applies to. You cannot use a command on an object of the wrong type - you
will simply get a compile error. In many cases, this rules out using the
command from one application on an object from another. You need to read the
Help entry for the command carefully in order to understand what it applies
to.

Is it worth pursuing?

Possibly - it depends on what you are trying to achieve. Remember that there
are tools other than Word and Excel, some of which may suit your purposes
better. RegExp is an example which has already been mentioned. There are
lots of others - so many of them that you shouldn't rely on me to know them
all. I'm still learning new ones and I have been programming in WordBasic
and Word VBA for about 15 years now.

I get the impression that you are going about asking questions in a
less-than-optimal way. If you find you are stuck on a problem and lack the
tools or knowledge to go forward, when asking here you need to describe what
you are trying to achieve overall. By all means describe what you have tried
so far, but the key thing is the objective. A clear statement of the
objective, in English and without reference to tools, will allow us to think
laterally and suggest an entirely different approach if one is appropriate,
perhaps suggesting the use of a library or command that you haven't come
across yet.

If you get too hung up on how to make a particular technique work, and
concentrate solely on that, you are likely to get into the kind of
conversation you had with Karl Peterson, where he was most helpful in
developing a technique which was almost certainly inappropriate for your
overall problem.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Ed]

Thank you very much, Jonathan, for your comments.

Well, actually you can't, because Word's Find object is applicable only to
a Word Selection or Range object, not to a text string.

Come to think of it, this is what led me into RegExp in the first place
(which I had the impression was best called in a VBScript) - I realized Word
defined a range as bounded by characters in the document, and I couldn't set
a range to a portion of a string variable captured within Excel. That same
issue, now that I think about it, would prevent using any of Excel's data
tools on a Word table - it's not recognized by Excel as a range that the
tools will work with.

If you get too hung up on how to make a particular technique work, and
concentrate solely on that,

Yeah, I do that! 8>( Sorry!

Thanks again. It's the help and patience of the great folks here that have
helped me get anywhere with coding!

Ed