Ask Your Question
0

Trying to replicate VBA shell functionality...

asked 2016-06-17 12:24:14 +0100

timdp gravatar image

updated 2016-07-07 22:37:05 +0100

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-01-30 02:07:00 +0100

doug gravatar image

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.

edit flag offensive delete link more
0

answered 2016-07-06 15:40:10 +0100

timdp gravatar image

updated 2016-07-07 22:24:47 +0100

[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?

edit flag offensive delete link more

Comments

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

karolus gravatar imagekarolus ( 2016-07-06 17:34:36 +0100 )edit

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.

timdp gravatar imagetimdp ( 2016-07-07 13:07:16 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-06-17 12:24:14 +0100

Seen: 738 times

Last updated: Jan 30 '17