CODEWITHSUNDEEP
shellmacrosopenoffice-calcopenoffice-basic
Mark
Mark

Reputation: 343

Run shell command and return output as result of custom function

I would like to write a custom OpenOffice function that runs a shell command and puts the result into the cell from which it was invoked. I have a basic macro working, but I can't find a way to capture the command's output. 

Function MyTest( c1 )
    MyTest = Shell("bash -c "" echo hello "" ")
End Function

The above always returns 0. Looking at the documentation of the Shell command, I don't think it actually returns STDOUT.

How would I capture the output so that I can return it in my function?

Upvotes: 3

Views: 8800

Answers (3)

tomy
tomy

Reputation: 464

You can redirect the output of your system command to a temporary file and read afterwards in one go, like the other answer from @'Kimball Robinson' suggested. The underlined macro must be saved under 'Tools >> Macros >> Edit Macro' and can be used immediate in all libreoffice calc documents afterwards in a regular cell formula. This was tested with LibreOffice Version 24.8:

Public Function SystemCmd(sCmd As String) As String
    Dim iNumber As Integer
    Dim sLine As String
    Dim aFile As String
    Dim sMsg As String
    aFile = "/tmp/calc_shell_macro.txt"
    
    'iNumber = Freefile
    'Open aFile For Output As #iNumber
    'Print #iNumber, "This is the first Line"
    'Print #iNumber, "This is the second Line"
    'Close #iNumber
    Shell("bash -c "" " & sCmd & " > " & aFile & " "" ", 2, "", true)
    
    iNumber = Freefile
    Open aFile For Input As iNumber
    While Not eof(iNumber)
        Line Input #iNumber, sLine
        If sLine <>"" Then
            sMsg = sMsg & sLine & chr(13)' & chr(10)
        End If
    Wend
    Close #iNumber
    SystemCmd=sMsg
End Function

using:

SystemCmd("sleep 1s; echo 123;")

you can re-evaluate all formulas with Ctr+Shift+F9

Upvotes: 0

Pierre-Antoine
Pierre-Antoine

Reputation: 2094

Kimbal Robinson's answer works, but has the disadvantage of using a file. It is slow (compared to using memory only), and you have to delete the file afterwards (although by putting it in /tmp, it will usually be deleted automatically eventually).

An alternative is to have the data transit via the clipboard:

Dim myCommand As String
myCommand = "echo hello world"
Shell ("bash", 1, "-c "" " & myCommand & " | xclip -selection clipboard"" ", true)
' setting the 4th parameter to true ensures that the Shell function will wait until '
' the command terminates before returning '

' then paste the content of the clipboard '
Dim dh As Object
dh = createUnoService("com.sun.star.frame.DispatchHelper")
dh.executeDispatch(StarDesktop.CurrentFrame, ".uno:Paste", "", 0, Array())

This will paste the output of the command at the current point of the active document.

Note that, if the command issues several lines, the "text import" dialog will pop up (I don't know how to prevent that).

Upvotes: 1

Kimball Robinson
Kimball Robinson

Reputation: 3388

Can you redirect the output to a temporary file, then read in the contents of that file with another command?

eg, Shell("bash -c "" echo hello > tmp.txt "" ")

Upvotes: 1

Related Questions