Styles and Formats

I have about over 200 different Excel_Condformats in my Styles and Formats in Calc…I cannot seem to batch delete them I seem to have to do each separate one by hand!..and even some of them reappear after being deleted!

How can I get rid of them for good and all in one go?

You can quickly delete all of these styles with a simple macro:

REM Remove the cell styles that begin with prefixStyleName
Sub removeStyles()
Dim oStyleFamilies As Variant
Dim oStyleFamilie As Variant
Dim aElementNames As Variant
Dim i%, count%
Const prefixStyleName = "Excel_CondFormat_"
	oStyleFamilies = ThisComponent.getStyleFamilies()
	oStyleFamilie = oStyleFamilies.getByName("CellStyles")
	aElementNames = oStyleFamilie.getElementNames()
	count = 0
	For i = LBound(aElementNames) To UBound(aElementNames)
		If Left(aElementNames(i), Len(prefixStyleName)) = prefixStyleName Then
			count = count + 1
	Next i
	MsgBox ("It was removed " + count + " style(s) with the names '" + prefixStyleName + "...'", 64, "Completed successfully")
End Sub

However, you must remember that you lose a set of conditional formats in the document. The conditions for the cells remain in the same form as in the original xls-file, but the design of the cells which correspond to these conditions will be “undefined”

Wow! Thank you for that…I can see how it works…but how and where do I run it?

Worked it out, thanks…that worked great!

Brilliant. Absolutely brilliant. Thank you thank you.

I moved from Windoze to Linux in 2002 and never looked back, except haltingly at my zillions of VB and VBA scripts. The OOffice attempt at a macro language was just 'way too arcane. This LibreOffice Basic version seems much better, but still not a match, unless I can find a really good tutorial, which I have not yet. Probably the most pressing need is for a keyboard recording macro, which is basically how I learned VB. Record a series of actions, then analyse them and now you know it, rather than spending hours wading through man pages…

Excellent solution. Thank you again, so much.

Kind regards, Andy

1 Like