1

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.

PLPeeters
  • 1,009
  • 12
  • 26
Milton Johnson
  • 13
  • 4
  • 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 Apr 03 '20 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 Apr 03 '20 at 18:00

1 Answers1

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((), (), ())
Jim K
  • 12,824
  • 2
  • 22
  • 51
  • 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 Apr 05 '20 at 16:19
  • 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. – Milton Johnson Apr 08 '20 at 22:13
  • You could set [Option Compatible](https://help.libreoffice.org/6.3/en-US/text/sbasic/shared/compatible.html). However, I would recommend rewriting the commands instead, as you have done. – Jim K Jun 18 '20 at 22:23