[Solved] Need Datestamp Macro

Discuss the spreadsheet application

[Solved] Need Datestamp Macro

Postby daveg7 » Wed Sep 27, 2017 10:28 pm

Needs to be in this format: YY.MM.DD

Will look like this: 17.09.27

Thanks in advance!
Last edited by daveg7 on Fri Sep 29, 2017 2:14 am, edited 2 times in total.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
daveg7
 
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: Need Datestamp Macro

Postby Lupp » Wed Sep 27, 2017 10:35 pm

Try to search THIS FORMUM for the search word "timestamp". See https://forum.openoffice.org/en/forum/s ... d%5B0%5D=9 .
If you have remaining questions come back after reading, please.

Also:
Never use 2-digit-years! Don't you remeber the "y2k" hysteria and the related information around everywhere less than 20 years ago?
Make sure you cannot do things in a simpler way before asking for "macros", There are many disadvantages. Only Excel enthusiasts prefer macros for everything.

What about assigning a keyboard-shortcut to the command 'Insert Current Date'. The cell content created will then be numeric as is standard in spreadsheets. To get displayed the recommended format YYYY-MM-DD simply prepare a 'Date' cell style with it and assign it to the cells (columns) expected to have to take a date-stamp.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Need Datestamp Macro

Postby daveg7 » Wed Sep 27, 2017 11:25 pm

I had done your search before posting, but nothing seemed to work for me.

Thanks for your input, but I've worked for decades with spreadsheets since VisiCalc. Though I know that sometimes an old horse doesn't know where he's going, I think I know what I need for my client--and yes, it's a macro.

Danken!
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
daveg7
 
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: Need Datestamp Macro

Postby Lupp » Wed Sep 27, 2017 11:34 pm

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

REM First variant
Sub insertCDnumFormatted()
   Dim dFrame As Object
   Dim dH     As Object
dFrame = ThisComponent.CurrentController.Frame
dH     = CreateUnoService("com.sun.star.frame.DispatchHelper")
   Dim args1(0) As New com.sun.star.beans.PropertyValue
args1(0).Name  = "StringName"
args1(0).Value = Format(Int(Now()), "YYYY-MM-DD")
REM Expect problems with different locales.

dH.ExecuteDispatch(dFrame, ".uno:EnterString", "", 0, args1())
'dH.ExecuteDispatch(dFrame, ".uno:JumpToNextCell", "", 0, Array())
End Sub

REM Second variant
Sub inserCDisoText()
theFC = focusCell()
theFC.String = Format(Int(Now()), "YYYY-MM-DD")
REM Expect problems with different locales.
End Sub

REM  Helper needed for the second version:
Function focusCell(Optional pDoc) As Object
REM Concept by Andreas Säger
REM Responsible for this variant: Wolfgang Jäger
If IsMissing(pDoc) Then pDoc = ThisComponent
If NOT pDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then
    Exit Function
End If
vD          = pDoc.CurrentController.ViewData
vDSplit     = Split(vD, ";")
sheetNum    = 0 + vDSplit(1)
theSheet    = pDoc.Sheets(sheetNum)
vDtheSheet  = Split(vDSplit(sheetNum + 3), "/")
fC          = theSheet.GetCellByPosition(vDtheSheet(0), vDtheSheet(1))
focusCell   = fC
End Function
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Need Datestamp Macro

Postby Villeroy » Thu Sep 28, 2017 12:45 am

FormulaLocal is easier and avoids locale problems:
Code: Select all   Expand viewCollapse view
oCell = focusCell()
oCell.FormulaLocal = Format(Now(), "YYYY-MM-DD")

works fine with full ISO format string "YYYY-MM-DD HH:MM:SS.00"
If the cell is not formatted explicitly (number format "General"), the entry of an ISO date/time triggers the default date/time format.
 Edit: fixed typo and property assignment. 
Last edited by Villeroy on Thu Sep 28, 2017 2:32 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27296
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need Datestamp Macro

Postby Lupp » Thu Sep 28, 2017 1:14 am

(Maybe I overread the hint concerning the expectable locale problems the first time. Now I got it. But how will this be achieved.)

Hmm. You surely found my "Second variant" which assigns the Formatted result to the .String property of the cell under focus.
In what way should it be preferrable to assign the same result to the .FormulaLocal property?

BTW: In the way you posted it (the typo rectified), I think no assignment is done. You would either need to use a Set method (which does not exist for .FormulaLocal) or an explicit assignment like
Code: Select all   Expand viewCollapse view
oCell.FormulaLocal = Format(Now(), "YYYY-MM-DD")

OK. In LibO V 5.4.1. this leads to a numeric content formatted correctly. But where is this behaviour specified and who did promise that this will not be changed without notice?

Yes, my preferred solution creates text content, but this in a format explicitly specified to be recognised numeric.
The "First variant" mimics a manual entering and has the same effect as yours, which seems more expectable to me in this case..
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Need Datestamp Macro

Postby Villeroy » Thu Sep 28, 2017 2:42 pm

OK. In LibO V 5.4.1. this leads to a numeric content formatted correctly. But where is this behaviour specified and who did promise that this will not be changed without notice?

It is not documented. ISO date input is recognized since version one. I tested the correct behaviour with all built-in locale settings.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27296
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need Datestamp Macro

Postby Lupp » Thu Sep 28, 2017 10:13 pm

ISO8601 extemded (YYYY-MM-DD) is, however specified to be accepted for the conversion of text to number by the VALUE() function under 6.13.34 of 'OpenFormula'. Let us hope that everybody takes it as a concludent necessity that automatic conversion after entering 'keystroKes' into cells should also accept that ISO conforming format in the same way.

However, I did not understand for what reason an assignment to the cell property .FormulaLocal should act as if the string was entered into the cell.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Need Datestamp Macro

Postby Villeroy » Thu Sep 28, 2017 10:31 pm

Lupp wrote:However, I did not understand for what reason an assignment to the cell property .FormulaLocal should act as if the string was entered into the cell.

FormulaLocal is the string you see in the formula bar. When you set the FormulaLocal you enter something into the formula bar which may be evaluated as a number or not. When you enter a special number into the formula bar, you get the matching default format for the entered subtype.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27296
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need Datestamp Macro

Postby Lupp » Fri Sep 29, 2017 12:25 am

Thanks.
Lacking access to actual documentation (or being too dull / too lazy / too uninspired) I am stumped again and again. Why 'Formula'? Ok. The 'Formula bar' suffers from the confusion too, but the term didn't look to me as if relevant for naming any properties. At least for me the so called formula bar is a little editor for any cell content.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Need Datestamp Macro

Postby Villeroy » Fri Sep 29, 2017 1:01 am

There is always a formula, a value and a string even when the cell is blank.

Blank: formula="", string="", value=0
Text: formula=text, string=text, value=0
Numeric text: formula='0123, string=0123, value=0
Number: formula=1.234, string=numeric string according to number format, value=1.234
Calculation: formula=expression starting with =, string=numeric string or text, value=decimal number or 0 in case of text.

In case of number or calculation the FormulaLocal may differ from the unlocalized English formula. FormulaLocal may have a comma as decimal separator, the function names may differ and special numbers are formatted strings. The unlocalized get/setFormula() is always an unformatted decimal string with point as decimal separator.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27296
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 33 guests