Hi all,
I’m migrating from Office 365 to LibreOffice (LO), and I want to translate my VBA code to Python and use it in macros in Calc.
I’m using the latest LibreOffice 24.2 on MacOS 14.5. My Python installation is 3.11.9 (miniconda).
I’m having problems with the code below, that either reports an error or does nothing at all.
from __future__ import unicode_literals
import uno
def problematic_code():
# Get Current Document
doc = XSCRIPTCONTEXT.getDocument()
## CODING PROBLEM 1
# The Workbook has 2 Sheets: Sheet1 and Sheet2.
# If cell A1 in Sheet 1 has value 0, hide
# Sheet2 AND hide row 5 in Sheet1.
# Otherwise, show Sheet 2 and row 5 in Sheet1.
cell_value = doc.Sheets["Sheet1"]["A1"].getValue()
# Unfortunatelty, this does not work - returns error
if cell_value == 0:
doc.Sheets["Sheet2"].isVisible = False
doc.Sheets["Sheet1"]["A5"].Rows.isVisible = False
else:
doc.Sheets["Sheet2"].isVisible = True
doc.Sheets["Sheet1"]["A5"].Rows.isVisible = True
# CODING PROBLEM 2
# The Workbook has 3 Sheets: Sheet3 to Sheet5.
# Cell A1 in Sheet3 can assume values 1 to 3.
# If Cell A1 in Sheet3 has value of 1, then
# Cell A1 in Sheet4 will have a validity rule
# that allows for the values in cell range Sheet5!$A$1:$A$5.
# If Cell A1 in Sheet3 has value of 2, then
# Cell A1 in Sheet4 will have a validity rule
# that allows for the values in cell range Sheet5!$A$7:$A$11.
# If Cell A1 in Sheet3 has value of 3, then
# Cell A1 in Sheet4 will have a validity rule
# that allows for the values in cell range Sheet5!$A$13:$A$17.
cell_value = doc.Sheets["Sheet3"]["A1"].getValue()
# Unfortunatelty, this does not work - it doesn't return an error though
if cell_value == 1:
dv_list = doc.Sheets["Sheet4"]["A1"]
oValidation = dv_list.Validation
oValidation.Type = 6
oValidation.setFormula1("Sheet5!$A$1:$A$5")
elif cell_value == 2:
dv_list = doc.Sheets["Sheet4"]["A1"]
oValidation = dv_list.Validation
oValidation.Type = 6
oValidation.setFormula1("Sheet5!$A$7:$A$11")
elif cell_value == 3:
dv_list = doc.Sheets["Sheet4"]["A1"]
oValidation = dv_list.Validation
oValidation.Type = 6
oValidation.setFormula1("Sheet5!$A$13:$A$17")
else:
# By the way, here I would like to clear the validity rule.
pass
return
The error that I got from the hide/unhide bit is as thus:
# com.sun.star.uno.RuntimeException: Error during invoking function problematic_code in module file:///Applications/LibreOffice.app/Contents/Resources/Scripts/python/Example.py (<class 'AttributeError'>: isVisible
# File "/Applications/LibreOffice.app/Contents/Resources/pythonscript.py", line 916, in invoke
# ret = self.func( *args )
# File "/Applications/LibreOffice.app/Contents/Resources/Scripts/python/Example.py", line 16, in problematic_code
# doc.Sheets["Sheet2"].isVisible = False
# )
Maybe I’m not importing something.
Any help would be appreciated.
All the best,
FMVS