Re: J West: Using Word capabilities in Excel and vv? by Ed
Ed
Tue Jul 11 09:21:31 CDT 2006
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
"Jonathan West" <jwest@mvps.org> wrote in message
news:%23aaEefOpGHA.4848@TK2MSFTNGP03.phx.gbl...
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:Ocf7eXGpGHA.4208@TK2MSFTNGP04.phx.gbl...
>> 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,
>
> More than I have when I'm tearing my hair out on a tricky problem! <g>
>
>> 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