Re: [libreoffice-users] Value and Date function in Macro

On 02/08/2013 05:15 PM, Joel Madero wrote:
> Hi All,
>
> I have a macro setup that can easily do the following:
>
> pasteValue = mid(currentCellValue,1,4)

What is the format of "currentCellValue"? You grab the first four 
characters in your example above? Your code is dependent on how it is 
formatted.

> a string is stored to pasteValue which I then can manipulate how I want.
>
> What I want to do is make it actually more like this:
>
> pasteValue =
> date(value(mid(currentCellValue,1,4),value(mid(currentCellValue,5,2),value(mid(currentCellValue,8,2))

This won't work, there are so many things wrong with this, I won't even 
try to deal with what you did wrong. Instead, I will guess what you 
meant to do.

I am guessing that you are using something like a four digit year, a two 
digit month, and then a two digit day. Is this correct? If so, then you 
can CDateFromIso(currentCellValue), and this will give you a date object 
directly and do all the work for you. This is the easiest solution I think.

If you really want to do the hard work and rip values out, you probably 
want to use

DateSerial(year, month, day)

In your case, it is probably:

DateSerial(mid(currentCellValue,1,4), mid(currentCellValue,5,2), 
mid(currentCellValue,7,2))

Your strings will be automatically converted to numbers, so you don't 
need to do it in your macro.

Disclaimer: This will return a number, which will not cause the cell to 
be formatted as a date; you must set the formatting to be a date.

> I want to do this because the pasteValue as it stands is a text field, I
> need it converted to a date field. I can do this just fine by manaully
> entering the above code into a cell directly (ie. not in a macro) but when
> I enter it in the macro I get:
>
> Sub-procedure or function procedure not defined.
>
>
> I know I can work out a micky mouse way by adding another column and
> referring to the previous pasted data and then doing a special paste of it,
> but this seems unnecessary.
>
>
> Thanks in advance.
>
> Best Regards,
> Joel

--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
Info:  http://www.pitonyak.org/oo.php


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted