[Solved] Setting time format for multiple custom styles

[rhubarbpie]

I use the following macro to assign a user-defined time format to my BlueTime style as user-defined formats aren't saved.

Code: Select all

Sub MyTime_enGB_HMMap()
 REM assign H:MMa/p in US locale to this document's cell style "MyTime"
 MakeUDNF ThisComponent, sStyle:="BlueTime", sLang:="en", sCountry:="GB", sCode:="H:MMa/p"
End Sub

Sub MakeUDNF(oDoc, sStyle, sLang, sCountry, sCode)
 oLoc = createUnoStruct("com.sun.star.lang.Locale")
 oLoc.Language = sLang
 oLoc.Country = sCountry
 oStyles = oDoc.StyleFamilies.getByName("CellStyles")
 oStyle = oStyles.getByName(sStyle)
 REM queryKey ( [in] string aFormat, [in] .lang.Locale nLocale, [in] boolean bScan )  long .util.XNumberFormats
 REM addNew ( [in] string aFormat, [in] .lang.Locale nLocale ) long .util.XNumberFormats
 oNFs = oDoc.getNumberFormats()
 intNFKey = oNFs.queryKey(sCode, oLoc, True)
 REM intNFKey is always -1 for existing crude number formats
 on error goto exit_err
 ' print intNFKey
 if intNFKey = -1 then intNFKey = oNFs.addNew(sCode, oLoc)
 oStyle.NumberFormat = intNFKey
 exit_err:
End Sub

While the macro works well, I'd also like to apply the H:MMa/p format to YellowTime. I've tried creating two MyTime_enGB_HMMap() blocks:

Code: Select all

Sub BlueTime()
 REM assign H:MMa/p in US locale to this document's cell style "MyTime"
 MakeUDNF ThisComponent, sStyle:="BlueTime", sLang:="en", sCountry:="GB", sCode:="H:MMa/p"
End Sub

Sub YellowTime()
 REM assign H:MMa/p in US locale to this document's cell style "MyTime"
 MakeUDNF ThisComponent, sStyle:="YellowTime", sLang:="en", sCountry:="GB", sCode:="H:MMa/p"
End Sub

My thought was BlueTime() and YellowTime() would both call MakeUDNF, but pass different sStyle variables and modify both styles. But while that modified macro runs without error, it modifies only the BlueTime style.

So, how can I modify both styles? And is there a way to modify all user-defined styles as I always prefer H:MMa/p for the time?

[Zizi64]

Your macros work fine for me in my Libreoffice 4.4.7 and the AOO 4.1.3 portable. (I set two different Date-Time codes in my example.)

StyleMacro.ods

(12.18 KiB) Downloaded 113 times

[rhubarbpie]

Thank you, but I have the same problem with your spreadsheet. It will change one style, but not both.

For instance, if I open the file and click BlueTime that cell displays correctly. But clicking YellowTime does nothing. If I open the file and click YellowTime that cell displays correctly. But clicking BlueTime does nothing.

I'm running Linux OpenOffice 4.1.3 and don't see what I'm doing wrong. Why can't I change both by a macro? I can change both styles manually.

I'm attaching your spreadsheet, but the with sCode changed to only H:MMa/p so we're talking apples and apples.

[Zizi64]

I just downloaded my example file: it works fine, and I downloaded your new example file: it can change a style ONE times only.

There is not other difference in the macro code than the 'time code strings'...
your strings are:
"H:MMa/p"

my strings:
"YYYY MMM DD H:MM AM/PM"
"YY MM DD H:MM"

But when I changed the code strings in your example file to:
"H:MM AM/PM"
"H:MM A/P"
The code works fine again.

Maybe the time code is "case sensitive" and "space-sensitive" when you try to apply it by the API...

[rhubarbpie]

Thank you, the problem was case. sCode:="H:MMa/p" changes only one style. However, sCode:="H:MMA/P" allows me to change both styles by macro.

I used sCode:="H:MMa/p" as I specifically want a lower-case designation. Why sCode:="H:MMA/P" displays lower case escapes me, but I'll take it.