Umlaut in CopyByName?

[LibreOfficePerson]

I’ve been trying to copy and rename a sheet using this code:

Sub Main
sheet = "Vorlage"
newsheet = format(now, "MMMM") + format(now, "YYYY")
doc = ThisComponent
sheets = doc.getSheets
If sheets.hasByName(sheet) Then
	sheets.CopyByName(sheet, newsheet, sheets.Count - 1)
Else 
	MsgBox("Vorlage fehlt")
EndIf

End Sub

Since my locale is German, the format(now,“MMMM”) will yield the result “März”. Which leads to this error:
Type: com.sun.star.uno.RuntimeException
Message: .

The code runs fine if I only use the year. So how do I fix this?

[dajare]

Any help in this document?

[LibreOfficePerson]

I’m stupid. A sheet of the same name already existed from a previous run of the macro. The error message didn’t exactly help either. Runs fine now.

[Lupp]

In fact this was what I suspected. You may feel free, however, to read my answer nonetheless.
(Of course, LibO API should add a useful message to the ExceptionAlert.)

[LibreOfficePerson]

Thank you for your help! Much appreciated.

[Lupp]

The “ä” in newSheetName is not the cause of this problem! To be sure, I tried again. As I generally use an English UI and locale I even changed to German UI / locale, and the “März” was accepted as the new SheetName running your “macro”.
Based on no information guessing the actual reason won’t yield a result.

By the way:
Spreadsheet documents of the kind “OneSheetPerMonth” are mostly bad design. (Done again and again, however.)
Anyway: Basically avoid some problems in specific occuring with user programming by restricting SheetNames to the classical syntax of names in programming:
Start with a letter of the unextended Latin alphabet.
Append then zero or more characters taken from LatinAlphabet again, DecimalDigit, UnderscoreCharacter and nothing else.

Otherwise you will need the silly single quotes here and there which will never be part of the true SheetName …

I you feel sure that in your specific case a sheet per month is the unavoidable decision, name the sheets like
= "M_" & format(now,"YYYY""_""MM")

[LibreOfficePerson]

If no information is given, I can’t only guess. That’s on LibreOffice and its incredibly vague exception in this case.

I appreciate you taking the time to lay this out for me. I’m however merely following the rules set by my department. I suspect the trouble of changing them is not worth the effort.
Lastly I’d like to add that computers are there to serve us, not the other way around. Since the sheetname is an external representation (i.e. it is seen by the users) it should be user-friendly.

[Lupp]

Quoting @LibreOfficePerson: “Lastly I’d like to add that computers are there to serve us, not the other way around.”
This argument applied to the current case would require additional discussion from my view. Based on it without the amendment of “cum grano salis” led to lots of complications showing up as bugs. And neither free software nor commercial hokum of the Office kind will ever succeed with fixing them. They behave like the Lernaean Hydra if attacked.

[Lupp]

Another joke: Your purchaser or whatever decides to start activities in Austria. Who will write the macro then replacing “Januar” with “Jänner” and to adapt any references based on text constants probably (like when using the fifth parameter of ADDRESS(), e.g.)?
This kind of user-friendliness blocks international cooperation, portability and …
For dates and related information in text form there is ISO 8601. And it’s very human-friendly to comply with it as far as possible. Howgh!

[LibreOfficePerson]

Another joke: My employer decides to change my time card format to the Chinese calendar. Now your naming scheme is broken too.
The entire way date and time work on computers is completely broken, because these are not strictly logical systems but cultural norms, same as language. LibreOffice thinks Sunday is the first day of the week whereas most of the world doesn’t even have a “Sunday” and most of the remaining world uses Monday. Localization can only do so much when the foundation is broken.

[Lupp]

Quoting @LibreOfficePerson: “The entire way date and time work on computers is completely broken,”
This is not true. There is lots of need to exchange information accross cultural borders, and there are standards to allow for this without creating misunderstandings wherever possible. For data generally represented by numbers it is possible, and for the important field of dates ISO 8601 set the standards.
BTW: Even Russia accepted the Gregorian calendar except for the church.

[Lupp]

It may be seen as a great advantage from a “western” pont of view that in many fields of great importance the standards developped under western predominance are still accepted globally. If we now spoil this due to stubbornness, we won’t get back good old time, but will have to learn Chinese. Presently China is using the Gregorian calendar in everyday life, and Chinese spreadsheets represent dates the same way as German ones do.