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]

In article <e17Eqolb...@TK2MSFTNGP04.phx.gbl>,
Junmin Liu <jun...@pcbi.upenn.edu> wrote:

>
> 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:bin:perl myperl.pl", vbNormalFocus)

any suggestions will be greatly appreciated!

[JE McGimpsey]

In article <ekrZmUpb...@TK2MSFTNGP05.phx.gbl>,
Junmin Liu <jun...@pcbi.upenn.edu> wrote:

> 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:bin:perl 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]

In article <#euqfP7b...@TK2MSFTNGP05.phx.gbl>,
Junmin Liu <jun...@pcbi.upenn.edu> wrote:

> 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 wrote:
> In article <#euqfP7bHHA.20.phx.gbl>,

[JE McGimpsey]

In article <#lWR52ic...@TK2MSFTNGP04.phx.gbl>,
Junmin Liu <jun...@pcbi.upenn.edu> wrote:

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

JE McGimpsey <jemcg...@mvps.org> wrote:

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]

In article <1hvw82j.uqhdkp1y0kawqN%r...@rmfsnewsXL.fsnet.co.uk.invalid>,
r...@rmfsnewsXL.fsnet.co.uk.invalid (Roger Morris) wrote:

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

JE McGimpsey <jemcg...@mvps.org> wrote:

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