Re: Formatting a Date in a Cell in Excel
You can probably accomplish what you want by doing [Format][Cell][Number tab] and selecting "text". Excel likes to infer that cells are dates because then it can treat them as an ordered list, and you can do arithmetic like "this day next week = today + 7". Under [Format][Cell][Number tab] the date selection has a number of good options for date presentation, but none seem to meet your needs. Glen Plantz wrote: Hi Folks, I'm having trouble formatting the Cell Contents of an Excel sheet with Win32::OLE. I want to force the contents of a Cell that has a DATE value ( "Dec 2005" ) to be a string. What happens is the Date value 'DEC 2005' in converted into 'Dec-05'. Any help will be greatly appreciated Thanks, Glen Plantz Here is my code;. --- my ($currentMonth, $currentYear ) = split / /, $currentMonthYear; # holds "Dec 2005" # Now open the TEMPLATE FILE where this month value will be used... # $CSAA_A2_RegionOffice my $template_Workbook = $excel_app->Workbooks->Open($CSAA_A2_RegionOffice) or die "could not open $CSAA_A2_RegionOffice\n"; #now select first column heading where HOA will be used... and try putting the correct column heading in... my $Sheet1 = $template_Workbook->Worksheets(1); $Sheet1->Range("D2")->Select(); #now select correct array based on the '$currentMonth' value my $mnth; my $i = 0; my $mnth = $HOA{$currentMonth}[$i]; # $mnth holds 'DEC' #correct value... now try putting in the correct value... my $monthYear = $mnth . ' ' . $currentYear; print "monthYear = $monthYear\n"; # my $dt = Variant(VT_DATE, $monthYear); print "$dt\n"; print $dt->Date("MMM "), "\n"; my $formatedDate = $dt->Date("MMM "), "\n"; print "formatedDate = $formatedDate\n"; # this works, but the year has the format '05' see screen shot... $Sheet1->Range("D2")->{'Value'} = $monthYear; # this does not work #$Sheet1->Range("D2")->{'Value'} = $formatedDate; #this doesn't seem to effect the value entered... #$Sheet1->Range("D2")->Format("MMM "); ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs -- R. Dennis Kelly ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Formatting a Date in a Cell in Excel
PS - you can also use [Format][cell][Number tab] and select custom. Then specify "mmm " this will allow excel to treat it as a numeric quantity, and still have it be presented as you wish. Glen Plantz wrote: Hi Folks, I'm having trouble formatting the Cell Contents of an Excel sheet with Win32::OLE. I want to force the contents of a Cell that has a DATE value ( "Dec 2005" ) to be a string. What happens is the Date value 'DEC 2005' in converted into 'Dec-05'. Any help will be greatly appreciated Thanks, Glen Plantz Here is my code;. --- my ($currentMonth, $currentYear ) = split / /, $currentMonthYear; # holds "Dec 2005" # Now open the TEMPLATE FILE where this month value will be used... # $CSAA_A2_RegionOffice my $template_Workbook = $excel_app->Workbooks->Open($CSAA_A2_RegionOffice) or die "could not open $CSAA_A2_RegionOffice\n"; #now select first column heading where HOA will be used... and try putting the correct column heading in... my $Sheet1 = $template_Workbook->Worksheets(1); $Sheet1->Range("D2")->Select(); #now select correct array based on the '$currentMonth' value my $mnth; my $i = 0; my $mnth = $HOA{$currentMonth}[$i]; # $mnth holds 'DEC' #correct value... now try putting in the correct value... my $monthYear = $mnth . ' ' . $currentYear; print "monthYear = $monthYear\n"; # my $dt = Variant(VT_DATE, $monthYear); print "$dt\n"; print $dt->Date("MMM "), "\n"; my $formatedDate = $dt->Date("MMM "), "\n"; print "formatedDate = $formatedDate\n"; # this works, but the year has the format '05' see screen shot... $Sheet1->Range("D2")->{'Value'} = $monthYear; # this does not work #$Sheet1->Range("D2")->{'Value'} = $formatedDate; #this doesn't seem to effect the value entered... #$Sheet1->Range("D2")->Format("MMM "); ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs -- R. Dennis Kelly ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Formatting a Date in a Cell in Excel
If u precede the data with an apostrophe then Excel will not try to autoformat it. At 03:35 PM 12/15/2005 -0800, Glen Plantz wrote: >I want to force the contents of a Cell that has a DATE value ( "Dec >2005" ) to be a string. >What happens is the Date value 'DEC 2005' in converted into 'Dec-05'. $formatteddate = "'DEC 2005"; -- REMEMBER THE WORLD TRADE CENTER ---=< WTC 911 >=-- "...ne cede malis" 0100 ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
RE: Formatting a Date in a Cell in Excel
Try something like this: $Sheet1->Range("D2")->{NumberFormat} = "MMM "; and please use plain text instead of HTML for mailing list traffic. :) Cheers, -Jan ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Formatting a Date in a Cell in Excel
Title: Message Hi Folks, I'm having trouble formatting the Cell Contents of an Excel sheet with Win32::OLE. I want to force the contents of a Cell that has a DATE value ( "Dec 2005" ) to be a string. What happens is the Date value 'DEC 2005' in converted into 'Dec-05'. Any help will be greatly appreciated Thanks, Glen Plantz Here is my code;. --- my ($currentMonth, $currentYear ) = split / /, $currentMonthYear; # holds "Dec 2005" # Now open the TEMPLATE FILE where this month value will be used...# $CSAA_A2_RegionOfficemy $template_Workbook = $excel_app->Workbooks->Open($CSAA_A2_RegionOffice)or die "could not open $CSAA_A2_RegionOffice\n"; #now select first column heading where HOA will be used... and try putting the correct column heading in...my $Sheet1 = $template_Workbook->Worksheets(1);$Sheet1->Range("D2")->Select(); #now select correct array based on the '$currentMonth' value my $mnth;my $i = 0; my $mnth = $HOA{$currentMonth}[$i]; # $mnth holds 'DEC' #correct value... now try putting in the correct value... my $monthYear = $mnth . ' ' . $currentYear; print "monthYear = $monthYear\n"; # my $dt = Variant(VT_DATE, $monthYear); print "$dt\n";print $dt->Date("MMM "), "\n"; my $formatedDate = $dt->Date("MMM "), "\n";print "formatedDate = $formatedDate\n"; # this works, but the year has the format '05' see screen shot...$Sheet1->Range("D2")->{'Value'} = $monthYear; # this does not work#$Sheet1->Range("D2")->{'Value'} = $formatedDate; #this doesn't seem to effect the value entered...#$Sheet1->Range("D2")->Format("MMM "); ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs