Execute LibreOffice Calc Basic macro from python

Question

I am trying to automat a LibreOffice spreadsheet using Python. I get a desktop and open the spreadsheet with

file_url = uno.systemPathToFileUrl(os.path.abspath("/path/to/file/estimation.xlsm"))
doc = desktop.loadComponentFromURL(file_url, "_blank", 0, oo_properties(MacroExecutionMode=4))

The following code will print the basic script

the_basic_libs = doc.BasicLibraries
the_vba = the_basic_libs.getByName("VBAProject")
the_takerate = the_vba.getByName("TakeRate")
print(the_takerate)

The first lines of the module printed are:

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1

Public Sub TakeRateScenarioAnalysis()
Dim StartCell As Range

I get the script to with

oor = OORunner()
msp = oor.get_context().getValueByName("/singletons/com.sun.star.script.provider.theMasterScriptProviderFactory")
sp = msp.createScriptProvider("")
scriptx = sp.getScript("vnd.sun.star.script:VBAProject.TakeRate.TakeRateScenarioAnalysis?language=Basic&location=document")

The following error is returned

Traceback (most recent call last):
  File "./runProjectEstimate.py", line 198, in <module>
    scriptx = sp.getScript("vnd.sun.star.script:VBAProject.TakeRate.TakeRateScenarioAnalysis?language=Basic&location=document")
__main__.ScriptFrameworkErrorException: The following Basic script could not be found:
library: 'VBAProject'
module: 'TakeRate'
method: 'TakeRateScenarioAnalysis'
location: 'document'

Is there a problem with the script URI? I don't know why I can print the script but the script provider cannot find it.

After finding the script provider factory API document I noticed that the createScriptProvider call takes a context so I changed that line to msp.createScriptProvider(oor.get_context()) the result was the same error as before. — Milton Johnson
– Milton Johnson, Commented Apr 3, 2020 at 15:59
To make it easier to reproduce the problem, try to avoid names such as OORunner that are not defined in your question. In this case, it would have been better to simply write msp = ctx.getValueByName("/singletons/com.sun.star.script.provider.theMasterScriptProviderFactory"). — Jim K
– Jim K, Commented Apr 3, 2020 at 18:00

Jim K · Accepted Answer · 2020-06-18 22:34:49Z

1

The following worked for me:

scriptx = sp.getScript(
    'vnd.sun.star.script:Standard.Module1.TakeRateScenarioAnalysis?'
    'language=Basic&location=application')

However, as described in your question, this did not:

scriptx = sp.getScript(
    "vnd.sun.star.script:VBAProject.TakeRate.TakeRateScenarioAnalysis?"
    "language=Basic&location=document")

From these results, it does not seem possible to call macros this way when they are stored in documents. It is most likely a matter of permissions. Calling macros from documents that may have been created by someone else is a good way to spread viruses, so LO tries to prevent that.

Would it be acceptable to move your VBAProject library into My Macros instead of inside the document? Then everything should work as expected.

A couple of related links that may give more ideas:

EDIT:

There is a way to call macros stored in a document. Get the script provider from the document, not the master script provider.

oScriptProvider = doc.getScriptProvider()
oScript = oScriptProvider.getScript(
    "vnd.sun.star.script:Standard.Module1.SayHello?"
    "language=Basic&location=document")
oScript.invoke((), (), ())

edited Jun 18, 2020 at 22:34

answered Apr 3, 2020 at 17:55

Jim K

14.1k2 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Milton Johnson Over a year ago

Thanks, this solution worked. I moved the script to My Macros and the automation can find the script. I guess not running macros from the document is not just a suggestion, but enforced through the API.

Milton Johnson Over a year ago

One note for anyone with the same problem. The macro I was using was created in VisualBasic in Excel. When the macro was in the Document the VBA compatibility allowed the script to run when called from the UI. When I moved it to the My Macros library I could not get it to run in compatibility mode. So constructs like Frame("myNamedCell") would cause errors. I had to convert them to sheet.getCellRangeByName("myNamedCell") as well as other VBA syntaxes.

Jim K Over a year ago

You could set Option Compatible. However, I would recommend rewriting the commands instead, as you have done.

Collectives™ on Stack Overflow

Execute LibreOffice Calc Basic macro from python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related