Reputation: 10236
In OSX Excel provides functionality to run Applescript code via the MacScript
keyword. I am running shell code via this functionality and it generally looks like this:
MacScript "do shell script ""/usr/bin/whatever"""
recently I decided I wanted to capture the output (and stderr) because the command was failing and I wanted to see what the error was... so I rewrote it like this:
MacScript "do shell script ""/usr/bin/whatever > /tmp/out 2>&1"""
but I get the error above. if I run the given command in the AppleScript editor, I get the error:
The command exited with a non-zero status (number: 1)
so my question is: how do I trap the return code of MacScript
and prevent Excel from breaking? I tried:
Dim rc As Integer: rc = MacScript(...)
but the script still breaks!
Upvotes: 1
Views: 1811
Reputation: 440689
Generally, to prevent do shell script
(and therefore, indirectly, MacScript()
) from throwing an error, make sure that the shell command exits with code 0
.
In your case, to merely capture the shell command's exit code, append ; echo $?
to the command string passed to do shell script
:
Simplified example, using a malformed date
command:
Dim rc As Integer
rc = MacScript("do shell script ""date -nosuchoptions; echo $?""") ' returns 1
echo $?
outputs the (preceding) command's exit code to stdout and is therefore returned by do shell script
0
, because the echo
command succeeds; therefore, the overall command exits with code 0
, thus preventing do shell script
from throwing an error.Caveats:
MacScript
's return value to an Integer
variable, be sure that the do shell script
command's output can be parsed as a number.echo $?
is guaranteed to output a "number-looking" string.)MacScript
will still throw an error; you could use this to distinguish between syntax and runtime errors.If, by contrast, you still want to return a command's output and simply know in the abstract whether something went wrong:
Dim stdout As String
On Error Resume Next ' ignore runtime errors
stdout = MacScript("do shell script ""date -nosuchoptions""")
If Err.Number <> 0 Then
MsgBox "Something went wrong.", vbExclamation
Else
MsgBox "Captured output: " & stdout, vbInformation
End If
5
(Invalid procedure call or argument
).Finally, you can combine the 2 approaches - return the command's output and its specific exit code:
Dim stdout As String, rc As Integer, pos As Integer
' Execute the command, and appends its exit code as the *last line* of output.
' Note how both stdout and stderr output are captured via `2>&1`.
stdout = MacScript("do shell script ""{ date; date -nosuchoptions; } 2>&1; echo $?""")
' Extract the last line from the output captured.
pos = InStrRev(stdout, Chr$(13)) ' find last line break (\n has been translated to \r by `do shell script`)
rc = Mid(stdout, pos + 1) # exit code
stdout = Left(stdout, pos - 1) # captured output (only)
If rc <> 0 Then
MsgBox "Command failed with exit code " & rc & "; captured output: " & stdout, vbExclamation
Else
MsgBox "Captured output: " & stdout, vbInformation
End If
Caveats:
MacScript
will still throw an error; you could use this to distinguish between syntax and runtime errors.echo $?
) doesn't terminate its output with \n
, parsing out the exit code will not work.Upvotes: 2