CODEWITHSUNDEEP
macosexcelapplescriptvba
ekkis
ekkis

Reputation: 10236

MacScript Invalid procedure call or argument

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

Answers (1)

mklement0
mklement0

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
  • as a - desirable - side effect, the exit code will be set to 0, because the echo command succeeds; therefore, the overall command exits with code 0, thus preventing do shell script from throwing an error.

Caveats:

  • Since you're assigning MacScript's return value to an Integer variable, be sure that the do shell script command's output can be parsed as a number.
    (The above command is safe, as is yours from the question, because the preceding command's stdout output is captured in a file, and the subsequent echo $? is guaranteed to output a "number-looking" string.)
  • If your shell command is syntactically incorrect, 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
  • Note that this method does not allow you to determine the shell command's specific exit code, because Excel VBA translates any nonzero shell exit code into the generic error number 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:

  • If your shell command is syntactically incorrect, MacScript will still throw an error; you could use this to distinguish between syntax and runtime errors.
  • If your shell command (the one preceding echo $?) doesn't terminate its output with \n, parsing out the exit code will not work.

Upvotes: 2

Related Questions