[Solved] Method for date format

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

[Solved] Method for date format

Post by det »

Hi,
I wrote a macro to convert a text 13/04/2015 (from Internet) to a date number of 42853, using Split() and join(). Now I want to convert 42853 to 15/04/13.
The Macro recording gives:
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "NumberFormatValue"
args1(0).Value = 164
Is there a method that looks something like: oCell.FormatDate.NumberFormatValue=164, that I could just add to my macro?

With so many ways to state a Date (as Text, as Date, European, US, and hundreds more) isn't there a macro on the market to clean up this mess?
Last edited by Hagar Delest on Mon Aug 07, 2017 10:17 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Method for date format

Post by RusselB »

Off topic of your actual query, but related to the macro you wrote, you state that you convert 13/04/2015 to 42853, yet when I enter Apr 13 2015 into Calc, with the Date reference in Tools -> Settings -> OpenOffice Calc -> Calculate set as default 12/30/1899, I get a return of 42107
The other two options for that setting would return even lower numbers.
I don't know where you got the information that would give you the response you state for the date you specified, but the information from Open Office Calc suggests that you may have an error in your macro.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Post by Zizi64 »

Is there a method that looks something like: oCell.FormatDate.NumberFormatValue=164, that I could just add to my macro?
It is better to apply an existing (predefined) Cell style by your macro to the target cells.
The Styles are the most valuable properties of the AOO and LO. Use them.

Similar topic:
http://ooo-forums.apache.org/en/forum/v ... 20&t=67938
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Method for date format

Post by det »

Off Topic: You are right, I gave the wrong Date Value.
Applying a style outside the macro works. Thank you.
My problem, however, is that I do not know beforehand to which Cells the Dates go.
So I prefer to apply the Cell Style from inside the macro. Any suggestions?
Thank you Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Post by Zizi64 »

So I prefer to apply the Cell Style from inside the macro. Any suggestions?

Code: Select all

oCell.CellStyle = "MyDateStyle1"
Where MyDateStyle1 is the name of the user defined style.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Method for date format

Post by det »

How do I create MyDateStyle1? Do I start with
MyDateStyle1= What?
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Post by Zizi64 »

How do I create MyDateStyle1? Do I start with
MyDateStyle1= What?
It is a cellstyle. The name string is "MyDateStyle1". You can create it manually, or by a macro code (Creating it by a macro will be more difficult for you.)


Creating a cell style manually:

First:
F11: Styles and formatting
or Sidebar - styles and formatting
or from the menu: Format - Styles and formatting

then:
Cell styles icon
View Listbox at the bottom of the Sidebar: Hierarchical.

then:
Right click on the Default style - New
Name the new style and adjust the properties of the new style - OK.

then :
You can use the new style in the actual document.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Post by Zizi64 »

Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Method for date format

Post by det »

I had created the cell style "IntDate" as you suggested earlier manually, outside the macro.
How do I refer to this Style inside the macro?
oCell.CellStyle = "IntDate" does not work
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Post by Zizi64 »

How do I refer to this Style inside the macro?
oCell.CellStyle = "IntDate" does not work
Please upload your real ODF type sample file with your embedded macro code.


Here is my example:
Modify_Cellstyle_by_macro.ods
(12.54 KiB) Downloaded 236 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Method for date format

Post by det »

Thanks so much. It works!! No idea what I did wrong. Det
My Code (probably very improvable) is attached for others to use.

Code: Select all

         'XXXX Conversion of Text "DD/MM/YYY"Y to Date  YY/MM/DD  XXXXXX

Sub DateConversion
Dim oDoc As Object, oSheet As Object, oCell As Object
Dim R As Integer, C As Integer
Dim dispatcher as object
Dim NewDate As Date

               'XXXXXX Get Cell Position    XXXXXXX
               
oSheet=Thiscomponent.getcurrentcontroller.activesheet               
oCell = ThisComponent.CurrentSelection
'Print oCell.CellAddress.Column, oCell.CellAddress.Row, oCell.CellAddress.Sheet
R= oCell.CellAddress.Row
C=oCell.CellAddress.Column
'Print "String or Text =", oCell.String,"Row=",R,"Col=",C 

Do

oSheet.getCellByposition(C,R)
oCell = oSheet.getCellByposition(C,R)
oCell.CellStyle = "IntDate" 'Predefined in Calc
OldDate=oCell.string
If OldDate="" Then Exit Do
'Print "Old Date=", OldDate
AA=Split(OldDate,"/")
NewDate=Join(Array(AA(2),"/",AA(1),"/",AA(0)))
'Print "NewDate = ", NewDate
oCell.setValue(NewDate)
'Print " Newdatevalue",oCell.Value
'Print oCell.CellAddress.Column, oCell.CellAddress.Row, oCell.CellAddress.Sheet

R=R+1
Loop
Print "The chosen Cell in empty"
End Sub
Last edited by RoryOF on Mon Aug 07, 2017 7:56 pm, edited 1 time in total.
Reason: Added [Code] tags [RoryOF, Moderator]
OpenOffice 4.1.5 on Mac Sierra 10.13.1
Post Reply