VBA shell function help

[Junmin Liu]

does anyone know how the shell function work in Mac?
show me an example will be great
thanks,

 

[JE McGimpsey]

does anyone know how the shell function work in Mac?
show me an example will be great

XL/VBA Help has a Mac example:

Shell MacID("MSWD")

While the WindowStyle argument is supposed to determine whether the
application takes the focus or not, but it's broken in XL04.

 

[Junmin Liu]

hi, thanks that help.

but i try the example:

RetVal = Shell("Macintosh HD:Applications:Calculator", vbNormalFocus)
MsgBox RetVal

I got "0" returned, which means it doesn't work

actually i really want to do is to use shell to execute some perl
scripts, e.g.:

RetVal = Shell("Macintosh HD:usr:binerl myperl.pl", vbNormalFocus)

any suggestions will be greatly appreciated!

 

[JE McGimpsey]

but i try the example:

RetVal = Shell("Macintosh HD:Applications:Calculator", vbNormalFocus)
MsgBox RetVal

Well, you'd actually have to have an application named Calculator in
your Applications folder for it to work. For application packages, you
have to dig a little deeper

This works for me with the as-shipped OS X Calculator.app:

Dim RetVal As Variant
RetVal = Shell("Macintosh HD:Applications:Calculator.app:" & _
"Contents:MacOS:Calculator", vbNormalFocus)
MsgBox RetVal

I got "0" returned, which means it doesn't work

actually i really want to do is to use shell to execute some perl
scripts, e.g.:

RetVal = Shell("Macintosh HD:usr:binerl myperl.pl", vbNormalFocus)

I find it much easier, if a bit more verbose, to use MacScript
(Applescript) for shelling perl. For instance:

MacScript("do shell script ""/usr/bin/perl myperl.pl""")

It's a tad slower, but since VBA will not be included in Office2008,
using Applescript gives a leg up for upgrading.

 

[Junmin Liu]

Hi, Thanks a lot for the help!
indeed additional arg. helps open up the calculator.

But actually I wrote couple perl scripts which will run against the
excel files user generate. I do want to write excel add-in, so users can
edit their excel files while run my perl script. I provide my custom
menu items in excel.

So can you tell me how to run the perl scripts in VBA? I have searched
on google for two days, and couldn't find any clue.

---junmin

 

[Junmin Liu]

sorry, i misunderstood your message. the macscript solution works.
thanks a lot!!!

---junmin

 

[Junmin Liu]

how to pass the parameter in?

Dim st As String

st = "argus"

MacScript("do shell script ""/usr/bin/perl myperl.pl" + st + """)

thanks,
--junmin

 

[JE McGimpsey]

how to pass the parameter in?

Dim st As String

st = "argus"

MacScript("do shell script ""/usr/bin/perl myperl.pl" + st + """)

Just like building any other string (though I'd recommend using the
concatenation operator, &, to concatenate rather than +). Your example
needs a space before after .pl, and the final quotation mark should be
doubled:

MacScript("do shell script ""/usr/bin/perl myperl.pl " & st & """")

 

[Junmin Liu]

Hi, JE
thanks for the great help.

question:

looks like
RetVal = MacScript("do shell script ""env | grep PATH""")
MsgBox RetVal

the output I got is different from I do it in terminal:
env | grep PATH

in .bash_profile I added couple more path vars
export PATH=$XSDDOC_HOME/bin:$M2_HOME/bin:$PATH

I cann't got those additional paths, or some env vars like
"JAVA_HOME" in VBA.


How can I get those env vars like "java_home" via VBA?
---junmin

 

[JE McGimpsey]

the output I got is different from I do it in terminal:
env | grep PATH

do shell script uses the sh shell, not bash. For help in sending do
shell script commands, see

http://developer.apple.com/technotes/tn2002/tn2065.html

 

[Roger Morris]

do shell script uses the sh shell, not bash. For help in sending do
shell script commands, see

http://developer.apple.com/technotes/tn2002/tn2065.html

which includes this note:

Q: What shell does do shell script use, really?
A: do shell script always calls /bin/sh. However, in Mac OS X, /bin/sh
is really a copy of another shell that emulates sh. In 10.2 and later,
this is bash; prior to that it was zsh.

Roger

 

[JE McGimpsey]

which includes this note:

Q: What shell does do shell script use, really?
A: do shell script always calls /bin/sh. However, in Mac OS X, /bin/sh
is really a copy of another shell that emulates sh. In 10.2 and later,
this is bash; prior to that it was zsh.

So when sh is emulated by bash, are bash's environment variables
emulated too?

 

[Roger Morris]

So when sh is emulated by bash, are bash's environment variables
emulated too?

I was hoping someone else would give a definitive answer to this. But
here's my understanding of it:

/sh/ is an early and major unix shell by S.R.Bourne - hence the name
'Bourne shell'.
Then there is a (newer) superset of /sh/ - it was called the 'bourne
again shell' /bash/.

So (I'm assuming) the /bash/ environment is a superset of the /sh/
environment and setting/using any /bash/ environment variables is
equivalent to setting/using the subset /sh/ ones.

http://developer.apple.com/technotes/tn2002/tn2065.html was the link
(to Apple's shell script developer notes) provided by John McGimpsey and
from this I gather that:

In OS X 10.2 and later if we try to use /sh/ we are actually using
/bash/ so environment variables *are* /bash/ and there is no distinction
between /sh/ and /bash/

Further, if we can't call /sh/ (without getting /bash) we can't conduct
comparitive tests. (I wonder if this is really true)

Sorry if this isn't a precise explanation. Perhaps a better one could be
obtained from one of the unix or Mac programming groups or from an Apple
developer forum.

Roger