Call VBScript from VBA, pass variable, return result to macro?

[Ed]

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

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

 

[John.Greenan]

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.

 

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

Ed,

Yes. I didn't understand the question. Sorry. I now recognize that
your question is over my head. Good luck.

 

[Helmut Weber]

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

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]

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

 

[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

 

[Ed]

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

 

[Karl E. Peterson]

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!

 

[Ed]

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

 

[Jonathan West]

Hi, Walter.


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 E. Peterson]

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.

 

[Jay Freedman]

Comment in-line about halfway down...

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.

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:


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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Ed]

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

 

[Ed]

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

Comment in-line about halfway down...

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.

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:


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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Karl E. Peterson]

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.

 

[Karl E. Peterson]

D'oh!! Okay, "Call TestMe" is now the first line.

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'

Wrong number of arguments means, well, just that. Did you notice that in my
*re-typed* example, I removed the args to TestMe?

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. ;-)

 

[John Nurick]

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.