Inserting Time Into A Calc Document

[schwinn434]

I'm using the Now () function to insert the current time into my spreadsheet, but each time I use the Now () function the time gets reset for all entries in my spreadsheet that have the Now() function, to the current time. I work as a dispatcher, and clock the departure and arrival times for our trucks, which come and go all day long. So, I would like to have a macro that just enters the time when I hit a certain key combination.

I've tried sreaching for already written macro's, but the only one I find is written in German; and I can't seem to get it to work.

I'm a newbie using, and writing macro's, so any help would be appreciated.

Thanks
Jimmy Kamuf

[Villeroy]

[Mark B]

Hi

As a matter of interest I use:

[JohnV]

Villeroy,

I thought it might be nice to add a little overwrite protection so I wrote the code in a slightly different way
to lead to my questions.

[Villeroy]

Many people pre-select a (multiple) range before they edit a spreadsheet.
Your code assumes that the current selection is a single cell with pseudo-property "Formula". Mark's code simply puts the date into the first cell of a selection, which may be a range. This fails on a multiple selection of ranges or if you have selected something else (chart, note, form-control...). The active cell of the view is always available. I believe this will never fail unless there is no spreadsheet-document at all. Furthermore I believe that the active cell is the most reasonable target a common user would expect when he uses a shortcut to put a time-stamp somewhere.
Function getActiveCell gets the active cell by reading the string getViewData() from a given controller. The initial version by Uros was named "getFocussedCell" and it failed with rows larger than 8191, because the viewData-string is different.
This represents a cell in row 8191
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0
This represents a cell in row 8192
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0
I simply added an error handler which splits by "+".

Why do I use FormulaLocal in this case?
At this moment I'm using a german version of OOo with all lang-settings set to default.
Basic function Now() returns a value of Basic type Date, which is converted on the fly when needed.
Msgbox Now() shows "19.04.2007 18:00:00" (conversion to string according to locale setting).
Same with oCell.setString(Now()).
Mark's oCell.setFormula(Now()) puts a string into my cell unless I use an english locale. This is because setFormula is supposed to be the english formula as it is saved with the document. ODF saves value 39191.75 (dot-decimal) and SUM(A1:A100) in all documents. Here the number is shown as unformatted number 39191,75 (comma) or formatted number 19.04.2007 18:00:00 (number-format-locale is "Default", which defaults to the used locale). Formula "SUM(A1:A100)" is shown as german "SUMME(A1:A100)". When I type 39191,75 it is treated as a number as well as 19.04.2007 18:00:00 which gets formatted on the fly. Actually I don't type the formula. In the GUI I always set FormulaLocal, which is the string I type into the cell or formula-bar.
String-conversion is fine if you just want to print some info into a sheet. When you need to calculate with this date later, then you have to convert it by VALUE(A1) or DATEVALUE(A1). This however may fail if the document is edited with another lang-setting. US 7th Dec "12/07/2007" may be converted to 12th of July, some asian locales may fail completely.
Cell functions and Basic use the locale-setting for date2string-conversion (second setting in Tools>Options>Lang).

oCell.setValue(Now()) puts 39191,75 into an unformatted cell (on-the-fly-conversion to double because all cell-values are double).
This fails if the document happens to use a non-standard null-date (Tools>Options>Calc>Calculation>"Null Date").
StarBasic converts all dates to double with null-date 1899-12-30. When you put the resulting double-value into a formatted cell, the number is shown according to the document's null date, which may differ from default 1899-12-30. Try A1 2007-04-19 and B1 =N(A1), A2 =TODAY and B2 =N(A2). Change the null-date and notice that A2=NOW() calculates another numeric cell-value while A1 is off because the same constant value means another date now.

oCell.FormulaLocal = cStr(Now()) or even oCell.FormulaLocal = Now() works as expected because Basic converts according to the current locale just like the formula-bar does. It's just like typing the localized string cStr(Now()) into the formula bar. I believe it works with all null-dates and all locales.
EDIT: Oh, yes I believed where I should have known better.
http://www.openoffice.org/issues/show_bug.cgi?id=72640
When I set the active cell's format to English then oCell.ForumulaLocal =Now() is a string. In this particular cell =1,9 (comma) still is a number, but 1,9 (comma) is text.
Second edit:
No, I won't study the details of c.s.s.I18n and c.s.s.util. Instead I let the spreadsheet-cell do the job:

[JohnV]

Villeroy,

Thanks for the information which was more than I anticipated.

[schwinn434]

Thanks For the help everyone, but I'm not sure how to use the provided code. Sorry to be so ignorant, but I tried to cut and past the below code using the Under Tools-Macros-Organize Macros-OpenOffice.org Basic.

At this point I didn't know how to create a new macro file, and cut and paste the below code into a file to execute as a Macro.

Again, Any Help Would Be Appreciate
Jimmy Kamuf

[Villeroy]

OK, let me suggest a stuctured way to organize your first macro, assuming we talk about my Sub Now_To_ActiveCell() which depends on Function getActiveCell(oView).
Call Tools>Macros>Organize>OOoBasic...
Notice that there are three different kinds of containers
1. "My Macros" for all code that is not related to a particular document.
2. "OOo Macros" which is the same for all users, which use this installation of OOo. There are many macros already. You need write-permission on the install-directory in order to use this container.
3. Each document of type Writer, Calc, Impress or Draw has it's own container for Basic code. This is a good place for macros that operate on particular parts of this particular document.
The code we are talking about is applicable to all spreadsheets, so we want to save it in one of the public containers 1 or 2.
Hit button "Organize..." on the dialogue. Select tab "Libraries" in the new dialog and there select location "My Macros".
Button "New..." lets you insert a new library. Name it "Calc" or "Spreadsheet".
Go back to tab "Modules" and insert a new module into your new library.
Name it like "UI", "Callables" or "Tools". This is a good place for all macros that are callable directly by some shortcut or button.
Insert another module named like "Helpers" or "Generic". This is a good place for generic functions that are supposed to be called from other code. It makes no sense to call getActiveCell from a button. In fact it won't work because it expects to get one argument passed and it does nothing than returning a value (the active cell of the passed view).
Hit the edit-button, and paste my Sub Now_To_ActiveCell() into the first module and the helper Function getActiveCell(oView) into the other.
Each new module gets a skeleton Sub Main ... End Sub by default. Remove that. If you like to rename Now_To_ActiveCell() do it now.
Save the edited library (Ctrl+S)
Call Tools>Customize and assign a shortcut, menu-item and/or button to the callable routine saved in MyModules>Calc>Callables>Now_To_ActiveCell (if you used that names).
Now_To_ActiveCell is callable by some shortcut since it does not take any additional argument and it gets a particular job done. You may save similar Calc-jobs to come in the same module. Some of them may want to do something with the active cell. The required helper is already there. Some other macros may come with other helpers. Put them in the other module of the same library, so you won't attemt to assign a shortcut to them.
OK you could simply paste all stuff into any module of the existing default library "Standard". This will increase the startup-time when this library gets larger and this particular library may get overwritten when you update the office (happened to me).
Your new library "Calc" gets loaded only when you call one of it's callable macros from the user-interface and things are sorted in a structured manner when you search for a macro. This library won't be overwritten by any install or update.
Just in case you want to call anything from another library:
OK, let me suggest a stuctured way to organize your first macro, assuming we talk about my Sub Now_To_ActiveCell() which depends on Function getActiveCell(oView).
Call Tools>Macros>Organize>OOoBasic...
Notice that there are three different kinds of containers
1. "My Macros" for all code that is not related to a particular document.
2. "OOo Macros" which is the same for all users, which use this installation of OOo. There are many macros already. You need write-permission on the install-directory in order to use this container.
3. Each document of type Writer, Calc, Impress or Draw has it's own container for Basic code. This is a good place for macros that operate on this particular document.
The code we are talking about is applicable to all spreadsheets, so we want to save it in one of the public containers 1 or 2.
Hit button "Organize..." on the dialogue. Select tab "Libraries" in the new dialog and there select location "My Macros".
Button "New..." lets you insert a new library. Name it like "Calc" or "Spreadsheet".
Go back to tab "Modules" and insert a new module into your new library.
Name it like "UI", "Callables" or "Tools". This is a good place for all macros that are callable directly by some shortcut or button.
Insert another module named like "Helpers" or "Generic". This is a good place for generic functions that are supposed to be called from other code. It makes no sense to call function getActiveCell(oView) from a button. In fact it won't work because it expects to get one argument oView passed and it does nothing than return some value (the active cell of the passed view).
Hit the edit-button, and paste my Sub Now_To_ActiveCell() into the first module and the helper Function getActiveCell(oView) into the other.
Each new module gets a skeleton Sub Main ... End Sub by default. Remove that. If you like to rename Now_To_ActiveCell() do it now.
Save the edited library (Ctrl+S)
Call Tools>Customize and assign a shortcut, menu-item and/or button to the callable routine saved in MyModules>Calc>Callables>Now_To_ActiveCell (if you used that names).
Now_To_ActiveCell is callable by shortcut or button since it does not take any additional argument and it gets a particular job done. You may save similar Calc-jobs to come in the same module. Some of them may want to do something with the active cell. The required helper is already there. Some other macros may come with other helpers. Put them in the other module of the same library, so they are separated from the callable jobs.
OK, you could simply paste all stuff into any module of the existing default library "Standard". This will increase the startup-time when this library grows larger and this particular library may get overwritten when you update the office (happened to me).
Your new library "Calc" gets loaded only when you call one of it's callable macros from the user-interface and things are sorted in a structured manner when you search for a macro. This library won't be overwritten by any install or update, but you back up on a regular basis anyways, don't you?
Just in case you want to call a Calc-macro from another macro in some other library, this line will make the routines availlable if your Calc-library is currently not loaded:
GlobalScope.BasicLibraries.loadLibrary("Calc")
(does not harm if it is already loaded)
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

[schwinn434]

Thanks Villeroy and Mark B

You guys are great in my eyes.


Both of your macros did the job. I really want to thank you, especially, Villeroy for taking the time to explain some basic things about setting up macros in OO.

I'm just amazed at tech guys -- such as yourselves. I personally think that guys like you are the best of what the Open Source movement has going for it. I don't think I would have gotten any kind of support from Microsoft like this -- even though we would have paid for the software.

You guys keep up the great work. I work for a charity and I really believe guys, such as yourselves, will be blessed for the work you're doing.

Sincerely
Jimmy Kamuf
schwinn434