2014-10-26 13:11 GMT+01:00 DaveMainwaring <[email protected]>:
> Sure I can write all manner of macro. Takes time. I like ready made buttons
> :)
>
>
>
You could switch to LibreOffice, which has this functionality built in.
However, there's another way: You could use the macro I put together today.
There is a Macro document written by Andrew Pitonyak (he also wrote a book
about this) from which I borrowed a lot of work. Then I modified it a bit,
so this macro now works in single spreadsheet cells and in text documents.
In Writer it inserts the current date and time as a date field and in Calc
it just inserts the current time value and formats the cell to the format
that corresponds to your locale. Of course you can edit the macro to use a
different format of your choice. I commented those lines out, but I will
give you examples later. Here's the complete macro:
REM ***** BASIC *****
Option Explicit
Sub InsertDate
If ThisComponent.SupportsService("com.sun.star.sheet.SpreadsheetDocument")
Then
' Selection is a single cell
InsertDateIntoCell
ElseIf ThisComponent.SupportsService("com.sun.star.text.TextDocument") Then
' Selection is a paragraph
InsertDateIntoTextDocument
Else
MsgBox "Det där får du göra någon annanstans!",48
EndIf
End Sub
Sub InsertDateIntoCell
Dim oSelection As Object 'The currently selected cell
Dim oFormats As Object
oSelection=ThisComponent.getCurrentController().getSelection()
'Available formats
REM Verify that this is a Calc document
If oSelection.SupportsService("com.sun.star.sheet.SheetCell") Then
oSelection.setValue(Now())
Rem Set the date number format to default
oFormats=ThisComponent.NumberFormats
Dim aLocale As New com.sun.star.lang.Locale
oSelection.NumberFormat=_
oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATETIME,aLocale)
Rem Set the format to something completely different
Else
MsgBox "Detta fungerar endast i en cell åt gången.",48
End If
End Sub
Sub InsertDateIntoTextDocument
Dim oText As Object, oDateTime As Object
Dim oVCurs As Object, oTCurs As Object
oText=ThisComponent.Text
oVCurs=ThisComponent.CurrentController.getViewCursor()
oTCurs=oText.createTextCursorByRange(oVCurs.getStart())
' Create the DateTime type.
oDateTime=ThisComponent.createInstance("com.sun.star.text.TextField.DateTime")
oDateTime.IsFixed=TRUE
Dim aLocale As New com.sun.star.lang.Locale
Dim oFormats As Object
oFormats=ThisComponent.NumberFormats
oDateTime.NumberFormat=_
oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATETIME,aLocale)
oText.insertTextContent(oTCurs,oDateTime,FALSE)
oText.insertString(oTCurs," ",FALSE)
End Sub
Function FindCreateNumberFormatStyle (sFormat As String,Optional
doc,Optional locale)
Dim oDoc As Object
Dim aLocale As New com.sun.star.lang.Locale
Dim oFormats As Object
Dim formatNum As Integer
oDoc=IIf(IsMissing(doc),ThisComponent,doc)
oFormats=oDoc.getNumberFormats()
If ( Not IsMissing(locale)) Then
aLocale=locale
End If
formatNum=oFormats.queryKey (sFormat,aLocale,TRUE)
'If the number format does not exist then add it
If (formatNum=-1) Then
formatNum=oFormats.addNew(sFormat,aLocale)
If (formatNum=-1) Then formatNum=0
MsgBox "new Format number is " & formatNum
End If
FindCreateNumberFormatStyle=formatNum
End Function
REM ***** END OF BASIC *****
Copy this text to your clipboard (Ctrl+c, I'd guess; I don't know what
operating system you use).
Tools → Macros → OpenOffice Basic
I dialogue pops up with a few fields in it. One of them is called something
like ”Macro from”.
Click the ▸ symbol at ”My Macros”
Click the ▸ symbol at ”Standard” (or maybe ”Default” – I still use Apache
OpenOffice in Swedish, so I'm not sure…)
If there's nothing under it, click the ”New” button to the right inside the
dialogue.
A small dialogue pops up and want you to enter a module name. I called mine
”InsertStuff”. You may enter a different name, just remember that when I
say ”InsertStuff” below, I mean whatever name you entered.
Hit OK or press Enter.
The Basic IDE shows up, which is a big window where you enter your Basic
macros.
Replace the text in there with the content of your clipboard. The easiest
way to do that is probably Ctrl+a Ctrl+v (select all, paste).
You can now close the Basic IDE window if you like.
Now, you can start this macro with a keyboard shortcut or menu option, for
instance, if you create one. Here's how to create a keyboard shortcut; I
use Ctrl+Space, but feel free to use whatever you like:
Tools → Customise… → Keyboard
Scroll down to Ctrl+Space (or is it Ctrl+Blank? I'm not sure since I use
Apache OpenOffice in Swedish)
Click the Ctrl+Space line (or Ctrl+Blank)
At ”Functions”, scroll down the Category field and click the ▸ symbol at
”OpenOffice Macros”
If error messages appear, saying something about JRE, just click their OK
buttons. In my case there were 15 of those (so I guess I need to install a
JRE or something, but I didn't yet), just click them away.
Click the ▸ symbol at ”user”
Click the ▸ symbol at ”Standard” (or maybe ”Default” – I still use the
Swedish version…)
Click your new ”InsertStuff” (or whatever you called it)
In the field to the right, called ”Function”, click ”InsertDate”.
Click the ”Change” button. Or is it called ”Replace” or something? The
button at the top right of the dialogue, anyway.
Now click OK. Done!
I'm not sure if you need to do this in both Writer and Calc. I didn't but
it works anyway, maybe I did long ago and just don't remember it…
If you are not happy with the number format, you can edit the code yourself
to suit your needs. Here's an example:
I prefer the ISO-8601 time and date format, which looks like this for the
current date and time: 2014-10-26 15:20:59.
That is ”YYYY-MM-DD hh:mm:ss
Here's what the code could look like:
REM ***** BASIC *****
Option Explicit
Sub InsertDate
If ThisComponent.SupportsService("com.sun.star.sheet.SpreadsheetDocument")
Then
' Selection is a single cell
InsertDateIntoCell
ElseIf ThisComponent.SupportsService("com.sun.star.text.TextDocument") Then
' Selection is a paragraph
InsertDateIntoTextDocument
Else
MsgBox "Det där får du göra någon annanstans!",48
EndIf
End Sub
Sub InsertDateIntoCell
Dim oSelection As Object 'The currently selected cell
Dim oFormats As Object
oSelection=ThisComponent.getCurrentController().getSelection()
'Available formats
REM Verify that this is a Calc document
If oSelection.SupportsService("com.sun.star.sheet.SheetCell") Then
oSelection.setValue(Now())
Rem Set the date number format to default
oFormats=ThisComponent.NumberFormats
Dim aLocale As New com.sun.star.lang.Locale
' Customase the format
oSelection.NumberFormat=FindCreateNumberFormatStyle("YYYY-MM-DD TT:MM:SS")
Else
MsgBox "Detta fungerar endast i en cell åt gången.",48
End If
End Sub
Sub InsertDateIntoTextDocument
Dim oText As Object, oDateTime As Object
Dim oVCurs As Object, oTCurs As Object
oText=ThisComponent.Text
oVCurs=ThisComponent.CurrentController.getViewCursor()
oTCurs=oText.createTextCursorByRange(oVCurs.getStart())
' Create the DateTime type.
oDateTime=ThisComponent.createInstance("com.sun.star.text.TextField.DateTime")
oDateTime.IsFixed=TRUE
Dim aLocale As New com.sun.star.lang.Locale
Dim oFormats As Object
oFormats=ThisComponent.NumberFormats
' Customase the format
oDateTime.NumberFormat=FindCreateNumberFormatStyle("YYYY-MM-DD TT:MM:SS")
oText.insertTextContent(oTCurs,oDateTime,FALSE)
oText.insertString(oTCurs," ",FALSE)
End Sub
Function FindCreateNumberFormatStyle (sFormat As String,Optional
doc,Optional locale)
Dim oDoc As Object
Dim aLocale As New com.sun.star.lang.Locale
Dim oFormats As Object
Dim formatNum As Integer
oDoc=IIf(IsMissing(doc),ThisComponent,doc)
oFormats=oDoc.getNumberFormats()
If ( Not IsMissing(locale)) Then
aLocale=locale
End If
formatNum=oFormats.queryKey (sFormat,aLocale,TRUE)
'If the number format does not exist then add it
If (formatNum=-1) Then
formatNum=oFormats.addNew(sFormat,aLocale)
If (formatNum=-1) Then formatNum=0
MsgBox "new Format number is " & formatNum
End If
FindCreateNumberFormatStyle=formatNum
End Function
REM ***** END OF BASIC *****
At two places you can see the text "YYYY-MM-DD TT:MM:SS". This is the
ISO-8601 format I was talking about. Feel free to edit it to your likings.
Just note that ”TT” is probably not valid on your system. ”Hour”=”Timme” in
Swedish, that's probably why it works on my system. I'm not sure it was a
good idea by the developers to translate those format codes, but there's
nothing I can do about it anyway…
Another strange thing is that MM seems to mean not only Months, but also
Minutes. Strange but it actually works…!
Anyway, if you want to have a customised date and time format, replace the
old Basic code with the one above, then edit the "YYYY-MM-DD TT:MM:SS"
lines to your liking, for instance to ”MM/DD/YY HH:MM AM/PM” for that
strange American style date and time format, or whatever you like.
Good luck!
Kind regards
Johnny Rosenberg
ジョニー・ローゼンバーグ
> --
> Visit "Dave Mainwaring's Knowledge Networks" where participants share,
> learn and grow
>
>
>
>
> *"To succeed, stay focused. Do what
> you do best,
> up-skill, prepare for new challenges,
>
> make time for learning"*
>
> Patient Focused Friendly Hospitals http://tinyurl.com/qez9qvu
>
> Google 4 me: Dave+Mainwaring+Knowledge+Networks
>
> --
>
> On Sun, Oct 26, 2014 at 7:59 AM, Regina Henschel <[email protected]>
> wrote:
>
> > Hi Dave,
> >
> > DaveMainwaring schrieb:
> >
> >> I have started using EditPad for my text files. One feature like and
> miss
> >> in AOO is a simple date-time insert click and I can add a line that is
> >> dated,
> >>
> >> So many of the in-line forums have lousy editors I write my posts with
> >> EditPad, time & date the text, then cut and paste into the discussion.
> >>
> >
> > You can record a macro and assign it to a button or shortcut key. The
> > needed command is in Insert > Fields > Others. Tab Document, Type Date,
> > Select Date (fixed). For Format scroll down to "Additonal Formats". In
> that
> > format dialog use the format string TT.MM.JJ HH:MM.
> >
> > Kind regards
> > Regina
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [email protected]
> > For additional commands, e-mail: [email protected]
> >
> >
>
