CODEWITHSUNDEEP
pythonlibreofficeuno
Milton Johnson
Milton Johnson

Reputation: 13

Execute LibreOffice Calc Basic macro from python

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.

Upvotes: 1

Views: 1658

Answers (1)

Jim K
Jim K

Reputation: 13790

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((), (), ())

Upvotes: 1

Related Questions