[Solved] Method for date format

Keyboard macros or custom scripts

[Solved] Method for date format

Postby det » Sat Aug 05, 2017 3:50 am

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.3 on windows 7
det
 
Posts: 25
Joined: Fri Mar 31, 2017 8:24 pm

Re: Method for date format

Postby RusselB » Sat Aug 05, 2017 4:39 am

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.3 and LibreOffice 5.1.3.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4045
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Method for date format

Postby Zizi64 » Sat Aug 05, 2017 8:19 am

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; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Postby det » Sat Aug 05, 2017 7:32 pm

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.3 on windows 7
det
 
Posts: 25
Joined: Fri Mar 31, 2017 8:24 pm

Re: Method for date format

Postby Zizi64 » Sat Aug 05, 2017 8:35 pm

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


Code: Select all   Expand viewCollapse view
oCell.CellStyle = "MyDateStyle1"


Where MyDateStyle1 is the name of the user defined style.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Postby det » Sun Aug 06, 2017 6:40 pm

How do I create MyDateStyle1? Do I start with
MyDateStyle1= What?
OpenOffice 4.1.3 on windows 7
det
 
Posts: 25
Joined: Fri Mar 31, 2017 8:24 pm

Re: Method for date format

Postby Zizi64 » Sun Aug 06, 2017 7:03 pm

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; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Postby Zizi64 » Sun Aug 06, 2017 8:07 pm

Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Postby det » Sun Aug 06, 2017 9:35 pm

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.3 on windows 7
det
 
Posts: 25
Joined: Fri Mar 31, 2017 8:24 pm

Re: Method for date format

Postby Zizi64 » Mon Aug 07, 2017 7:28 am

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 16 times
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Method for date format

Postby det » Mon Aug 07, 2017 7:36 pm

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   Expand viewCollapse view
         '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.3 on windows 7
det
 
Posts: 25
Joined: Fri Mar 31, 2017 8:24 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 6 guests