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
oStyleFamilie.removeByName(aElementNames(i))
count = count + 1
EndIf
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