Ask Your Question

Umlaut in CopyByName?

asked 2018-03-15 11:58:06 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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)
    MsgBox("Vorlage fehlt")

End Sub

Since my locale is German, the format(now,"MMMM") will yield the result "März". Which leads to this error: Type: Message: .

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

edit retag flag offensive close merge delete


David gravatar imageDavid ( 2018-03-15 12:54:41 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-03-15 13:03:33 +0200

Lupp gravatar image

updated 2018-03-15 13:09:44 +0200

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")

edit flag offensive delete link more


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.

LibreOfficePerson gravatar imageLibreOfficePerson ( 2018-03-15 13:18:15 +0200 )edit

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 gravatar imageLupp ( 2018-03-15 13:36:47 +0200 )edit

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!

Lupp gravatar imageLupp ( 2018-03-15 13:51:39 +0200 )edit

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.

LibreOfficePerson gravatar imageLibreOfficePerson ( 2018-03-15 15:50:29 +0200 )edit

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 gravatar imageLupp ( 2018-03-15 16:08:52 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-03-15 16:23:13 +0200 )edit

answered 2018-03-15 12:58:13 +0200

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.

edit flag offensive delete link more


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.)

Lupp gravatar imageLupp ( 2018-03-15 13:06:01 +0200 )edit

Thank you for your help! Much appreciated.

LibreOfficePerson gravatar imageLibreOfficePerson ( 2018-03-15 13:18:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-15 11:58:06 +0200

Seen: 60 times

Last updated: Mar 15 '18