Re: [libreoffice-users] Re: macro pasting text instead of formula

On 12/10/2014 12:23 PM, Joel Madero wrote:
> Hi All,
>
> > Well, you set the string of a cell.
> > Set the formula instead.
> So I just started from scratch because I had a ton of if statements
> before, decided to go to switch to make it faster but I'm hitting the
> same type of problem.
>
> Document:
> https://drive.google.com/file/d/0B5S-XtOUGGH5UmpBdnIzUUM3OFE/view?usp=sharing
>
> Click "Create Ledger" and you'll see the macro begin.
>
> Problem: Look at sheet "Ledger" under header "Date" (B7:B10). At first I
> thought those were right but they are not. Convert those to date, you'll
> see that all of them are 12/10/14.
>
> I literally have no clue where those are coming from. They should be:
> B7: 06/26/2009
> B8: 06/26/2009
> B9: 06/27/2009
> B10: 06/28/2009
>
> (all taken from RawData sheet).
>
> If you look at the macro (Sub fillData) you'll see that the pasteValue
> gets Date(year,month,date), furthermore you can see that year, month,
> date all get the correct values (uncomment the "print" values in the
> switch).....
>
> Terribly confused at this point, suggestions welcome.
>
>
> Best,
> Joel

I can see many things that you have done wrong. Here are a few comments....

Your first problem... replace

pasteSheet.getCellByPosition(1,k).value = Date(year,month,day)

with

 pasteSheet.getCellByPosition(1,k).value = DateSerial(year,month,day)

DateSerial returns a date built from the component parts, Date does not.

Also, you probably want to set the format to use a Date. I won't bother 
with a complicated specified Numeric format for this, but will provide 
this simple example:

Dim oFormats
oFormats = ThisComponent.NumberFormats
Dim aLocale As New com.sun.star.lang.Locale
pasteSheet.getCellByPosition(1,k).value = DateSerial(year,month,day)
pasteSheet.getCellByPosition(1,k).NumberFormat = 
oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, aLocale)


Now for a few other comments.

You have this big select case for some things. Consider this:

Day month and year area already defined as type integer. This means that 
those big case statements can be removed as follows:

               month = mid(currentCellValue, 5,2)
               year = mid(currentCellValue, 10,2)
               If (year < 10) Then
                 year = year + 2010
               ElseIf (year < 99) Then
                 year = year + 2000
               End If
               day = mid(currentCellValue, 2,2)

   Dim oFormats
   oFormats = ThisComponent.NumberFormats
   Dim aLocale As New com.sun.star.lang.Locale
               pasteSheet.getCellByPosition(1,k).value = 
DateSerial(year,month,day)
               pasteSheet.getCellByPosition(1,k).NumberFormat = 
oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, aLocale)


Hope this helps.

AndrewMacro.odt is a good place to find specific focused examples.
OOME_3_0.odt contains numerous examples, but is more focused towards 
learning.

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


--
To unsubscribe e-mail to: users+unsubscribe@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