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

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

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

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

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

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

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

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

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/

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/

"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

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/

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.

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/

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.

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/

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/

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.

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/

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/

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/

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/

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/

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/

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/

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

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/