Trying to replicate VBA shell functionality...

[timdp]

We are trying to port some tools from Excel/VBA into Libre office - or rather at the moment testing if it’s feasible.

We use(d) excel to control many programmes that need to run in order using commands like:

Shell sbat, vbMinimizedNoFocus

where sbat would be something like:

sbat = "c:\something\program.bat arg1 arg2 arg3"

or
sbat = “c:\anaconda\python.exe c:\something\my_file.py arg1 arg2 arg3”

Using the shell command in libreoffice doesn’t work for us.

For a brief time I found the following appears to work:

Dim objExec As Object
objExec = createUnoService("com.sun.star.system.SystemShellExecute")

objExec.execute("C:\Anaconda\python.exe", "C:\dummy\out\temp.py", 0)

This appeared to replicate the VBA command:
shell “c:\anaconda\python.exe C:\dummy\out\temp.py”, vbMinimizedNoFocus

It worked briefly on my computer - however when I tried it again a week later, it failed. We tried it on other colleagues computers and it also didn’t work for them.

So far it seems there is no reliable way to use a ‘shell’ command in Libre Office and hence we have stopped migration into Libre. I would welcome any answers explaining how to make it work.

[timdp]

[EDIT]
Based on the responses so far - and searching on other threads - it sounds like the answer is Libre Office does not support a shell command similar to Excel-VBA.
The only option is to re-code everything to work directly from within Libre.

This is not an option for me.

I would be interested if anyone thinks their is a way to replicate shell.
[/EDIT]

for some reason when I tried this recently it stopped working.

A black box appeared and then vanished.

I modified the code so it would repeat the objExec line several times and took a screenshot.

There is an error in C:\Program Files (x86)\LibreOffice 5\program\python-core-3.3.3\lib\site.py line 173
The relevant line is

                print("Error processing line {:d} of {}:\n".format(n+1, fullname),
                  file=sys.stderr)

It appears to be a syntax error - if add the line

from __future__ import print_function

to the start of the file, then that line no longer gives an error but something else does.

This suggest to me my problem is simply that Libre is running things through python 2 rather than python 3 (I have python 2 installed on my system as the main version, and I don’t want to change it for numerous reasons).

Has anyone seen something like this, and knows how to change which version of python is being used?

[karolus]

probably the solution is very simple, but nobody can help until you show the content of C:\dummy\out\temp.py

[timdp]

I don’t see how that would help at all. Libre isn’t supposed to access that Python file directly, it should just send a command to windows to run it.

temp.py is a simple programme to copy a file - and yes I know that libre can do that, however I have lots of programmes to run via this spreadsheet - and they all worked in Excel using the “Shell” command.

If it is the case that no-one can help without telling me a modification based specificlly on this temp.py file then that’s not an answer.

[doug]

Use the built-in function Shell(). The help page is here. The syntax is very close to your VBA example:

sbat = "c:\something\program.bat arg1 arg2 arg3"
Shell(sbat, 6)

The documentation allows the dev to add more parameters with another comma, but I have the parameters in the first expression when I use it. I also use triple quotes to deal with the possibility of spaces in the filename.