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

Greg
Thu Jun 29 09:42:13 CDT 2006

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

JohnGreenan
Thu Jun 29 10:03:02 CDT 2006

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
>
>

Ed
Thu Jun 29 10:20:57 CDT 2006

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

Greg
Thu Jun 29 10:28:17 CDT 2006

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

Helmut
Thu Jun 29 10:32:18 CDT 2006

Hi Ed,

is there something in VBS you can't do in VBA?
If so, you could write data into a location,
a file, the clipboard (dataobject) or
into some specified location of the memory.
(For the very, very advanced, unlike me),
and have your VBS read it from there.

The same applies to reading the results from VBS.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Walter
Thu Jun 29 10:37:55 CDT 2006

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" <prof_ofwhat@yahoo.com> wrote in message
news:1151590604.940766.253250@m73g2000cwd.googlegroups.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
Thu Jun 29 11:14:50 CDT 2006

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
> >
> >

Ed
Thu Jun 29 11:25:04 CDT 2006

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" <prof_ofwhat@yahoo.com> wrote in message
> news:1151590604.940766.253250@m73g2000cwd.googlegroups.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
Thu Jun 29 11:33:14 CDT 2006

Hi, Helmut.

> is there something in VBS you can't do in VBA?
Actually, it's probably more like I haven't learned yet how to make VBA
do everything it really can do!

> If so, you could write data into a location,
> a file, the clipboard (dataobject)
I think that's where I'm headed, though maybe a file more than the
clipboard. I had hoped there was an easier way.

Ed

Helmut Weber wrote:
> Hi Ed,
>
> is there something in VBS you can't do in VBA?
> If so, you could write data into a location,
> a file, the clipboard (dataobject) or
> into some specified location of the memory.
> (For the very, very advanced, unlike me),
> and have your VBS read it from there.
>
> The same applies to reading the results from VBS.
>
> --
> Greetings from Bavaria, Germany
>
> Helmut Weber, MVP WordVBA
>
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"

Karl
Thu Jun 29 11:55:29 CDT 2006

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/archive299-2005-1-335409.html
Debug.Print "VBS Return Value: "; proc.ExitCode

Hope this helps!
--
Working without a .NET?
http://classicvb.org/

Ed
Thu Jun 29 13:01:30 CDT 2006

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/archive299-2005-1-335409.html
> Debug.Print "VBS Return Value: "; proc.ExitCode
>
> Hope this helps!
> --
> Working without a .NET?
> http://classicvb.org/

Jonathan
Thu Jun 29 13:41:57 CDT 2006


"Ed" <prof_ofwhat@yahoo.com> wrote in message
news:1151598304.462496.82320@x69g2000cwx.googlegroups.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/MacrosVBA/ProcArguments.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

Karl
Thu Jun 29 13:59:08 CDT 2006

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\Scripting>cscript
MacroTest.vbs ABC 2

You should see your parameters echoed at this point.
--
Working without a .NET?
http://classicvb.org/

Jay
Thu Jun 29 14:31:53 CDT 2006

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\Scripting>cscript
> 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.

Ed
Thu Jun 29 14:51:10 CDT 2006

> 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 Start>>Run - 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\Scripting>cscript
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\Scripting>cscript
> MacroTest.vbs ABC 2
>
> You should see your parameters echoed at this point.
> --
> Working without a .NET?
> http://classicvb.org/

Ed
Thu Jun 29 15:02:48 CDT 2006

> 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\Scripting>cscript
> > 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.

Karl
Thu Jun 29 15:10:01 CDT 2006

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/

Karl
Thu Jun 29 15:14:09 CDT 2006

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 Start>>Run - 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\Scripting>cscript
> 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/

John
Thu Jun 29 15:19:30 CDT 2006

On 29 Jun 2006 09:25:04 -0700, "Ed" <prof_ofwhat@yahoo.com> 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" <prof_ofwhat@yahoo.com> wrote in message
>> news:1151590604.940766.253250@m73g2000cwd.googlegroups.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.

Ed
Thu Jun 29 16:45:12 CDT 2006

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 Start>>Run - 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\Scripting>cscript
> > 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/

Karl
Thu Jun 29 17:09:25 CDT 2006

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 Start>>Run -
>>> 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\Scripting>cscript 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
Thu Jun 29 17:58:30 CDT 2006

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 Start>>Run -
> >>> 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\Scripting>cscript 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/

Karl
Thu Jun 29 18:45:00 CDT 2006

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/

Jonathan
Fri Jun 30 06:13:44 CDT 2006

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/MacrosVBA/ProcArguments.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" <prof_ofwhat@yahoo.com> wrote in message
news:1151621909.992330.167110@d56g2000cwd.googlegroups.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.
>> >