[Solved] [Calc] FindCreateNumberFormatStyle not defined

[FDB]

OSX 10.14.6 Mojave
Apache OpenOffice 4.1.14

When using FindCreateNumberFormatStyle for setting a date in a footer in CALKC, I get an error : "Sub-procedure or function not defined"
The script is a slight variant of the script from Andrew Pitonyak (https://pitonyak.org/AndrewMacro.pdf, section 6.17)

I am using this script in a calc document as a template (.OTS)

Code: Select all

Sub Main
	GlobalScope.BasicLibraries.LoadLibrary("Tools")	' loading Tools library
	SetFooterDateInSpreadSheet ()
End Sub

Sub SetFooterDateInSpreadSheet
     Dim oDoc, oSheet, oPstyle, oHeader, oFooter
     Dim oText, oCursor, oField
     Dim oStyles
     Dim sService$
     Dim ODateTime
     oDoc = ThisComponent
     ' Get the pagestyle for the currently active sheet.
     oSheet = oDoc.CurrentController.getActiveSheet
     oStyles = oDoc.StyleFamilies.getByName("PageStyles")
     oPstyle = oStyles.getByName(oSheet.PageStyle)
     ' Turn headers on and then make them shared!
     oPstyle.FooterOn = True
     oPstyle.FooterShared = True
     ' The is also a RightText and a LeftText
     oFooter = oPstyle.RightPageFooterContent    
     oText = oFooter.RightText
     ' You may now set the text object to be anything you desire
     ' Use setSTring() from the text object to set simple text.
     ' Use a cursor to insert a field (such as the current sheet name).
     ' First, clear any existing text!
     oText.setString("")

	 s = "com.sun.star.text.TextField.DateTime"
     ODateTime = oDoc.createInstance(s)
     oDateTime.IsFixed = TRUE
       oDateTime.NumberFormat = FindCreateNumberFormatStyle("DD. MMMM YYYY", oDoc)
       oText.insertTextContent(oTCurs,oDateTime,FALSE)
       oText.insertString(oTCurs," ",FALSE)
     oCursor = oText.createTextCursor()
'---     oText.insertString(oCursor,  "Sheet: ", False)
     ' This will have the sheet name of the current sheet!
'     sService = "com.sun.star.text.TextField.SheetName"
'---     sService = "com.sun.star.text.TextField.SheetName"
'     oField =  oDoc.createInstance(sService)
'     oText.insertTextContent(oCursor, oField, False)
     ' And now for the part that holds the entire thing together,
     ' You must write the header object back because we have been
     ' modifying a temporary object
	 oPstyle.RightPageFooterContent = oFooter
End Sub

Q : is this function defined in a library I am not aware of ?

advTHANKSance

[Bidouille]

Try a search next time: code snippet

And post in the right place: viewforum.php?f=20

[Lupp]

findCreateNumberFormatStyle() is a wellknown UDF published many years ago by Andrew Pitonyak in his famous texts on programming for LibreOffice using Basic.
You posted "The script is a slight variant of the script from Andrew Pitonyak."
But also "Is this function defined in a library I am not aware of ?

This is not consistent.
Afaik it was never included with macros distributed with any version of LibO. There is a module 'Tools' in the 'Application Macros', but it should be read-only, and will not contain that UDF.

If you also created a module 'Tools' in your 'My macros...' range, it should be in the library 'Standard' and you can find functions there there searching for the name. There is no need to load the 'Standard library' explicitly.

If the UDF is not present, you can't use it.
If you "slightly changed" the code, you will also know in what module you placed it.
If it's not present, you can't use it, of course, but you can copy it again from a source, and paste it to the module of your choice.

BTW1 The function returns neither a style nor a FormatString, but a Key.
In some places in the API the property name NumberFormat is misused for that key.

The "findCreate..." must be understood as "Find the Key if the described NumberFormat already exists. Otherwise create the respective NumberFormat object, add it to the NumberFormats object of the documnent, and return the Keyt it gets assigned this way."

BTW2 Don't invent new date formats. There are more than enogh of them in the world, and most of them are misleading. Use ISO 8601 extended: YYYY-MM-DD. You surely don't write for people unable to understand this one actually global date format.