Formatting date and time in a macro

[wordperfect_user]

I have a macro that puts the date and time right next to each other in a document. I mostly use this macro in table cells where I need to show the date and time. The macro works, but the problem I have is that I want the macro to render the date in 8601 format and the time in 24 hours format. Right now I get the date and time in the 10/11/18 02:08:26 PM formats, so I have to double click on each field separately and reformat it. Here's the macro I have, at least I think this is the right macro. Can anyone tell me how to get this macro to format the date and time the way I want it? I'm currently using OO 4.1.2. Thanks.

Code: Select all

sub dt
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertDateField", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Text"
args2(0).Value = " "

dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertTimeField", "", 0, Array())


end sub

[Villeroy]

Use auto-text rather than macros

[wordperfect_user]

Use auto-text rather than macros

I'm not sure that would work for me. In fact I'm not really familiar with the Auto Text feature. It looks like it takes a number of key combinations to get Auto Text to do things.

What I do now is press alt-M and the current date and time get pasted into the cell. If there's some way of making that date and time macro format its output to the right formats, e.g. 2018-10-11 14:43 instead of 10/11/18 02:43:15 PM that would be great. Is it possible to do?

[Lupp]

In this very exceptional case it might be forgivable to resort to custom code.
Due to an accidental coincidence I recently wrote a bit of code for a related task answering a question in a different forum.
The code was written and "debugged" under LibreOffice V6.1.1.2. It also worked under AOO, but the appearance of the fields needed to be updated expressly in addition: Press Ctrl+F9 twice to do so.

The code is for the conversion of ALL DateTimeFields in a Writer document to a unified format. You can apply it on old documents, and also on new ones, of course, before saving them, e.g.

The code:

Code: Select all

Sub unifyDateTimeFieldFormats(Optional pDoc As Object, _
                              Optional pLang As String, _
                              Optional pDateFormatString As String, _  
                              Optional pTimeFormatString As String)
REM Be careful! Undo does NOT work for the changes made by this routine!
theDoc = IIf(IsMissing(pDoc), ThisComponent, pDoc)
If NOT theDoc.SupportsService("com.sun.star.text.TextDocument") Then Exit Sub
fStringLang = IIf(IsMissing(pLang), "en", pLang)
REM Use a language for which your format codes are valid.
Dim fmtLocale As New com.sun.star.lang.Locale
fmtLocale.Language = fStringLang
dateFstring = IIf(IsMissing(pDateFormatString), "YYYY-MM-DD", pDateFormatString)
timeFstring = IIf(IsMissing(pTimeFormatString), "HH:MM:SS",   pTimeFormatString)
dateFkey    = findCreateNumberFormat(dateFstring, theDoc, fmtLocale)
timeFkey    = findCreateNumberFormat(timeFstring, theDoc, fmtLocale)
For Each tf In theDoc.TextFields
  If NOT tf.SupportsService("com.sun.star.text.TextField.DateTime") Then Goto nexttf
  Select Case tf.IsDate
    Case True  : tf.NumberFormat = dateFkey
    Case False : tf.NumberFormat = timeFkey
  End Select
nexttf:
Next tf
End Sub

The Sub uses the function findCreateNumberFormat next to completely taken from Andrew Pitonyaks's texts. (Only the errors - if any - are mine.) This function is also contained in the attached demo document.

[wordperfect_user]

The code is for the conversion of ALL DateTimeFields in a Writer document to a unified format.

Unfortunately, that's not what I need, since it converts all date and time fields in a document. Thanks though .

I need something that will take my tiny macro and just format what the macro is putting into the document to 8601 and 24 hour formats.

I'm thinking, after the topic hasn't found a solution for this long, that OO Writer just can't do what I need without my manually doing it.

[Villeroy]

If you do not want to use the simple end-user features of your text processor (auto-text) then you need to learn a programming langauge and this API which may take some weeks of intensive studies. The usage of auto-text is the same as in MS Word. It takes about 5 minutes to learn everything about it and another 5 minutes of excersizing with different types of content.

[wordperfect_user]

If you do not want to use the simple end-user features of your text processor (auto-text) then you need to learn a programming langauge and this API which may take some weeks of intensive studies. The usage of auto-text is the same as in MS Word. It takes about 5 minutes to learn everything about it and another 5 minutes of excersizing with different types of content.

Correct me if I'm wrong, but AutoText only puts a static string of text into the document. I need to put the current date followed by the current time, and with those fields formatted in 8601 and 24 hour modes respectively, into the document. I need to do this at various points in the document(s) and at various times. So a static sting of text will not work for me one minute after it's been saved in AutoText.

I have a macro that will do the date and time, but I can't figure out how to get a macro to automatically do the formatting I need. The macro puts out, for example, 11/21/18 06:55:12 AM instead of 2018-11-21 06:55. I have to format the date manually and then format the time manually after the macro has put those fields into the document.

If you can show me how AutoText can do what I need please show me. Thanks for your help.

[Villeroy]

If you spend more time speculating instead of trying you won't find out.

[wordperfect_user]

If you spend more time speculating instead of trying you won't find out.

I think it's obvious from what I've written here that I have tried it. If you actually know how to accomplish what I'm trying to do by using AutoText why not tell me ?

[Villeroy]

This is what I actually did with some success:
1) Insert>Fields>Date <space> Insert>Fields>Time insterts a fixed date and a fixed date separated by a space. Fixed dates don't update when a stored document reloads. This is what you want when using letter templates. You do not want to change the letter date every time you open the letter for reading but you want a new letter date when creating a new letter from template. The un-fixed date field variant updates every time when the saved document is loaded.
2) Dbl-click the date field and apply a US date with full weekday name and monthname (my default is German 21.11.2018). Changed the type from "Date (fixed)" to "Date".
3) Dbl-click the time field and apply a US time format with am/pm (my default is German 19:34:44). Unfixed this time field too.
4) Selected the text and applied some monospace font, bigger size, red font color.
5a) Keep the text selected and hit Ctrl+F3 and enter "US Time Stamp" into the name field. The shortcut field suggests "UTS" as new shortcut which is fine.
5b) I choose "My Auto Text" as storage pool and choose command "New" from the drop-down button at the right side. Notice that this command button provides another option to save a new auto-text without formatting.

After closing the dialog I can type uts{F3} in any text document and get the current date-time in US format, with red color and 16 point monospace font.
Wednesday, November 21, 2018 07:43:05 PM
The time updates every time when I reload the document.

[Villeroy]

And this is what I get when I hit F1 in AOO Writer and type "Autotext" into the search box:

To Create an AutoText Entry
1. Select the text, text with graphics, table, or field that you want to save as an AutoText entry. A graphic can only be stored if it is anchored as a character and is preceded and followed by at least one text character.
2. Choose Edit - AutoText.
3. Select the category where you want to store the AutoText.
4. Type a name that is longer than four characters. This allows you to use the Display remainder of name as suggestion while typing AutoText option. If you want, you can modify the proposed shortcut.
5. Click the AutoText button, and then choose New.
6. Click the Close button.

[Lupp]

Well, I was off. Physically some time and mentally some more time. I will not study what was suggested otherwise (and I rarely use AutoText). Thus I simply attach a demo .odt containing macros for
-1- harmonising date and time fields concerning the format
-2- inserting date and time in a predefined format
-3- converting date and time fields to ordinary text.
You may use it or not. I will not post another time in this thread.