|
Call VBScript from VBA, pass variable, return result to macro?
I've been looking at newsgroup posts and VBScript sites all morning,
and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed |
Call VBScript from VBA, pass variable, return result to macro?
Do yo mean something like this:
Sub Ed() Dim x As Long x = 2 MsgBox Edward(x) End Sub Function Edward(x As Long) As Long Edward = x + 2 End Function Ed wrote: I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed |
Call VBScript from VBA, pass variable, return result to macro?
Assuming that you are not just looking at a trivial implementation like
below, the way I did this (to call 3000 vbs methods - don't ask) was to embed the VBscript engine (msscript.ocx) into an application. Get a reference to the engine in your code, load up your VBScript function such as function Test(x,y) y = x + 2 end function Then you can reference the function by name and call with named parameters. You need to ensure that you are passing byref. Have a crack at that. Somewhere on the MSDN there is a page about a VBScript calculator - that will give you a lot of pointers. Be warned, this is not easy. Post back if you get stuck and please rate this posting if it helps. -- www.alignment-systems.com "Ed" wrote: I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed |
Call VBScript from VBA, pass variable, return result to macro?
Thanks for replying, Greg. I apologize for not making myself clear.
The script is in a separate .vbs file, not a script in the same VBA module. As such, I need to open the file, pass the variable from the VBA macro to the VBScript function, then pass the function result back into the macro. Ed Greg Maxey wrote: Do yo mean something like this: Sub Ed() Dim x As Long x = 2 MsgBox Edward(x) End Sub Function Edward(x As Long) As Long Edward = x + 2 End Function Ed wrote: I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed |
Call VBScript from VBA, pass variable, return result to macro?
Ed,
Yes. I didn't understand the question. Sorry. I now recognize that your question is over my head. Good luck. Ed wrote: Thanks for replying, Greg. I apologize for not making myself clear. The script is in a separate .vbs file, not a script in the same VBA module. As such, I need to open the file, pass the variable from the VBA macro to the VBScript function, then pass the function result back into the macro. Ed Greg Maxey wrote: Do yo mean something like this: Sub Ed() Dim x As Long x = 2 MsgBox Edward(x) End Sub Function Edward(x As Long) As Long Edward = x + 2 End Function Ed wrote: I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed |
Call VBScript from VBA, pass variable, return result to macro?
Is there something that you need to do with VBScript that you can't readily
accomplish with VBA? I confess that I'm hard-pressed to think of an example. For instance, you can add references to FileSystemObject and VBScript Regular Expressions in VBA and use them just as you would in script. "Ed" wrote in message oups.com... I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed |
Call VBScript from VBA, pass variable, return result to macro?
Hi, John. In a way, yes, I am asking for a relatively trivial
implementation (I think - maybe not, though, and I don't really know the full scope of what I'm asking). I want to have a function script in a vbs file and run a macro in Word or Excel that calls this script, passes a variable into it, and receives the return from the function. If it goes into what you are describing, I think I will forget about it until I "grow up" in my skills, because I can't understand most of what you were trying to explain. Ed John.Greenan wrote: Assuming that you are not just looking at a trivial implementation like below, the way I did this (to call 3000 vbs methods - don't ask) was to embed the VBscript engine (msscript.ocx) into an application. Get a reference to the engine in your code, load up your VBScript function such as function Test(x,y) y = x + 2 end function Then you can reference the function by name and call with named parameters. You need to ensure that you are passing byref. Have a crack at that. Somewhere on the MSDN there is a page about a VBScript calculator - that will give you a lot of pointers. Be warned, this is not easy. Post back if you get stuck and please rate this posting if it helps. -- www.alignment-systems.com "Ed" wrote: I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed |
Call VBScript from VBA, pass variable, return result to macro?
Hi, Walter.
Is there something that you need to do with VBScript that you can't readily accomplish with VBA? For example, I tried to use a RegExp wildcard text search in a Word VBA module. It wouldn't accept some of the delimiters allowed in RegExp, because (I'm guessing here) they have different meanings in Word. I'd really like to use something like this in Excel, which has very little text handling capabilities. Word, on the other hand, has few data handling functions. If I want to use these capabilities which are found in other programs, I have to open the other program, write into it, run it, and receive from it. (For instance, a wildcard text search in xcel meant I had to capture each cell, open a Word doc and write the string, then search and return my array of results to Excel.) I had the bright idea calling a script file might be easier and faster, certainly less tha opening another instance of an Office program every time I run it. If I am not making any sense here, please feel free to set me straight. Ed Walter Zackery wrote: Is there something that you need to do with VBScript that you can't readily accomplish with VBA? I confess that I'm hard-pressed to think of an example. For instance, you can add references to FileSystemObject and VBScript Regular Expressions in VBA and use them just as you would in script. "Ed" wrote in message oups.com... I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed |
Call VBScript from VBA, pass variable, return result to macro?
Ed wrote:
I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? Simple? It depends. Are you planning to "ship" this to computers outside your control? If not, yeah, there's a way that isn't overly involved. You'll, essentially, have to "lower yourself" to using VBS within your VBA. Fire off the VBS scripts with something like ("air code" alert! <g) this: Set wsh = CreateObject("WScript.Shell") Set proc = wsh.Exec("cscript your.vbs param1 param2") Spin cycles in your VBA app, waiting for the VBS to complete: ' Wait for application to exit Do While proc.Status = 0 Sleep 1 'Need to add the API declaration to your module! Loop Retrieve the command line parameters within the VBS like this (actual example taken from a script I use here): ' http://www.winguides.com/scripting/reference.php?id=117 Set Args = WScript.Arguments If Args.Count = 2 Then ' First argument is username; Second is password User = LCase(Args(0)) Pass = Args(1) WScript.Echo "Login as: " & User Else User = LCase(Network.UserName) End If Have your VBS use the optional ErrorCode parameter to the Quit method to send a return value: http://www.winguides.com/scripting/reference.php?id=111 Read that value with: ' http://www.codecomments.com/archive2...-1-335409.html Debug.Print "VBS Return Value: "; proc.ExitCode Hope this helps! -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Hi, Karl. No, this is only for me. I'm not about to unleash my messes
on the world at large!! <g Okay - I'm trying to take this one step at a time. I've got a vbs file with Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub and I've got a Word macro with Sub TestMyScriptHere() Dim str As String Dim x As Long str = "ABC" x = 2 Dim FPath As String FPath = "C:\Documents and Settings\edward.millis\My Documents\Scripting\" Dim wsh, proc Set wsh = CreateObject("WScript.Shell") Set proc = wsh.Exec("cscript FPath & MacroTest.vbs str x") End Sub I ran it, and got a brief flash of a command prompt window (Word &Windows XP) - no "echo" message boxes. So I at least got the macro to open the script file. I didn't get the variables into the script, though. Is there hope for me? Do I need years of intnse training? Or will a small drop-kick in the right direction get me going? Ed Karl E. Peterson wrote: Ed wrote: I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? Simple? It depends. Are you planning to "ship" this to computers outside your control? If not, yeah, there's a way that isn't overly involved. You'll, essentially, have to "lower yourself" to using VBS within your VBA. Fire off the VBS scripts with something like ("air code" alert! <g) this: Set wsh = CreateObject("WScript.Shell") Set proc = wsh.Exec("cscript your.vbs param1 param2") Spin cycles in your VBA app, waiting for the VBS to complete: ' Wait for application to exit Do While proc.Status = 0 Sleep 1 'Need to add the API declaration to your module! Loop Retrieve the command line parameters within the VBS like this (actual example taken from a script I use here): ' http://www.winguides.com/scripting/reference.php?id=117 Set Args = WScript.Arguments If Args.Count = 2 Then ' First argument is username; Second is password User = LCase(Args(0)) Pass = Args(1) WScript.Echo "Login as: " & User Else User = LCase(Network.UserName) End If Have your VBS use the optional ErrorCode parameter to the Quit method to send a return value: http://www.winguides.com/scripting/reference.php?id=111 Read that value with: ' http://www.codecomments.com/archive2...-1-335409.html Debug.Print "VBS Return Value: "; proc.ExitCode Hope this helps! -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
"Ed" wrote in message ups.com... Hi, Walter. Is there something that you need to do with VBScript that you can't readily accomplish with VBA? For example, I tried to use a RegExp wildcard text search in a Word VBA module. It wouldn't accept some of the delimiters allowed in RegExp, because (I'm guessing here) they have different meanings in Word. Quite correct. Look up wildcards in the Word help. Wildcard searches are similar but not identical to Regexp searches. I'd really like to use something like this in Excel, which has very little text handling capabilities. Word, on the other hand, has few data handling functions. If I want to use these capabilities which are found in other programs, I have to open the other program, write into it, run it, and receive from it. (For instance, a wildcard text search in xcel meant I had to capture each cell, open a Word doc and write the string, then search and return my array of results to Excel.) There are much easier ways of doing this. I had the bright idea calling a script file might be easier and faster, certainly less tha opening another instance of an Office program every time I run it. If I am not making any sense here, please feel free to set me straight. Opening a script file will not help much. Because even if you so open a script file, you are still faced with the need to use whatever object from whichever application suits your needs best. Better to create subroutines and functions within VBA. Take a look at this article. How to cut out repetition and write much less code, by using subroutines and functions that take arguments http://www.word.mvps.org/FAQs/Macros...cArguments.htm You can use a VBA routine to do anything you might do in a VBScript. -- 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 |
Call VBScript from VBA, pass variable, return result to macro?
Ed wrote:
Hi, Karl. No, this is only for me. I'm not about to unleash my messes on the world at large!! <g Okay - I'm trying to take this one step at a time. I've got a vbs file with Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub and I've got a Word macro with Sub TestMyScriptHere() Dim str As String Dim x As Long str = "ABC" x = 2 Dim FPath As String FPath = "C:\Documents and Settings\edward.millis\My Documents\Scripting\" Dim wsh, proc Set wsh = CreateObject("WScript.Shell") Set proc = wsh.Exec("cscript FPath & MacroTest.vbs str x") End Sub I ran it, and got a brief flash of a command prompt window (Word &Windows XP) - no "echo" message boxes. So I at least got the macro to open the script file. I didn't get the variables into the script, though. Is there hope for me? Do I need years of intnse training? Or will a small drop-kick in the right direction get me going? Heh, VBScript takes patience, and the inate ability to break things up into the smallest possible chunks. Try running the script directly from the command line, first, for example, to determine what it's doing. Going back to that: Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub You're not calling the TestMe subroutine? Try rewriting like this: ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Then, at the command prompt (beware word-warp): C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest.vbs ABC 2 You should see your parameters echoed at this point. -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Comment in-line about halfway down...
Karl E. Peterson wrote: Ed wrote: Hi, Karl. No, this is only for me. I'm not about to unleash my messes on the world at large!! <g Okay - I'm trying to take this one step at a time. I've got a vbs file with Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub and I've got a Word macro with Sub TestMyScriptHere() Dim str As String Dim x As Long str = "ABC" x = 2 Dim FPath As String FPath = "C:\Documents and Settings\edward.millis\My Documents\Scripting\" Dim wsh, proc Set wsh = CreateObject("WScript.Shell") Set proc = wsh.Exec("cscript FPath & MacroTest.vbs str x") End Sub Part of the problem is in the parameter you pass to wsh.Exec. Because the whole thing is enclosed in one pair of quotes, you're getting the literal strings "FPath", "str" and "x" in the command, instead of the values of the intended variables. Also, because the path contains spaces, the whole path\filename needs to be enclosed in quotes, represented by chr(34). The syntax should be [all on one line] Set proc = wsh.Exec("cscript " & chr(34) & FPath & "MacroTest.vbs" & chr(34) & " " & str & " " & x) Be careful to get the spaces and quotes in the right places so the command processor doesn't barf on an invalid file name. You also need Karl's modification of the script to call the function, since all you're passing are the filename and the arguments. I ran it, and got a brief flash of a command prompt window (Word &Windows XP) - no "echo" message boxes. So I at least got the macro to open the script file. I didn't get the variables into the script, though. Is there hope for me? Do I need years of intnse training? Or will a small drop-kick in the right direction get me going? Heh, VBScript takes patience, and the inate ability to break things up into the smallest possible chunks. Try running the script directly from the command line, first, for example, to determine what it's doing. Going back to that: Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub You're not calling the TestMe subroutine? Try rewriting like this: ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Then, at the command prompt (beware word-warp): C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest.vbs ABC 2 You should see your parameters echoed at this point. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
Call VBScript from VBA, pass variable, return result to macro?
You're not calling the TestMe subroutine?
D'oh!! Okay, "Call TestMe" is now the first line. Then, at the command prompt Now I feel stupid! Never worked with DOS and command prompts before. No matter what I try, I can't get it to work, so I must be trying wrong. Took out the line break and pasted in at StartRun - flashed a command window but no echos. Opened a command prompt from the Accessories menu and typed it in, got the following: C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest .vbs ABC 2 Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. C:\Documents and Settings\edward.millis\My Documents\Scripting\MacroTest.vbs(1, 1) Microsoft VBScript runtime error: Wrong number of arguments or invalid property assignment: 'TestMe' <sigh Are you sure this is how Bill Gates started? Ed Karl E. Peterson wrote: Ed wrote: Hi, Karl. No, this is only for me. I'm not about to unleash my messes on the world at large!! <g Okay - I'm trying to take this one step at a time. I've got a vbs file with Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub and I've got a Word macro with Sub TestMyScriptHere() Dim str As String Dim x As Long str = "ABC" x = 2 Dim FPath As String FPath = "C:\Documents and Settings\edward.millis\My Documents\Scripting\" Dim wsh, proc Set wsh = CreateObject("WScript.Shell") Set proc = wsh.Exec("cscript FPath & MacroTest.vbs str x") End Sub I ran it, and got a brief flash of a command prompt window (Word &Windows XP) - no "echo" message boxes. So I at least got the macro to open the script file. I didn't get the variables into the script, though. Is there hope for me? Do I need years of intnse training? Or will a small drop-kick in the right direction get me going? Heh, VBScript takes patience, and the inate ability to break things up into the smallest possible chunks. Try running the script directly from the command line, first, for example, to determine what it's doing. Going back to that: Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub You're not calling the TestMe subroutine? Try rewriting like this: ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Then, at the command prompt (beware word-warp): C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest.vbs ABC 2 You should see your parameters echoed at this point. -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Set proc = wsh.Exec("cscript " & chr(34) & FPath & "MacroTest.vbs" & chr(34)
& " " & str & " " & x) Be careful to get the spaces and quotes in the right places so the command processor doesn't barf on an invalid file name. You also need Karl's modification of the script to call the function, since all you're passing are the filename and the arguments. Thanks, Jay. Changes made. But still no joy. Ah, well. Ed Jay Freedman wrote: Comment in-line about halfway down... Karl E. Peterson wrote: Ed wrote: Hi, Karl. No, this is only for me. I'm not about to unleash my messes on the world at large!! <g Okay - I'm trying to take this one step at a time. I've got a vbs file with Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub and I've got a Word macro with Sub TestMyScriptHere() Dim str As String Dim x As Long str = "ABC" x = 2 Dim FPath As String FPath = "C:\Documents and Settings\edward.millis\My Documents\Scripting\" Dim wsh, proc Set wsh = CreateObject("WScript.Shell") Set proc = wsh.Exec("cscript FPath & MacroTest.vbs str x") End Sub Part of the problem is in the parameter you pass to wsh.Exec. Because the whole thing is enclosed in one pair of quotes, you're getting the literal strings "FPath", "str" and "x" in the command, instead of the values of the intended variables. Also, because the path contains spaces, the whole path\filename needs to be enclosed in quotes, represented by chr(34). The syntax should be [all on one line] Set proc = wsh.Exec("cscript " & chr(34) & FPath & "MacroTest.vbs" & chr(34) & " " & str & " " & x) Be careful to get the spaces and quotes in the right places so the command processor doesn't barf on an invalid file name. You also need Karl's modification of the script to call the function, since all you're passing are the filename and the arguments. I ran it, and got a brief flash of a command prompt window (Word &Windows XP) - no "echo" message boxes. So I at least got the macro to open the script file. I didn't get the variables into the script, though. Is there hope for me? Do I need years of intnse training? Or will a small drop-kick in the right direction get me going? Heh, VBScript takes patience, and the inate ability to break things up into the smallest possible chunks. Try running the script directly from the command line, first, for example, to determine what it's doing. Going back to that: Sub TestMe(str, x) Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub You're not calling the TestMe subroutine? Try rewriting like this: ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Then, at the command prompt (beware word-warp): C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest.vbs ABC 2 You should see your parameters echoed at this point. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
Call VBScript from VBA, pass variable, return result to macro?
Jay Freedman wrote:
Dim wsh, proc Set wsh = CreateObject("WScript.Shell") Set proc = wsh.Exec("cscript FPath & MacroTest.vbs str x") End Sub Part of the problem is in the parameter you pass to wsh.Exec. Because the whole thing is enclosed in one pair of quotes, you're getting the literal strings "FPath", "str" and "x" in the command, instead of the values of the intended variables. Good catch. I browsed that so quickly, I didn't even realize they _were_ variables. That said, had the rest of it worked, those variable names should still have been echoed. Also, because the path contains spaces, the whole path\filename needs to be enclosed in quotes, represented by chr(34). The syntax should be [all on one line] Set proc = wsh.Exec("cscript " & chr(34) & FPath & "MacroTest.vbs" & chr(34) & " " & str & " " & x) Yep! Another good point. -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Ed wrote:
You're not calling the TestMe subroutine? D'oh!! Okay, "Call TestMe" is now the first line. Then, at the command prompt Now I feel stupid! Never worked with DOS and command prompts before. Ah, okay, time to jump back in time a few decades... <g No matter what I try, I can't get it to work, so I must be trying wrong. Took out the line break and pasted in at StartRun - flashed a command window but no echos. Opened a command prompt from the Accessories menu and typed it in, got the following: C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest .vbs ABC 2 Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. C:\Documents and Settings\edward.millis\My Documents\Scripting\MacroTest.vbs(1, 1) Microsoft VBScript runtime error: Wrong number of arguments or invalid property assignment: 'TestMe' Wrong number of arguments means, well, just that. Did you notice that in my *re-typed* example, I removed the args to TestMe? ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Try again, this time heeding the error messages you get. <sigh Are you sure this is how Bill Gates started? He actually subcontracted the DOS prompt to a guy named Tim. ;-) -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
On 29 Jun 2006 09:25:04 -0700, "Ed" wrote:
For example, I tried to use a RegExp wildcard text search in a Word VBA module. It wouldn't accept some of the delimiters allowed in RegExp, because (I'm guessing here) they have different meanings in Word. The VBScript RegExp object works just fine in a VBA module, and it's *much* easier to use VBScript objects in VBA than it is to swap data with a VBScript executing as a separate process. There are some ready-made functions using the RegExp object at http://www.j.nurick.dial.pipex.com/Code/index.htm which may help you get started. I'd really like to use something like this in Excel, which has very little text handling capabilities. Word, on the other hand, has few data handling functions. If I want to use these capabilities which are found in other programs, I have to open the other program, write into it, run it, and receive from it. (For instance, a wildcard text search in xcel meant I had to capture each cell, open a Word doc and write the string, then search and return my array of results to Excel.) I had the bright idea calling a script file might be easier and faster, certainly less tha opening another instance of an Office program every time I run it. If I am not making any sense here, please feel free to set me straight. Ed Walter Zackery wrote: Is there something that you need to do with VBScript that you can't readily accomplish with VBA? I confess that I'm hard-pressed to think of an example. For instance, you can add references to FileSystemObject and VBScript Regular Expressions in VBA and use them just as you would in script. "Ed" wrote in message oups.com... I've been looking at newsgroup posts and VBScript sites all morning, and can't seem to find a simple explanation and method for calling a script from a Word or Excel macro and passing a variable into the script, then returning the result of whatever the script does into the macro. That can mean there isn't any simple way to do this, and I need to stop looking. In the hopes, however, that I am too unlearned to recognize the answer when I see it, I'd like to just ask: how do I do this? For instance: ~~VBA macro~~ Sub Foo2Script Dim x As Long x=2 Call script here MsgBox scriptresult End Sub ~~VBScript~~ Dim x, y x = x_from_macro y = x + 2 scriptresult = y Any help is greatly appreciated. Ed -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Call VBScript from VBA, pass variable, return result to macro?
Karl: I went back and re-read ~everything~ again, caught the bit about
_removing_ the arguments, and even added the Sleep API as originally recommended. Everything runs without errors. Sleep is at 5000, so I get a good glimpse of the command window. It's totally blank. Unless you've got a brilliant flash of insight into my stumblings, I'm going to chalk it up as not knowing enough about what I want to do. With a long weekend coming up, I'll have to be re-trained next week anyway! So I'll read some more and play some more, and maybe give this another round on the NG in a week or three. Thanks to all for all the help and support. Ed Karl E. Peterson wrote: Ed wrote: You're not calling the TestMe subroutine? D'oh!! Okay, "Call TestMe" is now the first line. Then, at the command prompt Now I feel stupid! Never worked with DOS and command prompts before. Ah, okay, time to jump back in time a few decades... <g No matter what I try, I can't get it to work, so I must be trying wrong. Took out the line break and pasted in at StartRun - flashed a command window but no echos. Opened a command prompt from the Accessories menu and typed it in, got the following: C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest .vbs ABC 2 Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. C:\Documents and Settings\edward.millis\My Documents\Scripting\MacroTest.vbs(1, 1) Microsoft VBScript runtime error: Wrong number of arguments or invalid property assignment: 'TestMe' Wrong number of arguments means, well, just that. Did you notice that in my *re-typed* example, I removed the args to TestMe? ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Try again, this time heeding the error messages you get. <sigh Are you sure this is how Bill Gates started? He actually subcontracted the DOS prompt to a guy named Tim. ;-) -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Didja try it at the command line?
-- Working without a .NET? http://classicvb.org/ Ed wrote: Karl: I went back and re-read ~everything~ again, caught the bit about _removing_ the arguments, and even added the Sleep API as originally recommended. Everything runs without errors. Sleep is at 5000, so I get a good glimpse of the command window. It's totally blank. Unless you've got a brilliant flash of insight into my stumblings, I'm going to chalk it up as not knowing enough about what I want to do. With a long weekend coming up, I'll have to be re-trained next week anyway! So I'll read some more and play some more, and maybe give this another round on the NG in a week or three. Thanks to all for all the help and support. Ed Karl E. Peterson wrote: Ed wrote: You're not calling the TestMe subroutine? D'oh!! Okay, "Call TestMe" is now the first line. Then, at the command prompt Now I feel stupid! Never worked with DOS and command prompts before. Ah, okay, time to jump back in time a few decades... <g No matter what I try, I can't get it to work, so I must be trying wrong. Took out the line break and pasted in at StartRun - flashed a command window but no echos. Opened a command prompt from the Accessories menu and typed it in, got the following: C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest .vbs ABC 2 Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. C:\Documents and Settings\edward.millis\My Documents\Scripting\MacroTest.vbs(1, 1) Microsoft VBScript runtime error: Wrong number of arguments or invalid property assignment: 'TestMe' Wrong number of arguments means, well, just that. Did you notice that in my *re-typed* example, I removed the args to TestMe? ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Try again, this time heeding the error messages you get. <sigh Are you sure this is how Bill Gates started? He actually subcontracted the DOS prompt to a guy named Tim. ;-) -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
OMG!! I think I did something right! I opened the Command window in
Accessories, had to CD to the right directory (it failed somehow when I tried to just type in the whole line), then did "cscript MacroTest.vbs ABC 2". Sure enough, I get ABC 2 in the Command window! So at least (and at last!) ~something~ is working, Karl, thanks to the great patience of you and the others here! Where should I go for the next baby step? Ed Karl E. Peterson wrote: Didja try it at the command line? -- Working without a .NET? http://classicvb.org/ Ed wrote: Karl: I went back and re-read ~everything~ again, caught the bit about _removing_ the arguments, and even added the Sleep API as originally recommended. Everything runs without errors. Sleep is at 5000, so I get a good glimpse of the command window. It's totally blank. Unless you've got a brilliant flash of insight into my stumblings, I'm going to chalk it up as not knowing enough about what I want to do. With a long weekend coming up, I'll have to be re-trained next week anyway! So I'll read some more and play some more, and maybe give this another round on the NG in a week or three. Thanks to all for all the help and support. Ed Karl E. Peterson wrote: Ed wrote: You're not calling the TestMe subroutine? D'oh!! Okay, "Call TestMe" is now the first line. Then, at the command prompt Now I feel stupid! Never worked with DOS and command prompts before. Ah, okay, time to jump back in time a few decades... <g No matter what I try, I can't get it to work, so I must be trying wrong. Took out the line break and pasted in at StartRun - flashed a command window but no echos. Opened a command prompt from the Accessories menu and typed it in, got the following: C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest .vbs ABC 2 Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. C:\Documents and Settings\edward.millis\My Documents\Scripting\MacroTest.vbs(1, 1) Microsoft VBScript runtime error: Wrong number of arguments or invalid property assignment: 'TestMe' Wrong number of arguments means, well, just that. Did you notice that in my *re-typed* example, I removed the args to TestMe? ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Try again, this time heeding the error messages you get. <sigh Are you sure this is how Bill Gates started? He actually subcontracted the DOS prompt to a guy named Tim. ;-) -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Ed wrote:
OMG!! I think I did something right! I opened the Command window in Accessories, had to CD to the right directory (it failed somehow when I tried to just type in the whole line), then did "cscript MacroTest.vbs ABC 2". Sure enough, I get ABC 2 in the Command window! So at least (and at last!) ~something~ is working, Karl, :-) thanks to the great patience of you and the others here! Where should I go for the next baby step? Well, a messagebox might be a little more noticable than an echo? ---------------------------- Call TestMe Sub TestMe() Dim WshShell Set WshShell = WScript.CreateObject("WScript.Shell") Set Args = WScript.Arguments This = Args(0) That = Args(1) WshShell.popup This, , "Args(0)" WshShell.popup That, , "Args(1)" End Sub ---------------------------- Maybe edit that, and (after making sure it works from the command prompt!) trying it from VBA. -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Hi Ed,
While all of this is fascinating stuff, you do seem to be trying to use a hammer to put a screw in the wall. If you want to use regexp, you can do so even more easily from VBA than you can from VBScript. John Nurick already posted a web page describing how to do this http://www.j.nurick.dial.pipex.com/Code/index.htm If you want to pass parameters to a subroutine and return a value to the calling routine, this is also much easier in VBA. I pointed out a simple tutorial on this here http://www.word.mvps.org/FAQs/Macros...cArguments.htm There are code examples of both Subs and Functions in the VBA Help. Just position the cursor on the word "Sub" or "Function" in the VBA editor and press F1. Before you go any further down th VBScript route, I do urge you to take a look at these pages. -- 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" wrote in message oups.com... OMG!! I think I did something right! I opened the Command window in Accessories, had to CD to the right directory (it failed somehow when I tried to just type in the whole line), then did "cscript MacroTest.vbs ABC 2". Sure enough, I get ABC 2 in the Command window! So at least (and at last!) ~something~ is working, Karl, thanks to the great patience of you and the others here! Where should I go for the next baby step? Ed Karl E. Peterson wrote: Didja try it at the command line? -- Working without a .NET? http://classicvb.org/ Ed wrote: Karl: I went back and re-read ~everything~ again, caught the bit about _removing_ the arguments, and even added the Sleep API as originally recommended. Everything runs without errors. Sleep is at 5000, so I get a good glimpse of the command window. It's totally blank. Unless you've got a brilliant flash of insight into my stumblings, I'm going to chalk it up as not knowing enough about what I want to do. With a long weekend coming up, I'll have to be re-trained next week anyway! So I'll read some more and play some more, and maybe give this another round on the NG in a week or three. Thanks to all for all the help and support. Ed Karl E. Peterson wrote: Ed wrote: You're not calling the TestMe subroutine? D'oh!! Okay, "Call TestMe" is now the first line. Then, at the command prompt Now I feel stupid! Never worked with DOS and command prompts before. Ah, okay, time to jump back in time a few decades... <g No matter what I try, I can't get it to work, so I must be trying wrong. Took out the line break and pasted in at StartRun - flashed a command window but no echos. Opened a command prompt from the Accessories menu and typed it in, got the following: C:\Documents and Settings\edward.millis\My Documents\Scriptingcscript MacroTest .vbs ABC 2 Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. C:\Documents and Settings\edward.millis\My Documents\Scripting\MacroTest.vbs(1, 1) Microsoft VBScript runtime error: Wrong number of arguments or invalid property assignment: 'TestMe' Wrong number of arguments means, well, just that. Did you notice that in my *re-typed* example, I removed the args to TestMe? ---------------------------- Call TestMe Sub TestMe() Set Args = WScript.Arguments This = Args(0) That = Args(1) wscript.echo This wscript.echo That End Sub ---------------------------- Try again, this time heeding the error messages you get. <sigh Are you sure this is how Bill Gates started? He actually subcontracted the DOS prompt to a guy named Tim. ;-) -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Jonathan West wrote:
While all of this is fascinating stuff, you do seem to be trying to use a hammer to put a screw in the wall. That'd be relatively easy, compared to using a screwdriver to pound a nail! (My sense is he's closer to the latter than the former. <g) Before you go any further down th VBScript route, I do urge you to take a look at these pages. Yep, going the route I was helping with would need some serious justification, which could even include raging boredom I suppose. <g -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Sorry, guys. I couldn't find the Hammer or Screwdriver Functions in
VBScript. Guess I nees to go back and re-read about the tollbox? Seriously, thanks for all our help. You're right - I am beginning to see that I was doing things the _very_ hard way. Unfortunately, I didn't know enough at first to know that. I appreciate all the comments and pointers. I will come back when I can ask more intelligent questions. Ed Karl E. Peterson wrote: Jonathan West wrote: While all of this is fascinating stuff, you do seem to be trying to use a hammer to put a screw in the wall. That'd be relatively easy, compared to using a screwdriver to pound a nail! (My sense is he's closer to the latter than the former. <g) Before you go any further down th VBScript route, I do urge you to take a look at these pages. Yep, going the route I was helping with would need some serious justification, which could even include raging boredom I suppose. <g -- Working without a .NET? http://classicvb.org/ |
Call VBScript from VBA, pass variable, return result to macro?
Ed was telling us:
Ed nous racontait que : Sorry, guys. I couldn't find the Hammer or Screwdriver Functions in VBScript. Guess I nees to go back and re-read about the tollbox? Seriously, thanks for all our help. You're right - I am beginning to see that I was doing things the _very_ hard way. Unfortunately, I didn't know enough at first to know that. I appreciate all the comments and pointers. I will come back when I can ask more intelligent questions. There are no "unintelligent" questions when we are sincere. We all have been there at some point or another, and still are depending on the topic. So don't worry, keep at it, come back with questions anytime you need it! -- Salut! _______________________________________ Jean-Guy Marcil - Word MVP ISTOO Word MVP site: http://www.word.mvps.org |
Call VBScript from VBA, pass variable, return result to macro?
Ed wrote:
Seriously, thanks for all our help. You're right - I am beginning to see that I was doing things the _very_ hard way. That's often the fastest way to learn the most. <g Unfortunately, I didn't know enough at first to know that. See? I appreciate all the comments and pointers. I will come back when I can ask more intelligent questions. Good luck... -- Working without a .NET? http://classicvb.org/ |
| All times are GMT +1. The time now is 11:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com