How to run 'AppleScriptTask' from LibraOffice Calc?

[nap]

I’m migrating a spreadsheet I have from MS Excel to Calc, and my (VBA) macro is not working. I’ve tried to find a solution that explains what needs to change, but I haven’t been successful thus far.

In MS Excel, I run the script using:

myScriptResult = AppleScriptTask("/Users/user/myScripts/showFile.scpt", "myapplescripthandler", fileName)

Where fileName contains a fully qualified path to the file I want pass to the showFile script. However, I get an error on this line:

Error saving the document MySpeadsheet:
BASIC runtime error.
'35'
AppleScriptTask

Can anyone suggest, either through a link or a code snippet, how can I fix this, please?

[joshua4]

There may be a direct way, but if you can get a bash instance on your Mac, then what about:

Sub ExecuteScript
	Dim oSvc as object

	oSvc = createUnoService("com.sun.star.system.SystemShellExecute")
	oSvc.execute("bash", "/home/username/directory/bashscript.sh", 0)

Exit Sub

Then look at macos - Run AppleScript from bash script - Ask Different for running the AppleScript from the bash. Basically it looks like running Java or whatever, using osascript executable to start the process. Remember, you may have to be careful about ownership and executable on the bash script itself.

[nap]

@joshua4 :Thanks!!!
That is so much simpler than having to work through AppleScript! Awesome!

[nap]

One small question (since I can’t find a proper explanation of the createUnoService Class’ execute method.

How do I pass parameters to bash?

[joshua4]

Your quoted second parameter is your whole thing, so if you can execute

myBigMac#>myscript.sh myparam1 myparam2

from the command line, then just put myparam1 and myparam2 in the same quotes as the myscript.sh, just like on the command line. I think…not sure I’ve done it. But the docs seem to say so:

https://www.openoffice.org/api/docs/common/ref/com/sun/star/system/XSystemShellExecute.html

[nap]

I’m doing that, but I’m getting the following error:

BASIC runtime error.
'1'

Type: com.sun.star.system.SystemShellExecuteException
Message: Undefined error: 0

My script works fine when I don’t pass parameters (i.e. & " " & filename is excluded from the assignment to commandStr. But it doesn’t work when included, as below:

commandStr = "/Users/user/myScripts/bashStrManipulation.sh" & " " & filename
MsgBox commandStr
oSvc = createUnoService("com.sun.star.system.SystemShellExecute")
oSvc.execute("bash", commandStr, 0)

Note; the MsgBox shows the correct string, though it appears there may be a new-line in between, which I have no idea where it could come from, but that’s what I’m going to work on.
EDIT: No, it’s not a new line. I’m going to try and quote the whole string.

[joshua4]

Hope someone who’s done it pops in here. What about %20 for spaces, that is, URL encoded?

[nap]

@joshua4 : Thanks. I’ll try that, and keep searching. You’ve been most helpful pointing me in this direction. When I figure it out, I will complete the thread.

[nap]

Just noting this issue seems to have been around for a while:
https://bz.apache.org/ooo/show_bug.cgi?id=125117

[nap]

Ok, here’s a version that works. It assumes the script has a SHEBANG (i.e. it is executable):

commandStr = "/Users/user/myScripts/bashStrManipulation.sh"
paramStr = fileName
oSvc = createUnoService("com.sun.star.system.SystemShellExecute")
oSvc.execute(commandStr, paramStr, 0)

[joshua4]

Great. Just execute from the shebang as it were and let the parameters be parameters. Great, I’ve been thinking about your question, and I’ve already thought of uses for passing parameters. Thanks.

[nap]

No problem! Thanks for your thoughts.
My application of this is now working perfectly, so I’m happy here.

Cheers, Nap.

[nap]

Maybe edit your post which I flagged as the solution with the additional info? Might save others having to read through the entire thread.