Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
J West: Using Word capabilities in Excel and vv?
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
J West: Using Word capabilities in Excel and vv?
"Ed" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
J West: Using Word capabilities in Excel and vv?
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" wrote in message ... "Ed" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Word field like capabilities | Excel Discussion (Misc queries) | |||
2006 West Coast Excel / Access User Conference | Excel Discussion (Misc queries) | |||
West Coast Excel User Conference - Announcement | Excel Discussion (Misc queries) | |||
West Coast Excel User Conference - Announcement | Excel Worksheet Functions | |||
Excel Capabilities in Word | Charts and Charting in Excel |