How to change "Spreadsheet Theme" from LO Basic

bharatvaj · July 23, 2024, 6:58am

I’m trying to automate Spreadsheet Theme change via basic macro, but I can’t seem to find the API for it.

I tried the following resources,

Andrew Pitonyak’s book (I read the book and could only find resources related to StyleFamilies)
CalcCommands.xcu (revision ec5e0d0c) - OpenGrok cross reference for /core/officecfg/registry/data/org/openoffice/Office/UI/CalcCommands.xcu

Macro recorder gives me this,

dispatcher.executeDispatch(document, ".uno:ChooseDesign", "", 0, Array())

but I want the Spreadsheet Theme to be changed when running the macro, not open the dialog box.

Any suggestions are appreciated, thanks.

Ash733 · July 23, 2024, 8:15am

I have not used this in a macro at all but I think your right, I can’t find any way to actually change the value; it only opens the dialog box. Hoping someone with more macro experience in sheet themes chimes in.

Villeroy · July 23, 2024, 9:59am

<install_dir>/share/template/common/wizard/styles has the spreadsheet templates corresponding to the themes. The program seems to merge the styles of the selected template with the styles of the current document.

cwolan · July 23, 2024, 10:25am

Indeed.

I would take a look at the LibreOffice library “Template”, module: “Samples”
(esp. the “SelectStyle” sub).
⠀

bharatvaj · July 23, 2024, 3:18pm

Thanks for sharing this! It might not be the exact solution I needed, but it’s super helpful for resolving future issues.

Villeroy · July 23, 2024, 11:20am

This seems to do the job.

Sub PumpkinTheme
	applyTheme "pumpkin.ots"
End Sub

Sub applyTheme(sFileName As String)
GlobalScope.BasicLibraries.loadLibrary("Tools")
oFamilies = ThisComponent.StyleFamilies
Dim aOptions(0) as New com.sun.star.beans.PropertyValue
StylesDir = GetOfficeSubPath("Template", "wizard/styles/")
StylePath = StylesDir & sFileName
aOptions(0).Name = "OverwriteStyles"
aOptions(0).Value = true
oFamilies.loadStylesFromURL(StylePath, aOptions())
End Sub