VBA Shell function doesn't work

[rmathews]

Folks --

The Shell in VBA in Office X always returns PID 0 and doesn't execute
anything, no matter what I try. I want to run a perl script. Similar
code works on Windows XP. Anyone know what's wrong? I'm running OS
10.4.8 and Office v.X with latest updates as of Dec 1, 2006 on a Ti
Powerbook.

What I've found so far:

1 VBA expects Macintosh-style paths using colons, in my case, something
like "TrustyG4 OS:bin:date", given that I want to exec /bin/date

2 Dir() finds date without a problem

3 Shell("TrustyG4 OS:bin:date") returns PID 0, no messages; obviously
works fine from Terminal to say % date

4 Shell("script.command") returns PID 0, no messages, doesn't the
script (chmod'd -x => rwx permissions); works fine to double-click it
from the Finder

I found a 2003-vintage related post titled "Several Errors/Dysfunctions
in VB-Macros for Excel X" with the same question. Just for the heck of
it, I tried:

5 Shell(MacID("MSWD")) , which still returns PID 0, but does in fact
launch word. But that doesn't do me any good given that I want to run
perl, not open an app in a window.

Ideas? Does this stuff work for you?

Thanks,

Rob

 

[rmathews]

Folks --

The Shell in VBA in Office X always returns PID 0 and doesn't execute
anything, no matter what I try...

One more piece of info: I tried downloading the Excel 2004 test drive.
No luck there, either -- same symptoms as for v.X.

This problem is really frustrating. The whole thing works fine on my
PC. Everything works on the Mac _except_ that I can figure out how
execute my perl script on my data. No workaround, except to run it by
hand. Aargh.

Thanks,

Rob

 

[rmathews]

Folks --

I solved the problem by using Shell() on Windows (actually, one of the
many Shell-and-wait snippets available, since I want synchronous
execution) and using Macscript() on OS-X/Office v.X. It only requires a
small bit of Applescript to hold everything together.

Here's the code.

R

Function ShellAndWaitMac(cmd As String) As Boolean
' Run a shell command cmd in the workbook directory, waiting for
completion
' To embed spaces within cmd, enclose the string in apostrophes, e.g.,
'name with space'
'
Dim scriptCmd As String ' Macscript command
Err.Clear
On Error GoTo scriptError
'
' Get current workbook directory
' VBA uses pre-OS-X, colon-delimited paths, e.g., TrustyG4
OS:Users:robesktop
' so use Applescript to convert to POSIX name, e.g., /Users/rob/Desktop
Dim posixcwd As String
scriptCmd = "POSIX path of """ & ThisWorkbook.path & """"
posixcwd = MacScript(scriptCmd)
'
' Compose UNIX shell command, then wrap it Applescript and execute
Dim shcd As String, shcmd As String
Dim result As String
' cd to current POSIX dir, quoted in case of embedded spaces
shcd = "cd " & "'" & posixcwd & "'"
' add the command and its arglist
shcmd = "; " & cmd
' embed for Applescript
scriptCmd = "do shell script """ & shcd & shcmd & """"
result = MacScript(scriptCmd) ' result contains stdout, should you care
ShellAndWaitMac = True
Exit Function
'
' Error, presumably executing MacScript()
scriptError:
Dim Msg As String
Msg = "Error # " & Str(Err.Number) & " from " _
& Err.Source & ": " & Err.Description & vbNewLine _
& "Macscript = " & scriptCmd
MsgBox Msg, , "ShellAndWaitMac"
ShellAndWaitMac = False
End Function

Sub testShellAndWait()
Debug.Print ShellandWait("pwd")
End Sub